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.
Best Answer
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.