Oracle - Big Table Archiving

Question

Version: Oracle 12cR2 EE

The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2

Table size: 95GB

Table row count: 20M

Table partitioned? No

Indexes on table: there is only one primary key as an index on table.

Depdencies: there is only one trigger "before each row (insert)" as a dependency on table.

Constraints: there are 3 constraints (1 of them is primary key and others are check).

We want to archive this table. However, I could not decide how the archiving should be done for such a large table. It would be a nightmare to move records before a certain date into a new table with CTAS and then delete these imported records on the existing table.

I think the best way would be to make the table interval partitioned by month (I'm open to any ideas if you have better solutions).

A few pre-operation and sequence questions:

1. Is there a way to find out beforehand how much archive will generate during the partitioning of a table of this size?
2. Do we have a chance to calculate the duration of the process in advance?
3. Would it be a good choice to stop other database jobs that will load during the process?
4. Is it necessary to make a change on the primary key index for the operation or other dependencies or trigger?

For after the process:

1. Does it cause heavy load on the database when large partitions are dropped?

2. For example, is it possible to set up an order like this; can we keep only the last 3 or last 6 months' data on the main table and move the older partitions to the archive table every month? Is it correct to use only the export import method when migrating?

3. The migrated partition will be dropped from the migration process. Will there be a problem on the basis of archive generate while doing this?

Thank you.

Best Regards.

Answer 1

Interval partitioning should be the propriate method for this. If you can take care that most of the application also takes this into account (meaning: their queries should ALWAYS contain the partition criteria in the WHERE clause), then it will speed up things.

pre-operation

1. not really. I would create a clone database on another (similar) system and test it there - this should answer most of your questions.
2. see 1, look at v$session_longops. Not that this predictions are 100% accurate, but they can give you an idea.
3. If possible, yes. Since 12.2 you can partition a table directly online, so actually you still can use it - but it does not speed up things, naturally
4. That should not be necessary

post operation:

1. without foreign keys and triggers, no. If you turn them off, it wont.
2. With interval partitioning on month level this should not be difficult. The classic method is to move that data to a DWH and access is via DB link if necessary. Don't use export (I hope you mean expdp), see https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html
3. no, if you use alter table drop partition update global indexes.