Optimizing select top N query in oracle

Question

I have query with sort and rownum to get top N record in big table (with more than 21M records). I use order and rownum for select n recent records like below. I defined index on nullable create_date column but the execution time was more than 30s. How can I improve this time?

select * from(
  select *
  from my_table t
  order by t.create_date
)
where rownum<N

Explain Plan:

----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name             | Rows     | Bytes       | Cost    | Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                  |        9 |       31446 | 1153573 | 03:50:43 |
| * 1 |   COUNT STOPKEY           |                  |          |             |         |          |
|   2 |    VIEW                   |                  | 21335585 | 74546533990 | 1153573 | 03:50:43 |
| * 3 |     SORT ORDER BY STOPKEY |                  | 21335585 |  4544479605 | 1153573 | 03:50:43 |
|   4 |      TABLE ACCESS FULL    |     MY_TABLE     | 21335585 |  4544479605 |  165097 | 00:33:02 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(ROWNUM<10)
* 3 - filter(ROWNUM<10)

Please append the explain plan - run EXPLAIN PLAN FOR your-query then SELECT * FROM table(DBMS_XPLAN.Display), and copy a result of last query (as a plain text - don't take a printscreen) and append it to the question. — krokodilko
Please note that 11g is not a version identifier, it is only a marketing name used by Oracle. The version is either 11.1 or 11.2 (and often the full version number, as in 11.2.0.2.0, is relevant too). To find out the full version number, if you don't know it, you can run select * from v$version. — mathguy

bot403 bot403 · Accepted Answer · 2017-08-19T19:12:14

You could try the first_rows hint.

FIRST_ROWS(n)

The hints FIRST_ROWS(n) (where n is any positive integer) or FIRST_ROWS instruct Oracle to optimize an individual SQL statement for fast response. FIRST_ROWS(n) affords greater precision, because it instructs Oracle to choose the plan that returns the first n rows most efficiently. The FIRST_ROWS hint, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability.

For example, the optimizer uses the cost-based approach to optimize this statement for best response time:

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id FROM employees WHERE department_id = 20;

Optimizing select top N query in oracle

1 Answers