Ed Avis wrote:
> 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?
>
Since, in this example, PRICE is the first field of the INDEX, then ORACLE
knows it can access it in index order. (Which, I believe is a tree, so
ascending or decending order takes the same amount of time). The extra SORT
might be needed due to ID being requested with the opposite order. But until
the response time fails to meet the requirements of the application, I Would
Not Worry About It.
--
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 Mon Feb 09 2004 - 00:16:06 CST
