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.

1 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.

  • What is the impact of enabling row movement ?
    – RKA
    Commented Mar 22, 2020 at 16:53
  • That a row might change rowid (when it is moved due to a table maintenance operation). For regular heap tables this should not be a problem. Only if your application are using the rowid pseudo-column of a row. Commented Mar 23, 2020 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.