Hi All,
I am using the following sql in our production and the DBA is saying it is doing tablescans instead of indexscan though it has index on where criteria because of IN criteria has 60(atmost) values. Unfortunatly I can't avoid this sql, any suggestions to improve the performance.

SELECT ACCOUNTNUMBER,NAME,PARTYINDEX
FROM SAMPLETABLE WHERE ACCOUNTNUMBER IN('1','2','3',...'60');

I searched on the web, I don't find any links saying that use of IN clause causes tablescans, any thoughts?

Thanks
Shri

the only tool that can show the access path is explain

The point is that the optimizer sees 60 individual values, while actually they are consecutive (logically speaking). Apparently, one table scan is cheaper than 60 individual index scans (thinks the optimizer, based on what he sees in the statistics).
Assuming your "accountnumber" column is indeed textual, and its length is 1 for numbers up to 9, 2 for numbers up to 99, etc., there's not much you can do to improve this since the index on accountnumber will have those values sorted as
1 10 100 101 ... 109 11 110 111 112 ... 12 121 122 ... 13 ... 14 .... 199 2 20 200 ....
which would give a completely incorrect result when using "accountnumber between '1' and '60'".
One option *might* be to make your column numeric (int).
Second option: creating an index "on expression" which is actually one on "lpad(accountnumber, 10)", then using the conditionThird option: let your DBA generate column statistics for individual values (using RUNSTATS) so that the optimizer finds out that it's cheaper to do 60 matching index scans than a single table scan.
Fourth option (not really a useful one, but still, if that would force the optimizer into the optimal access path...)