Re: howto disable index build before full load?

In article <38F89647.1218CC90_at_online.de>,   bmlam <bmlam_at_online.de> wrote:
> Here is the challenge:
>
> To speed up performance in loading data for a data warehouse
> application, we would like to the following in a PL/SQL
> package/procedure
>
> 1. truncate the table
> 2. "disable the build of indexes on the table"
> 3. insert load into the table
> 4. "enable the indexes"
>
> Step 1 4 are straightforward. Step 2 and 4 are more convoluted because
> the methods known to me would require to do the following with dynamic
> SQL
>
> 2. drop all the indexes
> 4. create the indexes with all the required parameters.
>
> For my taste, step 4 is too error prone bcos it requires putting too
> many logic into dynamic SQL statements that may go wrong during run
> time. So I am looking for a way that is more bulletproof. Any ideas?
>

The only way to disable the indexes during the load is to drop them. This means you will need to rebuild them so you do not have a lot of choice. Logically, if you rebuild the indexes using parameter values obtained from the dictionary before the drop of the indexes there should be little chance of an error occurring during the index rebuilds. The most likely error is space related but if you use the same initial and next parameters as before then space should not be an issue unless you table increases in size.

Once de-bugged your routine should work. You could add logic to the job to verify that all indexes exist and to notify the Operator/DBA if any of the indexes are missing.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.