CODEWITHSUNDEEP
oracle-databaseoracle10gpartitioningindexing
PatternMatching
PatternMatching

Reputation: 466

Can't drop Oracle index partition -- any alternative besides dropping entire index and rebuilding?

So, I have a .NET program doing batch loading of records into partitioned tables using array bound stored procedure calls via Oracle ODP.NET, but that's neither here nor there.

What I would like to know is: because I have a partitioned index on said tables, the speed of the batch load is pretty slow. I fully understand that I cannot drop an index partition, but I would obviously prefer not to have to drop and rebuild the entire index since that will take considerably more time to execute. Is this my only recourse?

Is there a fairly simple way to drop the partition itself and then rebuild the partition and index partition that would save time and go about accomplishing my goal?

Upvotes: 1

Views: 1554

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Are you loading an entire partition at once? Or are you merely adding new rows to an existing partition? Are all the indexes equipartitioned with the table?

Normally, if you are loading data into a partitioned table, your partitioning scheme is chosen so that each load will put data into a fresh partition. If that is the case, you can use partition exchange to load the data. In a nutshell, you load data into an (unindexed) staging table whose structure matches the real table, you create the indexes to match the indexes on the real table, and then do

ALTER TABLE partitioned_table
  EXCHANGE PARTITION new_partition_name
  WITH TABLE staging_table_name
  WITHOUT VALIDATION;

Upvotes: 1

Related Questions