Re: Can we optimize an ORDER BY clause?

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

> Oracle does not have a clustered index but it has better I think.
> Simply declare a cluster table with only one table and the cluster key
> being the column you want to order by your table. Then create an index
> on it as usual. This should answer your need.

This response is dangerously misleading. Oracle uses the term 'cluster' and 'clustered index' differently from Sybase. In the Sybase (and SQL Server) world, this refers to physical row ordering. That is, when a row is inserted into a clustered index, the entire table may have to be sorted and rewritten to maintain the physical row order. Very fast for retrieval when the appropriate 'order by' clause is issued, but hideous for random inserts.

Oracle uses the clustering concept to maintain the same physical, contiguous location of blocks of two tables with a common key, like a parent-child table relationship. The actual rows and cluster keys are not physically ordered, they are written sequentially (appended) just like regular row. They are accessed by a separate physical index structure, just like a regular table. Sybase clusters have the index and table physically written in the same structure.

The advantage that Oracle clusters offer is that the parent and child can be grabbed together in fewer disk accesses. When you are getting both together *frequently*, as in an order processing application, this can reduce i/o and improve performance dramatically. There is overhead associated with having to write both table's rows at the same time, but the table's rows do not have to be sorted at each insert, a la Sybase and SQL Server.

The idea presented here, to create a clustered with only one table is pointless. You end up with the same sort of structure as a regular table, with an index and everything, but have to preallocate more space. Plus, you end up with index values for null key columns, unlike normal indices where null values do not get indexed.

Sorry, no easy trick here. But, please don't misuse clusters. The price could be high.

Roger Received on Mon Jan 20 1997 - 00:00:00 CST