We have a table that has an index on three columns, say A, B, C columns in ascending order.
We need to retrieve the data paginated.
It is a large table > 150 million rows.
The query we are using is this:
select * from large_table order by A, B, C fetch first 100 rows only;
This is taking almost 2 min to run and the reason for that is that the order by is taking too long.
My question here is why Oracle is trying to order the columns if they are already ordered in the index? Why doesn't it reads the index first and get the 100 rows without attempting to order by the full dataset? Is there a way to read the rows based on the index?
NOTE: I also tried to replace the index for BTree index with no improvement.
In order for Oracle to consider using an index when your query does not apply any predicate to the index columns, at least one of the index columns must be defined as NOT NULL. If all the indexed columns are NULLable, then without a query predicate on any of them rows with all NULLs in the indexed columns would be candidate rows you would want to retrieve, but such a row cannot be found via the index, since Oracle does not store such all-NULL key values in b-tree indexes. Therefore Oracle cannot safely use the index to find the rows you are asking for.
Having said that, even with a viable index Oracle may not choose to use it, preferring a full table scan, particularly if you are asking for some non-indexed columns in your SELECT clause. If this happens and it's not fixed by stats gathering, you can force index use with a hint:
SELECT /*+ INDEX(large_table indexname_on_largetable) */ . . .
To get the sorting benefit of the index, the ORDER BY must match the index column list exactly, in the right order, and in the same direction as the index scan (e.g. ORDER BY ASC would require INDEX_ASC (the defaults), but ORDER BY DESC would require INDEX_DESC)