473,382 Members | 1,359 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

Need help with a performance of a query.


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.
Apr 17 '07 #1
6 2739
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
Apr 17 '07 #2
How about changing the sequence of the columns of an index ?
CREATE INDEX BDBPMAC_IDX0 ON BDBPMAC
(
ARTKEY0
, CATKEY1
)
;

Apr 18 '07 #3
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.
Apr 18 '07 #4
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: 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.
Apr 18 '07 #5
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: 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.
Apr 18 '07 #6
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



Apr 18 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
3
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going...
5
by: Alexander Korovyev | last post by:
Suppose I have two tables: CREATE TABLE Tab1 ( NOT NULL, NOT NULL, NOT NULL, NOT NULL) CREATE TABLE Tab2 ( NOT NULL,
3
by: gizmo | last post by:
I have a stored procedure that queries a database using a Select statement with some inner joins and conditions. With over 9 million records it takes 1 min 36 sec to complete. This is too slow...
4
by: bhbgroup | last post by:
I have a query on one large table. I only add one condition, i.e. a date (the SQL reads like 'where date > parameterdate'. This query is rather quick if 'parameterdate' is either explicitly...
11
by: my-wings | last post by:
I think I've painted myself into a corner, and I'm hoping someone can help me out. I have a table of books (tblBooks), which includes a field (strPubName) for Publisher Name and another field...
4
by: Steph | last post by:
Hi - Trying to chase down a baffling performance issue. Our database has been running very slow lately. So we are performance tuning the database. In doing so, we created a copy of our...
59
by: Rico | last post by:
Hello, I have an application that I'm converting to Access 2003 and SQL Server 2005 Express. The application uses extensive use of DAO and the SEEK method on indexes. I'm having an issue when...
14
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity....
8
by: rshivaraman | last post by:
Hi : I have a TableA with around 10 columns with varchar and numeric datatypes It has 500 million records and its size is 999999999 KB. i believe it is kb i got this data after running...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.