I have a monster sized non-partitioned table. I recently updated the statistics as on it as well.
The primary key is on a char field called "ID".
SELECT * FROM "MYDATA" WHERE "ID" = '0000492319'
The plan is saying TABLE ACCESS (FULL) and has a filter predicate for the the ID. This results in a query that takes 8 seconds to run.
If I give the optimizer a hint to use the primary key, the query takes 1.6 seconds to run.
Its bizarre to me that I should need to provide this hint. The indexed plan estimates a lower cost, and the optimizer should be aware of this.
Here is the filter predicate:
NLSSORT(INTERNAL_FUNCTION(ID),'nls_sort="JAPANESE_M"')=HEXTORAW('017...')
The database NLS_SORT is set to JAPANESE_M and the NLS_CHARAACTERSET is JA16SJIS.
So nothing seems to be mismatched that would cause a special sort function to be called. Its a bit odd though.
One more piece of information, if I select only the "ID" column in my query then the planer chooses INDEX (FAST FULL SCAN) automatically.
The problem only arises when I use select *.
Oracle Database Verion: 10.2.0.5.0.