Tuning table select SQL having a RAW column in Oracle 10g

Question

Hi all,

I have a table with several columns and a unique RAW column. I created an unique index on the RAW column.

My query selects all columns from the table (6 million rows).

when i see the cost of the query its too high (51K). and its still using INDEX FULL scan. The query do not have any filter conditions, its a plain select * from.

Please suggest how can i tune the query operation.

Thanks in advance.

Answer 1

Why are you hinting it to use the index if you're retrieving all columns from all rows? The index would only help if you were filtering on the indexed column. If you were only retrieving the indexed column then an INDEX_FFS hint might help. But if you have to go back to the data for any non-indexed columns then using the index at all becomes counterproductive beyond a certain proportion of returned data as you're having to access both the index data blocks and the table data blocks repeatedly.

Answer 2

So, your query is:

select /*+ index (rawdata idx_test) */
       rawdata.*
from   v_wis_cds_cp_rawdata_test rawdata

and you want to know why Oracle is choosing an INDEX FULL scan?

Well, as Alex said, the reason is the "index (raw data idx_text)" hint. This is a directive that tells the Oracle optimizer, "when you access rawdata, use an index access on the idx_text index", which means that's what Oracle will do if at all possible - even if that's not the best plan.

Hints don't make queries faster automatically. They are a way of telling the optimizer what not to do.

I've seen queries like this before - sometimes a hint like this is added in order to return the rows in sorted order, without actually doing a sort. However, if this was the requirement, I'd strongly recommend adding an ORDER BY clause in anyway, because if the hint becomes invalid for some reason (e.g. the index gets dropped or renamed), the sorting would no longer happen and no error would be reported.

If you don't need the rows returned in any particular order, I suggest you remove the hint and see if the performance improves.