I am working on a oracle 11g database. System ATM has many history tables for records related to transactions. The issue we are having is transaction history information records span multiple tables. As an example we have TransactionDetail table that has version column and TransactionQuestions table that has version column of it's own. When historic information needs to be retrieved massive view is used to get this data out of database - it is incredibly slow, and due to size and complexity with versions and many joins we are constantly finding bugs.
The approach we are thinking to take is instead saving data in multiple history tables, for sole purpose to be later joined into massive view later is to save whole system state on each transaction into massive table with lots of columns instead, put index on PK and - problem solved.
This would solve for us performance issue as data would be retrieved without aggregation.
Biggest downside of table approach in my eyes is if there is bug in SQL View structure it can be fixed and actual history data is not being affected, if there is a bug in mechanism (which would be moved from view to code essentially) that writes data to history table - data becomes corrupt and can be impossible to fix.
What other drawbacks does massive history table would bring when compared to view that combines data from multiple history tables?
Have you considered creating a materialized view? Materialized views have significantly better storage characteristics, while still allowing normalized table structure. They have a couple of tradeoffs, including higher disk usage (basically formalizing the "massive history table"). This basically creates your massive history table, and lets oracle do the heavy lifting of serializing changes out to the normalized tables.