Index not used only on a particular environment

[Victor]

Hello,
I have a table which has around 3 billion records on an env and a
simple query against it goes for a tablescan eventhough an index has
been defined and this happens only on an env.

Details are as below. Please advice
Query Considered :
select A.OPTN_CD from VIN_VEH_OPTNS as A WHERE A.DWH_UPD_TIMSTM <
(SELECT MAX(DWH_ETL_ST_TIMSTM) FROM DWH_CONTROL where DWH_SEQ_NM
='SSeq' and DWH_ETL_STATUS='S')

Indexes: (which is same on both env)
No Index on DWH_CONTROL table
Index on VIN_VEH_OPTNS
DWH_UPD_TIMSTM ASC

Env1 : where Index is used
Logically partitioned db having 4 logical partitions
VIN_VEH_OPTNS table has around 100 million records (this is a
partitioned table spread across 3 logical partition)
DWH_CONTROL table has around 1000 records (non-partitioned table
located on the 4th partition)
Access Plan:

RETURN
( 1)
|
BTQ
( 2)
|
NLJOIN
( 3)
/--------/ \-------\
GRPBY FETCH
( 4) (----)
| / \
BTQ RIDSCN Table:
( 5) ( 9) CARD
| | VIN_VEH_OPTNS
GRPBY SORT
( 6) ( 10)
| |
TBSCAN IXSCAN
( 7) ( 11)
| / \
Table: Index: Table:
CARD CARD CARD
DWH_CONTROL XIF4VIN_VEHOPTNS VIN_VEH_OPTNS

Env2: where Table scan is performed
Logically parititioned db having 8 logical parittions
VIN_VEH_OPTNS table has around 3 billion records(this is a partitioned
table spread across 7 logical partition)
DWH_CONTROL table has around 1000 records (non-partitioned table
located on the 8th partition)

Access Plan:
RETURN
( 1)
|
BTQ
( 2)
|
FETCH
(----)
/ \
RIDSCN Table:
( 4) CARD
| VIN_VEH_OPTNS
SORT
( 5)
|
IXSCAN
( 6)
/ \
Index: Table:
CARD CARD
XIF4VIN_VEHOPTNS VIN_VEH_OPTNS
Any suggestion is greatly appreciated.

Thanks, Victor

[Victor]

I've posted a wrong access plan for the Env2(where tablescan is
happening). Here is the correct one

Optimizer Plan:

RETURN
( 1)
|
BTQ
( 2)
|
NLJOIN
( 3)
/ \
BTQ TBSCAN
( 4) ( 3)
| |
GRPBY Table:
( 5) CARD
| VIN_VEH_OPTNS
TBSCAN
( 6)
|
Table:
CARD
DWH_CONTROL
Victor wrote:

Hello,
I have a table which has around 3 billion records on an env and a
simple query against it goes for a tablescan eventhough an index has
been defined and this happens only on an env.

Details are as below. Please advice
Query Considered :
select A.OPTN_CD from VIN_VEH_OPTNS as A WHERE A.DWH_UPD_TIMSTM <
(SELECT MAX(DWH_ETL_ST_TIMSTM) FROM DWH_CONTROL where DWH_SEQ_NM
='SSeq' and DWH_ETL_STATUS='S')

Indexes: (which is same on both env)
No Index on DWH_CONTROL table
Index on VIN_VEH_OPTNS
DWH_UPD_TIMSTM ASC

Env1 : where Index is used
Logically partitioned db having 4 logical partitions
VIN_VEH_OPTNS table has around 100 million records (this is a
partitioned table spread across 3 logical partition)
DWH_CONTROL table has around 1000 records (non-partitioned table
located on the 4th partition)
Access Plan:

RETURN
( 1)
|
BTQ
( 2)
|
NLJOIN
( 3)
/--------/ \-------\
GRPBY FETCH
( 4) (----)
| / \
BTQ RIDSCN Table:
( 5) ( 9) CARD
| | VIN_VEH_OPTNS
GRPBY SORT
( 6) ( 10)
| |
TBSCAN IXSCAN
( 7) ( 11)
| / \
Table: Index: Table:
CARD CARD CARD
DWH_CONTROL XIF4VIN_VEHOPTNS VIN_VEH_OPTNS

Env2: where Table scan is performed
Logically parititioned db having 8 logical parittions
VIN_VEH_OPTNS table has around 3 billion records(this is a partitioned
table spread across 7 logical partition)
DWH_CONTROL table has around 1000 records (non-partitioned table
located on the 8th partition)

Access Plan:
RETURN
( 1)
|
BTQ
( 2)
|
FETCH
(----)
/ \
RIDSCN Table:
( 4) CARD
| VIN_VEH_OPTNS
SORT
( 5)
|
IXSCAN
( 6)
/ \
Index: Table:
CARD CARD
XIF4VIN_VEHOPTNS VIN_VEH_OPTNS
Any suggestion is greatly appreciated.

Thanks, Victor

[Serge Rielau]

Why isn't the small table replicated?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Victor]

Thanks for a quick reply. I recently interited this database and it has
been designed as such so I may consider that option as well in future.
However when I see the access plan I dont see any big data movement
across data partiton and it shows only a tablescan hence I thought that
something else is going wrong.


Serge Rielau wrote:

Why isn't the small table replicated?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Serge Rielau]

Victor wrote:

Thanks for a quick reply. I recently interited this database and it has
been designed as such so I may consider that option as well in future.
However when I see the access plan I dont see any big data movement
across data partiton and it shows only a tablescan hence I thought that
something else is going wrong.

The benefit of a replicated table is that it can be indexed on each node.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Victor]

ok, Thanks. I would implement a replicated MQT and recheck.

Serge Rielau wrote:

Victor wrote:

Thanks for a quick reply. I recently interited this database and it has
been designed as such so I may consider that option as well in future.
However when I see the access plan I dont see any big data movement
across data partiton and it shows only a tablescan hence I thought that
something else is going wrong.

The benefit of a replicated table is that it can be indexed on each node.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/