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.
Best Answer
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 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.