Oracle: partition a table with more than 1B records

162 Views Asked by At

Database: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 Issue: in production we have a table with more than 1B records that we are not able to query since the table is not partitioned/indexed. To tune the table we want to partition it, but we have problems due to a high data volume since operation requires a lot CPU and cannot be done in one day. Steps we want to follow are:

  1. create a bkp table
  2. truncate original table
  3. add partitions, sub-partitions and indexes on original table (partition will be done on date column - daily data, sub-partition will be done on type)
  4. reinsert data in table
  5. rebuild indexes

Do you have other solution that can be faster, maybe doing this operation on main table without locking it?

0

There are 0 best solutions below