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

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/April%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.
>
>