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

Hello Charles.
1)  SQL defined.
2)  Yes, Visual Explain.  This is where I obtained the resultant access
paths in the original post.  Suprisingly, no suggestions were made.
3)  Yes, I am pretty up to date.  I installed the DB / HIPER PTF's for V5R2
that were current as of December 2005.
4)  I wasn't sure what to expect here.  I've seen different RDBMS's treat
this differently.  I've seen a redbook somewhere that suggested using
criteria in the JOIN whenever possible as it can be more helpful to the
optimizer.  It almost seems as if DB2/400 is more likely to apply the
criteria as part of the join when in the ON clause...while applying the
criteria after the join when in the where clause.  Although, most cost based
optimizers are more intelligent that that these days...
5) Yes, in my oppinion GLDSYJ and GLDGJ are much better candidates (WAY more
selective) but building indexes with either as the left most are typically
ignored (again, I've had this before because of BETWEEN and >=/<=
statements)  I don't know much about EVI's.  I have stayed away from them
because of their maintenance requirements.  Our AS400 is our OLTP system and
I've seen a recommendation to stay away from EVI on OLTP.

Thanks.

Ryan

"Wilt, Charles" <CWilt@xxxxxxxxxxxx> wrote in
message
news:F520B5C51DB10041B239BC06383A7EDC01D376D9@xxxxxxxxxxxxxxxxxxxxxxxxxx
> Ryan,
>
> A few questions:
>
> 1) Are these SQL defined tables and indexes or DDS physical and
> logicals?  SQL defined tables and indexes perform better due to a larger
> page size and less validation on the read.
>
> 2) Have you run the queries through Visual Explain (under Run SQL
> Scripts of iNav), does it recommend any additional indexes?
>
> 3) Are you up to date on DB & HIPER PTFs?  You may want to pass it to
> IBM as I don't think you should see a difference between
>
> Select <...>
> >From t1 inner join t2
> Where <criteria>
>
> And
>
> Select <..>
> >From t1 inner join t2 on <criteria>
>
> 4) How/where are these queries being run?  You may want to consider
> stored procedures and/or UDTs.
>
>
> 5) Lastly, you said your index begins with your least selective column,
> what is the most selective? What problems have you had when starting the
> index with the most selective?  Would GLDSYJ and GLDGJ be candidates for
> an EVI index, how many distinct values do they have?
>
>
>
> 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 11:14 AM
> > To: midrange-l@xxxxxxxxxxxx
> > 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.
>
>