Hi All,

I have the below query:

select TRAN_ID, TRAN_STAS, TRAN_DTS, UPDT_BY_USER
from D01SUNI1.TPRDCT_ACS_TRAN
where tran_dts <= '2006-10-01 00:00:00'
order by tran_id desc;

and the table structure is as follows:
CREATE TABLE D01SUNI1.TPRDCT_ACS_TRAN
(TRAN_ID INTEGER NOT NULL,
TRAN_STAS INTEGER NOT NULL,
TRAN_DTS TIMESTAMP,
UPDT_BY_USER VARCHAR(20)
)


With a couple of indexes one for the TRAN_ID column and one index for the TRAN_DTS column. This table has only 17000 rows but I wonder how can I re-write the query in order to avoid scanning the whole table everytime we execute the query above. Using Fetch first row will only cut the number of rows sent back to the application so I was trying to work out how to improve the query. thanks.

I f you have not done a RUNSTATS on the table, that can cause a table scan. Also, you where clause is the main factor in determining how a table is accessed. If you have done RUNSTATS and you have an index on the column in the where clause, as it appears you have, and you are still getting table scans, it is probably because DB2 thinks it is still more prudent to do that anyway. Lets take you query for an example of this. I will assume you have done RUNSTATS and they are current. Your where clause is asking for all rows where the timestamp is less than or equal to a particular value. Now if the stats show that vast majority of the rows in the table would qualify, as I suspect they will for your query, and the fact that you have asked for columns not in the index, DB2 thinks it would be better just to scan the table instead of using the index. To make this a little clearer, suppose your table is using 100 pages for the data and the index uses 5 pages. It looks like the index would be the best way to access the data, but if DB2 thinks that 95% of the rows will be returned, then doing a table scan is clearly the way to go. To use the index in this case, DB2 will read the 5 index pages and still have to read the 95 data pages for a total of 100 pages which is the same as a table scan. So it might just think to do a table scan anyway.

HTH

Andy

Hi

Why you are looking for Index scan. Is it any specific reason. Whether this query is taking lot of time ?

Thanks

Nagesh