Re: Index Ordered Table ordering

From: Kris Jenkins <krisajenkins_at_yahoo.co.uk>
Date: Wed, 04 Feb 2004 15:19:02 +0000
Message-ID: <40210d61$0$21304$cc9e4d1f@news.dial.pipex.com>

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 ORGANIZATION INDEX keywords mean?

> 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 :-) Received on Wed Feb 04 2004 - 09:19:02 CST