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

Ryan,

Note that just because an index isn't directly used in an access plan,
that doesn't mean the statistics intrinsic to the index weren't used by
the optimizer to determine an access plan.

In other words, don't be surprised if the optimizer does something
different if you delete those "unused" indexes.

I'd be curious to see what happens if you change:
CREATE INDEX T1IDX1 ON T1 (GLLT ASC, GLDSYJ ASC, GLDGJ DESC)

To
CREATE INDEX T1IDX1 ON T1 (GLLT ASC, GLDSYJ ASC, GLDGJ ASC)

I normally wouldn't expect the order to make a difference.  However,
given that your query specifies GLDSYJ>=106072 AND GLDGJ<=105365, I
think I can see why the order might make a difference in this case.

Consider:
Position at GLLT = 'AA', GLDSYJ = 106072, and GLDGJ = 105365
Read forward till EOF or GLLT <> 'AA'

Vs.
Position at GLLT = 'AA', GLDSYJ = *MAXVAL, and GLDGJ = 105365 + 1
Read backward till EOF or GLLT <> 'AA' or GLDSYJ < 106072

I think the index stats would be easier to make use of with the DESC.  I
could be way off though ;-)

HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Ryan Hunt
> Sent: Monday, May 15, 2006 1:14 PM
> To: midrange-l@xxxxxxxxxxxx
> 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
> 
> "Elvis Budimlic"
> <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
> wrote in message news:000e01c6783a$2fc7fe30$6e01a8c0@xxxxxxxx
> > Ryan,
> >
> > A while back I ran across an IBM article "Art and science of SQL
> performance
> > tuning" 
> (http://www.common.be/pdffiles/16042002DB2ArtandScience.pdf) and
> had
> > to chuckle at the title as it sure is more art than 
> science, at least in
> my
> > experience :)
> >
> > There are two ways SQL views can be implemented on the DB2 
> for System i,
> > view materialization and view composition.  You want to try 
> and avoid view
> > materialization if possible, and it should be possible with 
> your query as
> > it's relatively straightforward.
> > IBM has been doing a good job of getting more queries toward view
> > composition with new query engine (SQE).  So, one thing to 
> make sure is
> that
> > your query is not going down CQE path (see latest 
> Centerfield article on
> the
> > method to determine which query engine your statement is hitting -
> >
> http://www.centerfieldtechnology.com/publications/archive/Apri
> l%202006.pdf).
> >
> > Another thing I learned as I'm tuning SQL on System i is 
> that the answer
> is
> > always "It depends".  There are just too many factors query 
> optimizer
> takes
> > into consideration when it's making its decisions for 
> anyone to claim
> > anything with full certainty.  Another thing that exasperates this
> situation
> > is that IBM is hard at work changing, enhancing and 
> optimizing the query
> > optimizer all the time, so what you know today may be 
> outdated tomorrow.
> >
> > Query optimization is iterative process so view my 
> suggestion as iteration
> 1
> > and cross your fingers :)
> >
> > CREATE INDEX T1IDX1 ON T1 (GLLT ASC, GLDSYJ ASC, GLDGJ DESC)
> > CREATE INDEX T1IDX2 ON T1 (GLMCU ASC)
> > CREATE INDEX T2IDX1 ON T2 (MCMCU ASC)
> >
> > WITH t AS
> > {
> > SELECT GLMCU,
> >        GLOBJ,
> >        GLSUB,
> >        GLEXA,
> >        GLEXR,
> >        GLAA,
> >        GLIVD,
> >        GLDGJ,
> >        GLDCT,
> >        GLPO,
> >        GLLT,
> >        GLDSYJ
> > FROM t1
> > WHERE GLLT='AA' AND GLDSYJ>=106072 AND GLDGJ<=105365
> > }
> > SELECT
> >   t.GLMCU,
> >   t2.MCDC,
> >   t2.MCRP01,
> >   t.GLOBJ,
> >   t.GLSUB,
> >   t.GLEXA,
> >   t.GLEXR,
> >   t.GLAA,
> >   t.GLIVD,
> >   t.GLDGJ,
> >   t.GLDCT,
> >   t.GLPO,
> >   t.GLLT,
> >   t.GLDSYJ
> > FROM t INNER JOIN t2 ON t2.MCMCU = t1.GLMCU
> >
> > Test it first with some simple selects before you building 
> a view on top
> of
> > it. I'm curious to see what your testing shows.
> > Good luck.
> >
> > Elvis
> >
> > -----Original Message-----
> > Subject: Fun with DB2/400 Optimizer...well, not quite.
> >
> > I have a recurring problem with the DB2/400 optimizer that 
> I'm hoping
> others
> > can shed some light on. This post is pretty long (sorry) so 
> I will begin
> > with my questions before you guys get tired of reading.  ;-)
> >
> >
> ______________________________________________________________
> ______________
> > __
> > My first issue is that DB2/400 seems to want to table scan 
> WAY more often
> on
> > this particular data selection than it should.  The 
> selection criteria
> only
> > selects 4665 records of the 18.5 million in a table.  There 
> is some index
> > experimentation I could do here.  For instance, my index is actually
> > beginning with my least selective column (GLLT), however 
> I've done this
> > because I've had issues in the past by starting with 
> columns I am doing
> > range (BETWEEN) or >=/<= selection on.  Is there a way to force the
> > optimizer to use indexes, or force it to select the 4665 
> relevant records
> > BEFORE the join???
> >
> > My second issue is the creation of views.  Most of these 
> queries I have to
> > turn into views.  Even after I have a very well tuned query / index
> > combination, turning the SQL into a view ALMOST ALWAYS 
> (99%) results in
> > table scans.
> >
> ______________________________________________________________
> ______________
> > __
> >
> > Some general info...
> >
> > Table t1 is about 100 columns wide, has 18.5 million rows, 
> and is 14GB in
> > size.  Table t2 is about 50 columns wide, has 4500 rows, 
> and is 3mb in
> size
> >
> > There are lots of indexes, but the relevant ones are:
> >
> > IDX_T1 (GLLT asc, GLDGJ asc, GLDSYJ asc, GLMCU asc)
> > IDX_t2 (MCMCU asc)
> >
> > Instead of obfuscating with pseudo code, here is the real deal.
> >
> > Query1:
> >
> > SELECT t1.GLMCU
> > , t2.MCDC
> > , t2.MCRP01
> > , t1.GLOBJ
> > , t1.GLSUB
> > , t1.GLEXA
> > , t1.GLEXR
> > , t1.GLAA
> > , t1.GLIVD
> > , t1.GLDGJ
> > , t1.GLDCT
> > , t1.GLPO
> > , t1.GLLT
> > , t1.GLDSYJ
> > FROM t1
> > INNER JOIN t2 ON t2.MCMCU = t1.GLMCU
> > WHERE t1.GLLT='AA' AND t1.GLDSYJ>=106072 AND t1.GLDGJ<=105365
> >
> > Results mixed.
> > 50%:  TABLE SCAN on t1, TABLE SCAN on t2 with temp hash 
> table, hash join
> > 50%:  Index Scan IDX_t1, Index Scan IDX_t2, table probes, 
> then nested loop
> > join.
> >
> > If I turn Query1 into a view and SELECT * FROM View, this 
> ALWAYS results
> in
> > tables scans and overnight processing.
> >
> > Query2:
> >
> > SELECT t1.GLMCU
> > , t2.MCDC
> > , t2.MCRP01
> > , t1.GLOBJ
> > , t1.GLSUB
> > , t1.GLEXA
> > , t1.GLEXR
> > , t1.GLAA
> > , t1.GLIVD
> > , t1.GLDGJ
> > , t1.GLDCT
> > , t1.GLPO
> > , t1.GLLT
> > , t1.GLDSYJ
> > FROM t1
> > INNER JOIN t2 ON t2.MCMCU = t1.GLMCU AND t1.GLLT='AA' AND
> t1.GLDSYJ>=106072
> > AND t1.GLDGJ<=105365
> >
> > Results:
> > 100%:  Index Scan IDX_t1, Index Scan IDX_t2, table probes, 
> then nested
> loop
> > join.
> >
> > If it turn Query 2 into a view and SELECT * FROM View, 
> results are mixed.
> > 5%:  Index Scan IDX_t1, Index Scan IDX_t2, table probes, 
> then nested loop
> > join.
> > 95%  TABLE SCAN on t1, TABLE SCAN on t2 with temp hash 
> table, hash join
> >
> > Query3:
> >
> > SELECT t2.MCDC,  t2.MCRP01, ilv1.*
> > FROM
> > (
> > SELECT GLMCU
> > , GLOBJ
> > , GLSUB
> > , GLEXA
> > , GLEXR
> > , GLAA
> > , GLIVD
> > , GLDGJ
> > , GLDCT
> > , GLPO
> > , GLLT
> > , GLDSYJ
> > FROM t1 WHERE GLLT='AA' AND GLDSYJ>=106072 AND GLDGJ<=105365
> > ) ilv1
> > INNER JOIN t2 ON t2.MCMCU = ilv1.GLMCU
> >
> > Results:
> > 100%:  TABLE SCAN on t1, TABLE SCAN on t2 with temp hash 
> table, hash join
> >
> > Turning this into a view has same result.
> >
> >
> > Any ideas?
> >
> >
> >
> > -- 
> > 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.
> >
> >
> 
> 
> 
> -- 
> 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.
> 
>