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: NoJunkMailshah@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.

Hemant Shah wrote:
> 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.
>

ARTKEY0 is an integer ascending keys index which is *NOT* most
significant key - so what makes you think that DB2 could use this index
in predicate like ARTKEY0=657?

Jan M. Nelken

How about changing the sequence of the columns of an index ?
CREATE INDEX BDBPMAC_IDX0 ON BDBPMAC
(
ARTKEY0
, CATKEY1
)
;

Your problem is a lot more severe than what you've run into with the query.

You defined a foreign key with delete cascade on ARTKEY0. This means
that when you delete a row from the BDBPMAD table, the matching rows in
BDBPMAC will be deleted. This will cause a table scan on BDBPMAC because
there is no index on the table having ARTKEY0 as its first column.

Assume that the optimizer decided to use your existing index for the
query. It will have to scan the entire index to locate rows containing
matching ARTKEY0 rows. Each row will then need to be retrieved. Since a
table scan was selected by the optimizer; it's safe to assume that it
decided that it would cost less to do that rather than scan the index
and retrieve individual rows.

An index on ARTKEY0 may still not be used for the query. If the
optimizer decides that enough rows will be retrieved, and that they will
be dispersed throughout the table, it may still choose to use a scan
instead of fetching individual rows.

Phil Sherman


Hemant Shah wrote:
> 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.
>

While stranded on information super highway Tonkuma wrote:
> How about changing the sequence of the columns of an index ?
> CREATE INDEX BDBPMAC_IDX0 ON BDBPMAC
> (
> ARTKEY0
> , CATKEY1
> )
> ;
>


Yep, re-arranging the order of columns fixed the problem. I wonder why the
optimizer was not using index if CATKEY1 was first.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@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.

While stranded on information super highway Jan M. Nelken wrote:
> Hemant Shah wrote:
>> 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.
>>
>
> ARTKEY0 is an integer ascending keys index which is *NOT* most
> significant key - so what makes you think that DB2 could use this index
> in predicate like ARTKEY0=657?
>

I am not sure how DB2 optimizer works, so I did not know that the column
in WHERE clause should be the most significant key.


> Jan M. Nelken

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: NoJunkMailshah@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.

Hemant,

BDBPMAC_IDX0 is defined on two columns. In your original query if you
had used CATKEY1 also as a criteria, I believe that optimizer would do
index scan.

Try something like :

SELECT PLAN_STAT_UPD_IND,
MAINT_EIBDATE,
MAINT_EIBTIME,
RECORD_ID
FROM BDBPMAC
WHERE ARTKEY0 = 657 and

CATKEY1 not like ' '

ORDER BY CATKEY1 ;



See if it does the job...

Regards,

Mehmet