Oracle top-n query sort performance

The performance difference probably doesn't matter. The execution plan difference is because multi-column index accesses are only implicitly sorted if the leading columns use equality conditions.

Performance Difference

Don't worry too much about the cost of an execution plan. Even though it's called the "Cost Based Optimizer", the cost is a weird number that only a few people in the world fully understand.

One reason why it is complicated to compare explain plan costs is that the total cost is sometimes less than one of the child operation costs. As I explain in my answer here, this can happen with a COUNT STOPKEY operation. This is Oracle's way of saying "this child operation would cost this huge amount, but the COUNT STOPKEY will probably cut it off before it gets that high". It's usually best to compare the top cost of the plan, but even that number can sometimes be misleading, as other examples in that answer demonstrate.

Which means that normally the run time is the only thing that matters. If the A-Time (actual time) is only 0.1 seconds for both queries then your job is probably done here.

Execution Plan Difference

The difference in the execution plans are caused by the ways multi-column indexes are stored and accessed. Sometimes when an index is scanned the results will be automatically stored and sometimes they will not. That's why one plan has COUNT STOPKEY and the other has the more expensive SORT ORDER BY STOPKEY.

To demonstrate this plan difference, create a simple table and index with just 2 columns and 4 rows:

create table test1 as select 1 a, 10 b from dual union allselect 1 a, 30 b from dual union allselect 2 a, 20 b from dual union allselect 2 a, 40 b from dual;create index test1_idx on test1(a, b);begin    dbms_stats.gather_table_stats(user, 'TEST1');end;/

Below is a simplified idea of how the index is stored. The data is stored ordered by the leading column first, then ordered by the trailing column.

               +----+        +------+Root+-------+        |      +----+       |        |                   |      +-v-+               +-v-+   +--+A=1+--+         +--+A=2+--+   |  +---+  |         |  +---+  |   |         |         |         | +-v--+   +--v-+     +-v--+   +--v-+ |B=10|   |B=30|     |B=20|   |B=40| +----+   +----+     +----+   +----+

If the query only accesses one value in the leading column A, then it can read the values from column B in order without any extra effort. Oracle goes to one of the A blocks and then reads the B blocks in order without even trying.

Note how this query has an ORDER BY but there's no SORT in the execution plan.

explain plan for select * from test1 where a = 1 and b > 0 order by b;select * from table(dbms_xplan.display(format => 'basic'));Plan hash value: 598212486--------------------------------------| Id  | Operation        | Name      |--------------------------------------|   0 | SELECT STATEMENT |           ||   1 |  INDEX RANGE SCAN| TEST1_IDX |--------------------------------------

But if the query accesses more than one value in the leading column A, the results from B will not necessarily be retrieved in order. Oracle may read the A blocks in order, but the B block order is only true for one A value.

Now, an extra SORT ORDER BY operation shows up in the execution plan.

explain plan for select * from test1 where a in (1,2) and b > 0 order by b;select * from table(dbms_xplan.display(format => 'basic'));Plan hash value: 704117715----------------------------------------| Id  | Operation          | Name      |----------------------------------------|   0 | SELECT STATEMENT   |           ||   1 |  SORT ORDER BY     |           ||   2 |   INLIST ITERATOR  |           ||   3 |    INDEX RANGE SCAN| TEST1_IDX |----------------------------------------

This is why changing column1 = value1 to column1 in (value1, value2) may add an extra SORT operation.

Use EXISTS instead of IN.

Example:

EXISTS (select 1 from DUAL where TABLE_1.F='STATE1' or TABLE_1.F='STATE2')

Try and see if the plan is changed.

If you want to use NOT IN, make use of the hints HASH_AJ or NL_AJ.