We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1).

The priority does not matter for the inserts or updates i.e. not part of the primary key, which I'll enforce separately.

We basically don't want to have to do an order by over a range scan 170 times a second, as the number executed will drop massively.

Does an index organised table guarantee that priority = 1 will always come before priority = 9 when running the following query:

select *
  from my_table
 where rownum < 2

For slightly more context a typical query would be:

select *
  from my_table
 where mod(to_number(to_char(tstamp,'ss')),1) = 0
   and done is null
   and country = 'gbr'
   and rownum < 2

The pk constraint, for the IOT, would become priority, rest of the pk with a separate constraint on the pk solely for structure. done is null in approximately 99% of the table so this isn't very selective anyway.

Main index used is, I think, country, done, to_number(to_char(tstamp,'ss'), we tested about 20 combinations and this came up top by a long way.

I am completely unwilling to add any time at all to these queries, 0.01s a day added by select is 41 minutes a day. We'd much rather settle for "good enough" than perfection.

  • 2
    When you add an ORDER BY priority (as in Jack's example), you find your SELECT queries run much slower? What indexes do you have on the table? Commented Mar 7, 2012 at 17:23
  • you are proposing adding priority to the IOT as a leading column? Commented Mar 7, 2012 at 17:25
  • @NickChammas, the process is slow enough already I have no wish to add more (CPU) time; "good enough" is good enough.
    – Ben
    Commented Mar 7, 2012 at 17:38
  • @JackDouglas, yes, definitely.
    – Ben
    Commented Mar 7, 2012 at 17:39
  • 4
    @Ben - is adding the order by clause actually bolloxing up your query plans or is the performance issue a hypothetical one? Commented Mar 7, 2012 at 17:50

4 Answers 4



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.

  • I thought so, but had hoped not :-(. Thanks.
    – Ben
    Commented Mar 7, 2012 at 17:09
  • 2
    I guess parallelism would destroy order if you don't request it.
    – usr
    Commented Mar 7, 2012 at 18:31
  • 1
    Also ORDER SIBLINGS BY can provide a guarantee about result order in a hierarchical query.
    – Benoit
    Commented Mar 7, 2012 at 20:31
  • 1
    I can't reinforce Nick's comments more. Developers assuming implicit ordering can be the source of some pretty horrific bugs.
    – Philᵀᴹ
    Commented Mar 8, 2012 at 9:17
  • 3
    Specifically in the Oracle 9i or 10g timeframe, when Oracle added hash grouping. All sorts of SQL broke because what used to do SORT GROUP BY no longer did.
    – Adam Musch
    Commented Mar 9, 2012 at 21:33

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;
-------: | -:
       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.


For Microsoft SQL Server, the answer is also no, and the mechanics of it are pretty interesting. The Books Online page about scanning explains the concept of merry-go-round scans in the Advanced Scanning section:

For example, assume that you have a table with 500,000 pages. UserA executes a Transact-SQL statement that requires a scan of the table. When that scan has processed 100,000 pages, UserB executes another Transact-SQL statement that scans the same table. The Database Engine schedules one set of read requests for pages after 100,001, and passes the rows from each page back to both scans. When the scan reaches the 200,000th page, UserC executes another Transact-SQL statement that scans the same table. Starting with page 200,001, the Database Engine passes the rows from each page it reads back to all three scans. After it reads the 500,000th row, the scan for UserA is complete, and the scans for UserB and UserC wrap back and start to read the pages starting with page 1.

There's more details in the Books Online page.

  • When does the merry-go-round scan actually come into play? I've tried to see this previously on my developer machine but couldn't. Does it only happen at read uncommitted for example or have some other caveats to it? Commented Mar 10, 2012 at 13:52
  • @MartinSmith - you can read the full details in the Books Online page. You need to be on Enterprise Edition (not Std) and multiple queries need to be scanning a table that's too big to fit in memory.
    – Brent Ozar
    Commented Mar 13, 2012 at 12:45
  • I tested on developer edition (so I assume that counts as Enterprise edition) with a huge table much greater than the amount of RAM and still couldn't get this to happen hence wondering if there were some other caveats. I might give it another go then if you think there aren't. Commented Mar 13, 2012 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.