Oracle Partition - Data Load

Direct Insert

Taking advantage of partitioning for fact tables reduces index and statistics maintenance time during ETL processes.

Because the majority of inserts and updates impact the last partition(s), you only need to:

1. disable local indexes on a few impacted partitions,
2. and then rebuild disabled indexes after the load
3. and then compute statistics on updated partitions only.

Partition Exchange

See Oracle Partition - Partition Exchange (between tables)

Index disabling/Re-enabling

The below procedure

• disabling an index,
• doing a large bulk load,
• re-enabling and rebuild the index

can be faster than “leaving the index in place during the load”

This is true for single partition tables (normal tables) as well as partitioned tables.

However, during the load, the indexes are “not there (not usable)” for query.