Background:
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.
Problem:
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.
Solution:
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:
Any experiences? (both good and bad are welcome for discussion)
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.
Problem:
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.
Solution:
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)
No comments:
Post a Comment