Slow select using primary key
I have a table ( DISPATCH ) that has about 7M rows.It has a primary key on an integer column (DIS_ID) with index DIS_PK.
It takes 20-30~ seconds to run this query : select dispatch_date from DISPATCH where dis_id = 10443. On the execution plan, by default, Oracle uses FULL TABLE SCAN. When I force it to use index with hint, the execution plan change to FULL INDEX SCAN, and it takes about 20 seconds to execute. That is too slow. We have another table, with similar structure, similar query which only takes < 0.1 second to execute. I have gathered statitics for the schema but no better result was found.
Does anybody else know what I should try next.
Thanks,
Nguyen.
Edited by: user1152556 on 00:09 17-01-2013
Edited by: user1152556 on 00:10 17-01-2013