Automatic SQL*Loader control file generation

From: Greg Coman <g.coman_at_pgrad.unimelb.edu.au>
Date: 2000/04/07
Message-ID: <38ECA8F3.2953ABBF@pgrad.unimelb.edu.au>#1/1

Hi,
I'd like to do a true reorganisation of one of my tables, that is, I'd like to put the data back in the table sorted by a specific key, and I'd like my indexes to be optimally organised. This is for performance reasons. To do the reorganisation I'm planning to do the following:

1) Unload the table using SQL.
2) Sort the unloaded data using the Unix 'sort' command.
3) Drop the indexes on the table.
4) Load the table using SQL*Loader.
5) Rebuild the indexes using 'create index' statements.

Does anyone know of a generic program / utility that can unload a table and also generate an SQL*Loader control file for the unloaded data? (If you're familiar with DB2, I'm looking for a program similar to the DB2 DSNTIAUL program.)

I know that I could also do the reorganisation by: 1) inserting into a cloned table selecting from my table ordering on my desired key;

2) dropping my table,
3) renaming the cloned table to the name of my original table,
4) building the indexes on the table.

However, I believe that the first method mentioned above will be faster.

I also believe that full reorganisations of this type can also be done with third party products like BMC's REORG Plus. However, I'd like to see what type of performance gains I can get first before looking at 3rd party products.

Regards,
Mark. Received on Fri Apr 07 2000 - 00:00:00 CDT