0

We are making use of Oracle 12c RANGE INTERVAL partitioning where Oracle creates the partitions automatically based on the data.

Parent tables are partitioned based on RANGE INTERVAL, child tables are based on REFERENCE partition.

We have about 33 million rows to be loaded in about 30 tables.

Need advice on when to create LOCAL and GLOBAL indexes? Is it okay to create local, global indexes (and constraints) after the data is loaded? Is there a downside in this approach? Appreciate tips and best practices followed as this is very common scenario.

AJORA
  • 27
  • 5

1 Answers1

0

To begin with, there are no local or global constraints, constraints are always on the whole table, not a partition. Indexes can be local or global (partitioned or not). If you're sure that your data is consistent with regards to constraints (PK/uniqueness and FK), then you better create the indexes/constraints afterwards, makes everything faster. You can even create the constraints 'enable novalidate' to go faster.

gsalem
  • 1,957
  • 1
  • 8
  • 7