Advantages of loading sorted data into large oracle tables

It is generally recommended that data be sorted on the primary key before loading into large tables ( partitions). At a minimum the advantage of this would be the ability to create indexes fast ( using the nosort option). Since external sort programs such as syncsort are considerable more effiecient than the sorting algorithms used by Oracle this would lead to a more efficient load process.

 My question is about performance of queries in the database when the data is stored in a physically sorted order. I do understand perfectly well that in oracle ( and in relational theory) you cannot depend on the data being stored in sorted order. You have to issue an order by clause to retrieve data in sorted order. Empirically though I have observed that data in a table (or partition) is returned in the same order that it was loaded in ( as long as the data has not been modified in any way.). The presumably is taken advantage of in the nosort option of the create index command.

  In a application where the data is relatively static ( a large data warehouse) would queries involving sorting ( order by, group by, sort merge joins) by primary key be faster when the data is stored in sorted order rather than random order. Oracle will of course sort the data ( as it cannot rely on the data being sorted) but will this sorting of sorted ( or almost sorted) data be more efficient that sorting unsorted data ?Presumably there could be less I/O because of the effects of caching. During one I/O operation a number of adjacent rows are brought into memory. If these rows are processed while still in memory than the total amount of I/O is reduced. This might help hash joins as well if the join occurs on the sort key. Received on Sun Jun 09 2002 - 14:50:08 CDT