[DB2-L] Betr: Re: [DB2-L] Is this access path correct?

Walter Janißen

[DB2-L] Betr: Re: [DB2-L] Is this access path correct?
Hi

I think, you can create as much indexes as you want, you won't get a matching index access for this query (as Terry already pointed out). May be you can get a non-matching index-only access.

To get a better access path, you have to rewrite that query as I already suggested, but you said, you can't, because it is a generated query. So make the vendor change their tool, to generate better queries or get rid of that tool.


Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
Tel.: +49 211 477-2928
Fax: +49 211 477-2615
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Roy Boxwell
Gesendet: Donnerstag, 3. Dezember 2009 10:49
An: [login to unmask email]
Betreff: Re: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?


varchar and PADDED is not a good mix in this case. I would try the following steps

Create a new COLG index as NOT PADDED, Runstat it with FREQVAL and EXPLAIN the query again



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



Harry Garagoski <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>

03.12.2009 08:32
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie
Thema
[DB2-L] Betr: Re: [DB2-L] Is this access path correct?






Hello Roy,


COLG = VARCHAR and
we are running V9




Index for COLF :



Sel Column Name Seq No O Col Type Length Scale Null Def FP Col Card
* * * * * * * * * *
--- ------------------ ------ - -------- ------ ------ ---- --- -- -----------
COLF 1 A CHAR 16 0 N N N 290607


Details for index : ABC

On table : CM_BK
Index space ID (ISOBID) : 64 Index type : Type 2 index
Index subpage size(bytes): 4 Max. ds piece size (KB) : 2097152
Number of columns in key : 1 Clustering ratio : 100
Is it a clustering index : Yes Table clustered by index: Yes
Unique rule : Primary index
Close rule for data set : Y - The data sets are closed after use
Erase rule for data set : N - Data set is NOT erased when dropped
Can the index be copied : No Created TS: 2009-06-19-07.34.32.186212
Oldest data version : 0 Altered TS: 2009-06-19-07.34.32.186212
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : N/A
Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No
Oldest data version : 0 Altered TS: 2009-06-19-07.34.32.186212
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : N/A
Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No

Statistical data : RUNSTATS timestamp: 2009-12-02-16.38.17.274174
Allocated space (KB) : 10224 (Float: 1.022400000000000E+04)
First key column count : 290607 (Float: 2.906070000000000E+05)
Number of distinct keys : 290607 (Float: 2.906070000000000E+05)
Number of leaf pages : 2169
Number of index levels : 3
Average key length : 16
Data repeat factor : 8.542000000000000E+03


Index for COLG :

Sel Column Name Seq No O Col Type Length Scale Null Def FP Col Card
* * * * * * * * * *
--- ------------------ ------ - -------- ------ ------ ---- --- -- -----------
CM_STR_VORD_ID 1 A VARCHAR 25 0 N 1 N 290607




Details for index : XYZ On table : CM_BK

Index space name : CM123TMP Buffer pool used : BP2
Index space ID (ISOBID) : 172 Index type : Type 2 index
Index subpage size(bytes): 4 Max. ds piece size (KB) : 2097152
Number of columns in key : 1 Clustering ratio : 0: No stats
Is it a clustering index : No Table clustered by index: No
Unique rule : Non unique - duplicates allowed
Close rule for data set : Y - The data sets are closed after use
Erase rule for data set : N - Data set is NOT erased when dropped
Can the index be copied : No Created TS: 2009-12-01-15.40.51.571636
Oldest data version : 0 Altered TS: 2009-12-01-15.40.51.571636
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : Padded
Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No
Oldest data version : 0 Altered TS: 2009-12-01-15.40.51.571636
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : Padded
Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No

Statistical data : RUNSTATS timestamp: 2009-12-02-16.38.17.274174
Allocated space (KB) : 19584 (Float: 1.958400000000000E+04)
First key column count : 290607 (Float: 2.906070000000000E+05)
Number of distinct keys : 290607 (Float: 2.906070000000000E+05)
Number of leaf pages : 3159
Number of index levels : 3
Average key length : 25
Data repeat factor : 2.898030000000000E+05




Regards,

Harry Garagoski



Van: Roy Boxwell <[login to unmask email]>
Aan: [login to unmask email]
Datum: 02-12-2009 15:28
Onderwerp: Re: [DB2-L] Is this access path correct?
Verzonden door: IDUG DB2-L <[login to unmask email]>


________________________________




are colg or even the colf VARCHAR by any chance?? and remember STATS STATS STATS and STATS

ps What version of DB2 are you running?



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 < http://www.seg.de/ >

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

Harry Garagoski <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>

02.12.2009 14:10
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie
Thema
[DB2-L] Is this access path correct?










We have a query like this on a 300.000 row table and there is a index on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort (Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------
01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT 0912011622
01 02 0 3 00 N NNNN YNNN SELECT 0912011622

Has any one an idea?


Regards,

Harry Garagoski




------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.



________________________________

< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >


------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.


________________________________

[cid:_2_13C2D4E813C2D1A00035E86DC1257681 ] < http://www.idug.org/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L