There are three options by which instantaneous data refresh across multiple tables can be achieved, they are
- Bulk COMMIT
- Partition Exchange
Earlier had discussed on what is instantaneous data refresh and the option of using Bulk COMMIT, now shall detail on the other two options. The following points are in reference to Oracle, but would be applicable to other databases as well.
‘Partition Exchange’: In this option the database feature to exchange partitions between tables is being leveraged. The steps involved in designing instantaneous data refresh using table partitions are
- Create two similar table structures, one a ‘partition’ table and the other one a ‘non-partition’ table. Both the tables will hold data in it. The ‘partition’ table will be accessed by the users for data analysis and reporting
- Have the data load process built against the ‘non-partition’ table
- On successful load run a script to exchange the ‘non-partition’ table data with the ‘partition’ table. This switch will happen instantaneously without any definite downtime
‘Synonym’: In this option the database feature Synonym is being leveraged, Synonyms decouple the underlying table to the applications that access it. The steps involved in designing instantaneous data refresh through synonym definition are
- Create two ‘data’ schemas and have same table structures with same table names in both the schemas. Both the schemas will hold the data.
- Create a ‘synonym’ schema and have Synonyms pointing to one of the ‘data’ schemas. The Synonym schema will be accessed by the users and other applications for data analysis and reporting. The Synonym schema will have only the definition and will not hold the data.
- Always the ETL process will run against the schema that is not being used for reporting, hence the ETL process will need to be designed to run against the two ‘data’ schemas alternatively
- After successful load, run a script to re-point the Synonym to the recently loaded ‘data’ schema. This switch will happen instantaneously without any definite downtime
Next time, shall compare these three options and determine the best option….