Does an IOT guarantee order in a select?
No.
The only thing that guarantees result set order is an ORDER BY
clause in your query.
This is a popular question about SQL so it's worth repeating what I've written in response to similar questions about SQL Server and MySQL:
In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.
In Oracle, an Index-Organized Table (IOT) will minimize the amount of work the database has to do to get you the data sorted in the index's sort order. Though you may find that Oracle tends to return rows selected from an IOT in that same order, you are only guaranteed that order when you query the IOT with an ORDER BY
clause.
Does an IOT guarantee order in a select?
No, order is not guaranteed without ORDER BY
. Ever.
But you can achieve what you want like this:
select * from (select * from my_table order by priority) where rownum < 2;
This will not necessarily cause Oracle to do more work:
create table foo(priority, id, primary key (priority,id)) organization index as select mod(level,9), level from dual connect by level<=100000;
select /*+ gather_plan_statistics */ * from (select * from foo order by priority) where rownum<2;
PRIORITY | ID -------: | -: 0 | 9
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
| ------------------------------------------------------------------------------------------------- | | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | | ------------------------------------------------------------------------------------------------- | | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | | |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 2 | | | | 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | | | | 3 | INDEX FULL SCAN| SYS_IOT_TOP_26495 | 1 | 100K| 1 |00:00:00.01 | 2 | | | ------------------------------------------------------------------------------------------------- | | | | Predicate Information (identified by operation id): | | --------------------------------------------------- | | | | 1 - filter(ROWNUM<2) | | |
dbfiddle here
The meaning of the COUNT STOPKEY
is that Oracle does not have to go through all the rows of the FULL SCAN
No. The only way to guarantee the order of results is to add an ORDER BY
.