Best practice guidelines for a application table archive in Oracle 12C

Question

Database environment: Oracle 12C Enterprise edition

• I have a table in the application schema and corresponding history table in the history schema.
• The application table has about 10 small varchar2 columns with a maximum row size of 200 bytes.
• Once a week ( call it a period) an Oracle scheduler job will run a stored procedure to extract the data out of application table and insert into the corresponding history table.
• The history table will have the year and period number columns in addition to the application fields.
• A separate asynchronous process purges the application table after the rows are transferred to the history table (outside the scope)
• The history table will contain a year worth of data from the corresponding application table. New history table will be created each year.
• The history table data is queried primarily by the application id column and other criteria including the year and period

At this time I am not considering a partitioned table design... With the above design goals in mind, I am considering following points in designing my tables and load stored procedures -

1. setting up compression at the table space level for the history schema
2. Direct-path insert with append hint
3. Make indexes unusable before load and usable after loading

My Question: Looking for recommendation on the design I am considering.

Answer 1

You can use Flashback Data Archive to keep track of previous versions of a table. With that feature you can query the table as it looked at any point in time (within your retention period). That does sound like a more flexible approach than the one you are building (re-inventing the wheel).

1 and 2 does sound very straightforward good ideas. 3 depends on the amount of data you are loading. unusable indexes will hurt your application during the load. I would consider putting each load into a separate partition. Then it is very easy to get rid of data after a year - just drop the entire partition. And I assume that you often will look at data at a specific point in time - in which case the optimizer can use partition pruning if you construct the SQL correctly.