Oracle Partition - Data Load

Data Loading with partitions.

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.

See Oracle Partition - Partition Exchange (between tables)

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.

• Difference between dropping rebuilding indexes and using update global indexes