Re: Can we optimize an ORDER BY clause?

Vincent Qing Yin wrote:
>
> Here's a small question for Oracle programmers.
>
> I come from a Sybase back ground. I noticed that Oracle doesn't have
> the concept of a "clustered" index. In Sybase, a clustered index
> forces the physical order of records on disk to be the same as the logical
> order of the index. In a SELECT statement, if I use an ORDER BY clause
> on the columns that have the clustered index, then the server immediately
> start to return rows without actually performing the sort (because the
> sort would be unnecessary.)
>
> In Oracle, is there a way I can optimize an ORDER BY clause so that
> the server will not build an intermediate result set for sorting?
>
> Thanks for any advice.
>
> --
>
> Q Vincent Yin | Repeat
> vyin_at_cs.ubc.ca | delete(next->bug);
> (604) 876-9096 (H) | Until 0 = 1;

Only if you are selecting columns that exist in an index - then Oracle will return the data from the index only and not have to touch the table, and since the index is already ordered, then no sort is necessary. However, in practice this is a rare situation, and the Oracle manual is very clear - if you want the result set ordered, the only way to do that is to use the order by clause.

The only time the Sybase feature you mention could be practically used is for a large read-only table, since inserting a row in the middle of the table requires shifting all the other rows to maintain the order. Great for ordered queries, absolutely horrible for updates.

Oracle does have a 'cluster' concept but it merely stores the data from multiple tables physically together when they are related which optimizes IO when doing joins. But it does nothing for sorting.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.