Re: How to insert data using index unusable/rebuild

aoshell2_at_yahoo.com wrote in message news:<1e271843.0307101206.725d4100_at_posting.google.com>...
> I am trying to rewrite a generic data load procedure for large tables
> to use the INDEX REBUILD statements instead of dropping and recreating
> the indexes. Trying to get rid of hard-coding the CREATE INDEX
> statements for less maintenance.
>
> We do have some PK and UNIQUE indexes on the tables that need to be
> loaded. I figured out how to get around the PK indexes by disabling
> the PK constraints prior to doing the INDEX REBUILD, but don't see how
> to get around or disable the UNQUE INDEX being UNUSABLE when I need to
> insert the data. There is no contraint on the table, just the unique
> index.
>
> Here is my basic processing for loading the tables.
>
> 1. truncate destination table.
> 2. Change status of indexes to UNUSABLE.
> 3. Generate index rebuild statements prior to disabling primary key
> constraints because after that the index info is not available.
> 4. SET SESSION SKIP_UNUSABLE_INDEXES=TRUE so that unusable indexes
> don't cause failure when inserting the data.
> 5. Disble any primary keys on the table because you can't do an index
> rebuild with an active PK constraint.
> 6. Insert data from source table (copying table data).
> 7. Enable any primary keys on the table that were disbled earlier.
> 8. Rebuild indexes.
> 9. Set SKIP_UNUSABLE_INDEXES=FALSE
>
> Any Help greatly appreciated.

You are aware of the fact that SKIP_UNUSABLE_INDEXES does not work for unique index, right? If your goal is to avoid hard coding the index names in your process script, there is really no need to use SKIP_UNUSABLE_INDEXES. If the goal is to avoid index maintainence during DML, you are out of luck with UNIQUE indexes. You can use SKIP_UNUSABLE_INDEXES to work with the primary key - since primary keys do not have to be unique. But not unique indexes.

Your table is not partitioned, yes? You might have better luck with partitioned tables. They have more options for you to play around with.

• Jusung Yang