Re: Automatic SQL*Loader control file generation

Hi,

I don't think there is an generic utility doing tables reorganisation. There is another 3rd party tool : Platinum's 'TSReorg' does something like your first method, but it dosen't download data by SQL*Plus (this is very long according to my tests).

I think your first method is faster because it dosen't run in rollback segment.

Yang

Greg Coman wrote in message <38ECA8F3.2953ABBF_at_pgrad.unimelb.edu.au>...
>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 Thu Apr 06 2000 - 00:00:00 CDT