Tablespace scan instead of index selection

Steve Schuering

Tablespace scan instead of index selection
I don't understand why the optimizer is doing a TBSP scan instead of using
an existing index.

The query is
SELECT CD_GAS_CATEGORY ,
CD_GAS_COND_TYPE
FROM CIMSENV1.CU04TB62
WHERE
KY_SO_NO = ? AND
KY_PREM_NO = ?

The index I think it should use is a non-unique index with these stats

INDEX=CIMSENV1.C04T62X4
Column Seq Lth DatTyp
KY_SO_NO 1 4 INTEGER
KY_PREM_NO 2 4 INTEGER

The Table contains 1,163,636 rows. The index is in 3 levels. Firstkeycard
is 2 and FULLKEYCARD 764,441.

We have the same query going against a clone of this Table and it uses the
same index. This Table has a few more rows but nothing significant. We're
getting around the problem with a Hint.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sushanta Dash

Re: Table space scan instead of index selection
(in response to Steve Schuering)
Hi Steve,

The total number of records in the table is not provided.

The optimizer decides to go for TS scan if it estimates the number of
I/Os to data page for index is more than the I/O s for TS scan.

This is usually seen when the non unique index with low cardinality and
low cluster ratio. So with less clustered with low cardinality the
estimation of I/O by the optimizer makes the access path a TS scan. So I
would suggest you to first see how current your are with statistics and
access path is according to that statistics or not, second determine the
clusterratiof for this index and try to determine whether this
particular index needed a reorg. If the reorg of this index is improving
your cluster ratio then the access path would fall back to index scan.



Thanks
Sushant Dash
Lead DBA
Fidelity Business Services India Pvt. Ltd.,
Embassy Golf Links Business Park,
Off Intermediate Ring Road,
Bangalore - 560 071.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Steve Schuering
Sent: Monday, December 04, 2006 4:06 PM
To: [login to unmask email]
Subject: [DB2-L] Tablespace scan instead of index selection

I don't understand why the optimizer is doing a TBSP scan instead of
using
an existing index.

The query is
SELECT CD_GAS_CATEGORY ,
CD_GAS_COND_TYPE
FROM CIMSENV1.CU04TB62
WHERE
KY_SO_NO = ? AND
KY_PREM_NO = ?

The index I think it should use is a non-unique index with these stats

INDEX=CIMSENV1.C04T62X4
Column Seq Lth DatTyp
KY_SO_NO 1 4 INTEGER
KY_PREM_NO 2 4 INTEGER

The Table contains 1,163,636 rows. The index is in 3 levels.
Firstkeycard
is 2 and FULLKEYCARD 764,441.

We have the same query going against a clone of this Table and it uses
the
same index. This Table has a few more rows but nothing significant.
We're
getting around the problem with a Hint.

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm