Re: Index Ordered Table ordering

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: Thu, 05 Feb 2004 07:22:55 -0500
Message-ID: <dcqUb.232$hJ6.210@fe01.usenetserver.com>

Kris Jenkins wrote:
> Ed prochak wrote:
>

>> Kris Jenkins <krisajenkins_at_yahoo.co.uk> wrote in message 
>> news:<401e2c88$0$16460$cc9e4d1f_at_news.dial.pipex.com>...
>>
>>> Hi,
>>>
>>> Is there a way to choose the order of Index Ordered Tables?
>>>
>>> I've got a table like this:
>>>
>>> CREATE TABLE prices (
>>>     price        integer,
>>>     id        integer
>>>     CONSTRAINT prices_pri PRIMARY KEY ( price, id )
>>> ) ORGANIZATION INDEX;
>>>
>>> ...and this gives me a table that is effectively 'ORDER BY price ASC, 
>>> id ASC'.  What I'd really like is to have it natively stored as 
>>> 'ORDER BY price *DESC*, id ASC'.
>>>
>>> Is that possible?
>>>
>>> If not I'll store price as (-1 * price), but that's not ideal.
>>>
>>> Cheers,
>>> Kris
>>
>>
>>
>>
>> you shouldn't care until you query the data. Stop thinking of ORACLE
>> as a file system and think in terms of tables (ie UNORDERED SETS of
>> data). Besides your initial assumption is wrong. The indices do not
>> have any special ordering.

>
>
> I thought that was the whole point of an Index-Ordered-Table (IOT) -
> isn't that what the keywords mean?

The O doesn't stand for Ordering. I understood ORGANIZATION INDEX to mean that the data is small enough to fit as part of the index. IOW, when you query the table, the data is fetched from the index, eliminating the need to do a second disc read.

Daniel, can you correct me?

>> Fact is, that index would work just fine for a query like:
>>
>> SELECT * FROM prices_pri
>> WHERE price BETWEEN 0.01 AND 0.99
>> ORDER BY price DESC, id ;

>
>
> True, but I'm only ever interested in the highest or lowest price right
> now. That price will change as rows get inserted/deleted, but on a
> query, I only really care about the highest/lowest.
>
>
> Thanks for the reply though, ed :-)

And the point is that the optimizer can find the max() and min() based on the index very quickly. That's true of any index. That the table is IOT means that other columns come along for free.

Consider the query
SELECT MAX(price), id
FROM prices_pri
GROUP BY id
ORDER BY MAX(price) DESC, id ;

Run that against a plain table, a table with an index on price and on an IOT version. Look and the trace log and note the I/O in particular.

HTH

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Thu Feb 05 2004 - 06:22:55 CST