Mimicking a Materialized View in Oracle

I had a quite complex View in an Oracle database that performed quite badly and I needed to improve performance. I tried making a Materialized View, but didn’t like the restrictions, so I decided to mimic the behavior using a table.

Materialized Views

I decided to look at Materialized Views, but soon realized that there were a lot of restrictions for the SQL that can be used to generate a Materialized View. A Materialized View is great for replication of data and simple queries, but for complex queries there were too many restrictions – so I decided to mimic the functionality of a Materialized View…and it turned out to be quite simple. I know it is not the best solution since it is vulnerable to coding errors, but besides that, I think it is quite elegant :-)

I had six tables (Table_A, Table_B….Table_F) and one view to create a summary – let us call it “View_ABCDEF”.

Improve Performance

To improve performance, I did the following:

I create a new table named “Mimick_ABCDEF” with the same structure as View_ABCDEF and then changed by code to search in Mimick_ABCDEF instead of View_ABCDEF.

I then updated all locations that performs UPDATE, INSERT or DELETE on Table_A through Table_F to run the following two queries after the original query:

DELETE FROM Mimick_ABCDEF WHERE (col1, col2, .... coln) IN 
(SELECT * FROM Mimick_ABCDEF MINUS SELECT * FROM View_ABCDEF);

This query removes all rows present in the table but not in the view (happens on update and delete). This query does not work when some on the values are null, but should be quite simple to fix.

INSERT INTO Mimick_ABCDEF (SELECT * FROM View_ABCDEF MINUS SELECT * FROM Mimick_ABCDEF);

This query inserts all rows in the view, but not in the table, into the table.

Quite simple :-)

 

Leave a Reply

Your email address will not be published. Required fields are marked *