When building large databases, the physical distribution of data in a table can make a significant difference to performance. There have been times when I have found it worth spending the time in an overnight batch run to sort an load a table in order so that the data is packed as efficiently as possible for certain classes of indexed query.
Until recently, I had two methods for doing this - dump the table to flat file, sort in the operating system, then SQL*Load it, or (and I prefer this because it eliminates the risk of losing the data somewhere outside the database in mid-run) creating a holding table, indexing the holding table, then creating the final table with a statement like:
create table final_table
as
select /*+ index (t1,i1) */
*
from
holding_table t1
where
indexed_col1 > {min value};
As you can imagine, this is a somewhat tedious and timewasting thing to do. However I have just discovered that one of the convenient (and I think unannounced) little features of Oracle 7.3.4 has just made my job easier. The syntax:
create table t1 as select * from t2 order by col1, col2,col3;
has just become legal. (Previously it resulted in ORA-00933 statement ended illegally).
Those of you busy with Oracle 8.1 will recognise that this is feature is available, and documented there; but don't you wonder how may other Oracle 8 features (like star transformations, but maybe they're only a beta version and you shouldn't use them) are tucked away in 7.3.4.
More on this feature at a later date.