RE: Fun with DB2/400 Optimizer...well, not quite.

Ryan, glad to hear you got positive results!

Basic premise behind my recommendation was that your problem had to do with
selection criteria (WHERE clause on T1).  T2 table size is insignificant so
there's no point in tuning it at all.  T1 size is significant and your query
is highly selective so if you can figure out a way to have an index used for
selection criteria, 98% of the pain for this specific query would have been
alleviated.
By isolating the selection into a CTE and building an index for selection
criteria specifically, I figured odds are good query optimizer will perform
the selection using new index prior to doing the join (now on very small
subset of rows - 4665).  Looks like that's what's going on (hash join at the
very end indicates no indexes are needed/usable so you can delete T1IDX2 and
T2IDX1 - unless T2 is going to grow rapidly and drastically).

As for the GLDGJ DESC, sort order shouldn't matter much in this particular
case (ASCending or DESCending), but I figured since your selection on that
column is less-than-or-equal-to (<=) it would be fitting to use sort order
mimicking that criteria.

BTW, I see you're still on V5R2.  New SQE query engine is being enhanced
continuously and for the better, so I recommend an upgrade of the OS as soon
as you get a chance.  
For example, one of the things that SQE can do is use and ASCending index to
find a MIN value with a single look up.  It couldn't do that in CQE (you had
to build a DESCending index for that specific requirement).

Elvis

-----Original Message-----
Subject: Re: Fun with DB2/400 Optimizer...well, not quite.


Wow - Elvis.  Your making me look bad.  I tried your suggestions.
Of your indexes two were ignored but one used.  The one used was
CREATE INDEX T1IDX1 ON T1 (GLLT ASC, GLDSYJ ASC, GLDGJ DESC)

Using your "WITH" syntax, the access plan looks like:
Index Scan Key Positioning-->Key Selection-->Table Probe-->dynamic
Bitmap-->Bitmap Merge-->skip sequential table scan-->hash join (with leg on
t2)

I also created your query as a view and it keeps the access plan!

So, two questions.  What's up with the GLDGJ DESC on your index suggestion,
and what's up with the "WITH" statement???

Thanks. RH