Re: Index Ordered Table ordering

ed.prochak_at_magicinterface.com (Ed prochak) writes:

>>I've got a table like this:
>>
>>CREATE TABLE prices (
>> price integer,
>> id integer
>> CONSTRAINT prices_pri PRIMARY KEY ( price, id )
>>) ORGANIZATION INDEX;

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

But isn't it the case that an 'order by' for the same columns as the index can be done faster than an 'order by' for different columns?

So if the index sorts by price, and it's an IOT, then 'select * from t order by price' can read the rows in price order and not perform an extra sorting step. Whereas 'order by something_else' would need to both read the rows and sort them.

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

It would work, but would the query be as fast as one where 'order by' follows the order of the index?

-- 
Ed Avis <ed_at_membled.com>