|
View previous topic :: View next topic
|
| Author |
Message |
Anand Kumar
New User
Joined: 29 Aug 2007
Posts: 24
Location: chennai
|
|
|
|
Hi Experts,
We are facing some performance issues on the table GWXX276. We gave a simple query but still it dosent pick up the index. The below are the details.
The table is GWXX276, and the table definitions are as follows:
Code:
| Code: |
TABLE INDEXES
Index Name Clustering PgSize UniqueRule KeyColumn Col# KSeq Ord
GXX.GWIX2761 Y 4096 PRIMARY KEY
MN_KEY 1 1 A
KN_KEY 3 2 A
BB_KEY 2 3 A |
The table is divided into partitions, its definition is
TABLESPACE PARTITIONS
| Code: |
Prt PriQty SecQty Stogrp VCatNm FrePg PctFr ChkFl Compr GBPCa TrkMd
1 -1 -1 GWSG001 GW 0 0 Y
2 -1 -1 GWSG001 GW 0 0 Y
3 -1 -1 GWSG001 GW 0 0 Y
..
-- |
until
| Code: |
| 60 -1 -1 GWSG001 GW 0 0 Y |
and the partition limit keys are as follows:-
Prt LimitKey
1 319
2 320
..
--
52 370
53 371
54 372
55 373
56 374
57 375
58 376
59 377
60 378
We gave an explain on a simple query on this table,
| Code: |
SELECT
T276.MN_KEY
,T276.MSR_KEY
,T276.KN_KEY AS SLD_KN_KEY
FROM
GWXX276 T276
WHERE T276.MN_KEY = 370 |
The explain results are as follows:-
| Code: |
PROGNAME PLANNO METHOD CREATOR TNAME TABNO ACCESS
------+---------+---------+---------+---------+---------+---------+---
DSNESM68 1 0 GXX GWXX276 1 R
MATCHCOLS
+---------
0
|
According to me the query should be able to pick up the index, but it dosent do it. because of this we have performance problem. There are around 80 million records in the table and for each partition we have around 8 million rows.
Other information are as follows:-
Cluster ratio of the index GWIX2761 is 100
Please help us why the query is not picking up the index?
Thank you very much,
Regards,
Anand |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
| select page_range from plan_table |
if it says 'Y' then it's ok.
Why would DB2 need to go to the index ? By going to the tablepart the where clause is resolved.
Because no extra criteria are given and the query is not index-only, it is better to do a table(part)scan.
Is the index Partitioned ? |
|
| Back to top |
|
|
Anand Kumar
New User
Joined: 29 Aug 2007
Posts: 24
Location: chennai
|
|
|
|
Hi GuyC,
The index is partitioned |
|
| Back to top |
|
|
|
|
