2

I am using Oracle (Enterprise Edition 10g) and I have a query like this:

SELECT * FROM (
SELECT * FROM MyTable
  ORDER BY MyColumn
) WHERE rownum <= 10;

MyColumn is indexed, however, Oracle is for some reason doing a full table scan before it cuts the first 10 rows. So for a table with 4 million records the above takes around 15 seconds.

Now consider this equivalent query:

SELECT MyTable.*
FROM
  (SELECT rid
  FROM
    (SELECT rowid as rid
    FROM MyTable
    ORDER BY MyColumn
    )
  WHERE rownum <= 10
  ) 
INNER JOIN MyTable
ON MyTable.rowid = rid
ORDER BY MyColumn;

Here Oracle scans the index and finds the top 10 rowids, and then uses nested loops to find the 10 records by rowid. This takes less than a second for a 4 million table.

  • My first question is why is the optimizer taking such an apparently bad decision for the first query above?
  • An my second and most important question is: is it possible to make the first query perform better. I have a specific need to use the first query as unmodified as possible. I am looking for something simpler than my second query above. Thank you!

Please note that for particular reasons I am unable to use the /*+ FIRST_ROWS(n) */ hint, or the ROW_NUMBER() OVER (ORDER BY column) construct.

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
Marquez
  • 5,891
  • 3
  • 31
  • 40
  • What happens if you remove the outer `SELECT MyTable.* FROM ( ... ) INNER JOIN ...` and just keep the inner `SELECT rid FROM ...`? Does it still use the index then? You can also try the `INDEX` hint in your first query: `SELECT /*+ INDEX(MyTable) */ * FROM MyTable ORDER BY MyColumn` – Mr. Llama Oct 30 '14 at 21:04
  • possible duplicate of [Why is Oracle ignoring index with ORDER BY?](http://stackoverflow.com/questions/13497887/why-is-oracle-ignoring-index-with-order-by) – Mr. Llama Oct 30 '14 at 21:06
  • A main goal is to get the other columns returned not just the rowid. Thanks. – Marquez Oct 30 '14 at 21:19
  • Mr. Llama, using ROW_NUMBER() OVER (ORDER BY column) produces a different execution plan than my first query above, so I do not think this is a duplicate of that other question. – Marquez Oct 30 '14 at 21:23
  • Why can't you use `row_number()`? That sounds like a really strange limitation you put on yourself. –  Oct 30 '14 at 23:11

1 Answers1

1

If this is acceptable in your case, adding a WHERE ... IS NOT NULL clause will help the optimizer to use the index instead of doing a full table scan when using an ORDER BY clause:

SELECT * FROM (
SELECT * FROM MyTable
WHERE MyColumn IS NOT NULL
--    ^^^^^^^^^^^^^^^^^^^^
ORDER BY MyColumn
) WHERE rownum <= 10;

The rational is Oracle does not store NULL values in the index. As your query was originally written, the optimizer took the decision of doing a full table scan, as if there was less than 10 non-NULL values, it should retrieve some "NULL rows" to "fill in" the remaining rows. Apparently it is not smart enough to check first if the index contains enough rows...

With the added WHERE MyColumn IS NOT NULL, you inform the optimizer that you don't want in any circumstances any row having NULL in MyColumn. So it can blindly use the index without worrying about hypothetical rows having NULL in MyColumn.


For the same reason, declaring the ORDER BY column as NOT NULL should prevent the optimizer to do a full table scan. So, if you can change the schema, a cleaner option would be:

ALTER TABLE MyTable MODIFY (MyColumn NOT NULL);

See http://sqlfiddle.com/#!4/e3616/1 for various comparisons (click on view execution plan)

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125