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

Elvis, thanks for the links.  I will check them out.  I don't recognize this
differentiation between view composition and view materialization - can you
provide a quick explanation or some additional links.

Also, what is this "WITH..." syntax - does this lead to "composition"?
"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.
>
>