Folks,
I am having trouble with a query. DB2 does not use index, it does relation
scan of the table.
I am running DB2 UDB 8.2 on Fedora Core release 4 (Stentz)
# db2level
DB21085I Instance "dbap68" uses "32" bits and DB2 code release "SQL08015" with
level identifier "02060106".
Informational tokens are "DB2 v8.1.0.48", "s040212", "MI00069", and FixPak "5".
Product is installed at "/opt/IBM/db2/V8.1".
Here is the table definitions:
CREATE TABLE BDBPMAC
(
ARTKEY0 INT NOT NULL
, CATKEY1 CHAR (19) NOT NULL
, PLAN_STAT_UPD_IND CHAR (1) NOT NULL
, MAINT_EIBDATE CHAR (7) NOT NULL
, MAINT_EIBTIME CHAR (8) NOT NULL
, RECORD_ID CHAR (3) NOT NULL
) IN BDBTBLSPACE INDEX IN BDBTBLSPACE;
CREATE INDEX BDBPMAC_IDX0 ON BDBPMAC
(
CATKEY1
, ARTKEY0
)
;
ALTER TABLE BDBPMAC ADD CONSTRAINT BDBPMAC_FK FOREIGN KEY
(
ARTKEY0
)
REFERENCES BDBPMAD(ARTKEY0) ON DELETE CASCADE;
When I run following query, DB2 does relation scan instead of index scan:
SELECT PLAN_STAT_UPD_IND,
MAINT_EIBDATE,
MAINT_EIBTIME,
RECORD_ID
FROM BDBPMAC
WHERE ARTKEY0 = 657
ORDER BY CATJEY1
Here is the output of reorgchk:
# db2 reorgchk current statistics on table apdev68.bdbpmac
Table statistics:
SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
APDEV68 BDBPMAC 1076500 0 4256 4256 - 55978000 0 40 100 -*-
----------------------------------------------------------------------------------------
Index statistics:
SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: APDEV68.BDBPMAC
APDEV68 BDBPMAC_IDX0 1e+06 1999 0 3 23 0 1e+06 87 52 79 0 0 -----
-------------------------------------------------------------------------------------------------
Here is the output of db2expln:
# db2expln -f tmp/q -d lidp -t
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "APDEV68"
SQL Statement:
SELECT PLAN_STAT_UPD_IND, MAINT_EIBDATE,
MAINT_EIBTIME, RECORD_ID
FROM BDBPMAC
WHERE ARTKEY0 =657
Section Code Page = 1208
Estimated Cost = 10403.719727
Estimated Cardinality = 0.995036
Access Table Name = APDEV68.BDBPMAC ID = 5,5
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 1
Return Data to Application
| #Columns = 4
End of section
If I create another index for ARTKEY0 only then it will use the index.
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail:
No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.