Antwort: Re: [DB2-L] Antwort: [DB2-L] Avoid index usage

Roy Boxwell

Antwort: Re: [DB2-L] Antwort: [DB2-L] Avoid index usage
thats odd because what I did works great for me...I will go and play in my
sandbox for a minute or two...
OK I'm back. I just did the EXPLAINS and looked into the DSN_FILTER_TABLE
and see that

BINARY > 0 is MATCHING (also with Matchcols 3)
BINARY + 0 > 0 changes MATCHING to a STAGE2 (Matchcols 2) (For me that is
OK as the important point was the bad index the MATCHING used!)
BINARY > 0 +0 changes MATCHING to SCREENING (Matchcols 2)

Note that in my case there are three columns in the index the third being
binary which *was* being used as a Matchcols 0 access path (very bad!)
however it is all immaterial now as the three access paths *all* use the
same index and tables in the same sequence these days with no MATCHCOLS
0...looks
like an APAR/RUNSTATS "fixed" the problem and I can change my SQL back....

I also did some timings "for fun"....
n > 0 18 Secs
(n + 0) > 0 19 Secs
n > 0 + 0 22 Secs

obviously the above timings are +- 4 seconds for "background noise" and so
it looks like no real difference. (Originally the bad access was over 60
secs!)

weird....anyway I have changed all my temporary SQL back to the original
as I *hate* using tricks to get stuff to work as you want it...
Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert





Peter Vanroose <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
15.01.2009 14:35
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: Re: [DB2-L] Antwort: [DB2-L] Avoid index usage


Roy Boxwell <[login to unmask email]> wrote:

>AND (binarycolumn + 0) > 0

You should actually use
AND binarycolumn > 0 + 0
instead, to inhibit matching index access while still having this as a
stage-1 predicate!

[Actually, index use is not really inhibited: the index could still be
used
(in a non-matching way) e.g. to avoid a sort and/or when the access would
be
index-only.]

Example: the three following queries have respective access paths
(1) matching index scan on pno
(2) non-matching index scan on pno
(3) table scan (stage-2)

(1) SELECT max(name), count(*) FROM persons WHERE pno= :hv ;
(2) SELECT max(name), count(*) FROM persons WHERE pno= :hv+0 ;
(3) SELECT max(name), count(*) FROM persons WHERE pno+0= :hv ;

-- Peter.


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Troy Coleman

Re: Avoid index usage
(in response to Roy Boxwell)
I assumed the name "hint" was given because that is what ORACLE uses.
From a marketing/sales view we want to make sure the customer can check
the feature off for DB2 and ORACLE.

Troy


<snip>
>

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html