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:
- create a bkp table
- truncate original table
- 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)
- reinsert data in table
- rebuild indexes
Do you have other solution that can be faster, maybe doing this operation on main table without locking it?