The application team is running a query which is taking long time--
SELECT * FROM TWEB WHERE ADD_DT <( DATE ( CURRENT DATE )
- 13 MONTHS )
i did explain on the query and it shows the cost is very high .also it is going in tablespace scan.
the table staructure is as follows--
CREATE TABLE DBV008.TWEB
(QUOTE_ASSOC_NUM INTEGER NOT NULL,
ADD_DT DATE NOT NULL,
PRIMARY KEY (QUOTE_ASSOC_NUM))
DATA CAPTURE NONE
APPEND NO ;
there are around 15746732 rows in table.
there is a unique clustering index defined on QUOTE_ASSOC_NUM
while there is no index defined on ADD_DT.
the distinct number of rows in QUOTE_ASSOC_NUM and ADD_DT are 15746732 and 384 respectively.so creating an index on ADD_DT would be of no help.
i tried creating index on ADD_DT same on test but still it goes in tablepsace scan.
could someone let me know how to tweak the query??