Two weeks back, I wrote a post on result_cache feature of Oracle 11g database to solve a specific performance scenario in MDM implementation. Working on the same set of performance issues, we have encountered another situation where we have a normalized structure which results in writing queries to use OUTER JOINS to achieve the required aggregation.
The structure contains a set of tables for PERSON and another set of tables to represent ORGANIZATION when a CUSTOMER can be a PERSON or an ORGANIZATION.
The requirement is to get a consolidated view of all persons and organizations together with certain attributes. We need to perform a UNION ALL query joining a total of 8 tables that is going to result in something like 10Million records. We will not be able to result_cache this result in memory.
Inevitably we need to create a persistent version of the result of the UNION ALL query in a materialized view. But customer needs real-time data and can’t afford any latency. So, we need a view that gets updated whenever underlying tables change. That is where the “REFRESH FAST ON COMMIT” comes into the picture.
To be able to do fast refresh MATERIALIZED VIEW LOG to be created on all the underlying tables. We have selected “rowid”. All the 8 underlying tables need to have the MV LOGS created before creating a MV as follows:
CREATE MATERIALIZED VIEW MV_PN_ORG REFRESH FAST WITH rowid ON COMMIT AS SELECT < REQUIRED COLUMNS> p.rowid AS prowid, xp.rowid AS xprowid, xpn.rowid AS xpnrowid, pn.rowid AS pnrowid FROM person p, xperson xp, xpersonname xpn, personname pn WHERE p.PID = xp.XPid AND XPN.XPID = PN.PNID AND pn.CId = p.CId AND xpn.preferred_ind ='Y' UNION ALL SELECT <REQUIRED COLUMNS>, o.rowid AS orowid, xo.rowid AS xorowid, xon.rowid AS xonrowid , orgn.rowid AS orgnrowid FROM org o, xorg xo, xorgname xon, orgname orgn WHERE o.cid = xo.xoid AND xon.xON_id =orgn.ONid AND orgn.cId = o.Cid AND xon.preferred_ind ='Y';
This MV now has de-normalized data which can be used in the higher level queries for looking up requird data without costly joins. We can also create INDEXes on the MV to improve lookup.
Any experiences? (both good and bad are welcome for discussion)