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

Charles, I hear you.

I tried both:
CREATE INDEX T1IDXA ON T1 (GLLT ASC, GLDSYJ ASC, GLDGJ ASC)
and
CREATE INDEX T1IDXB ON T1 (GLLT ASC, GLDSYJ ASC, GLDGJ ASC, GLMCU ASC)
T1IDXA was used once in a while.
T1IDXB wasn't used at all (maybe it would have been without T1IDXB).
I originally tried T1IDXB becuase I thought the optimizer would go through
the first 3 selection criteria and then use the last column of the index,
GLMCU to join to t2.  I'm not sure if this is viable logic for DB2/400 - I
will have to look it up.

RH


"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:F520B5C51DB10041B239BC06383A7EDC01D376EF@xxxxxxxxxxxxxxxxxxxxxxxxxx
> 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.
> >
> >
>
> -- 
> 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.
>
>