Indexing issue

kanha · New User Joined: 05 Nov 2005 Posts: 28

Explain shows that query1 uses index XTABLE12 but query2 does a TS scan. Can someone explain me why? & how to avoid the TS scan on query2?

Query:1
SELECT count(*)
FROM TABLE1
WHERE UPD_TIMESTAMP > '2011-03-25-09.25.55.415477'

Query:2
SELECT CONTRACT_NBR
FROM TABLE1
WHERE UPD_TIMESTAMP > '2011-03-25-09.25.55.415477'

The first index XPOM4011 is clustered & partitioned. I believe this is having some effect on indexing.

INDEX NAME COLUMN NAME COLSEQ ORD CLS
XTABLE11
CONTRACT_NBR 1 A Y
VNDR_NBR 2 A Y

INDEX NAME COLUMN NAME COLSEQ ORD CL
TABLE12
UPD_TIMESTAMP 1 A N
CONTRACT_STATUS 2 A N
CONTRACT_NBR 3 A N

GuyC · Posted: Mon Jul 25, 2011 1:56 pm

not enough info:
* Partitioning method, keys and ranges
* statistics for indexes,indexparts and column UPD_TIMESTAMP
* contents of PLANTABLE.PAGE_RANGE