JL Computer Consultancy

Packing a table

April 1999


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.

Back to Main Index of Topics