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

["Ryan Hunt" <ryan.hunt@xxxxxxxxxxxxx>]

Thanks again Elvis.  I am planning a move to V5R4 in September...primarily
to get at those SQE enhancements. RH

RH

"Elvis Budimlic"
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:001601c67846$a72e96d0$6e01a8c0@xxxxxxxx
> 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
>
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>