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 -
- setting up compression at the table space level for the history schema
- Direct-path insert with append hint
- Make indexes unusable before load and usable after loading
My Question: Looking for recommendation on the design I am considering.