/* Formatted on 2005/07/05 15:40 (Formatter Plus v4.8.5) */ /* Set at Instance and/or session level for example */ ALTER SESSION SET query_rewrite_enabled=TRUE Session altered ALTER SESSION SET query_rewrite_integrity=enforced Session altered /* Create the Objects for our example */ DROP TABLE peoples Table dropped CREATE TABLE peoples (peep_id NUMBER(10),bev_id NUMBER(10), peep_name VARCHAR2(1 Table created ALTER TABLE peoples ADD CONSTRAINT peep_pk PRIMARY KEY (peep_id) USING INDEX Table altered DROP TABLE beverages Table dropped CREATE TABLE beverages (bev_id NUMBER(10), bev_description VARCHAR2(255)) Table created ALTER TABLE beverages ADD CONSTRAINT bev_pk PRIMARY KEY (bev_id) USING INDEX Table altered ALTER TABLE peoples ADD CONSTRAINT peep_bev_fk FOREIGN KEY (bev_id) REFERENCES b Table altered INSERT INTO beverages VALUES (1, 'Ruby Micro') 1 row inserted INSERT INTO beverages VALUES (2, 'Jack Daniels') 1 row inserted INSERT INTO beverages VALUES (3, 'Crunk Juice') 1 row inserted INSERT INTO peoples VALUES (1, 1, 'Ja Kizzle') 1 row inserted INSERT INTO peoples VALUES (2, 2, 'Ja Kizzle') 1 row inserted INSERT INTO peoples VALUES (3, 3, 'Ja Kizzle') 1 row inserted COMMIT Commit complete ANALYZE TABLE peoples ESTIMATE STATISTICS Table analyzed ANALYZE TABLE beverages ESTIMATE STATISTICS Table analyzed DROP MATERIALIZED VIEW LOG ON peoples ORA-12002: there is no materialized view log on table "SYSTEM"."PEOPLES" CREATE MATERIALIZED VIEW LOG ON peoples WITH SEQUENCE, ROWID (peep_id, peep_name Snapshot log created ALTER MATERIALIZED VIEW LOG ON peoples ADD PRIMARY KEY Snapshot log altered DROP MATERIALIZED VIEW LOG ON beverages ORA-12002: there is no materialized view log on table "SYSTEM"."BEVERAGES" CREATE MATERIALIZED VIEW LOG ON beverages WITH SEQUENCE, ROWID ( bev_id, bev_des Snapshot log created ALTER MATERIALIZED VIEW LOG ON beverages ADD PRIMARY KEY Snapshot log altered DROP MATERIALIZED VIEW who_is_crunk_mv Snapshot dropped CREATE MATERIALIZED VIEW who_is_crunk_mv BUILD IMMEDIATE REFRESH FAST ON COMMI SELECT p.ROWID AS "peep_row", p.peep_id, p.peep_name, b.ROWID AS "bev_row", b.bev_description FROM peoples p, beverages b WHERE p.bev_id = b.bev_id Snapshot created /* Test 1 ..see how oracle rewrites a query to use the pre-aggregated data */ TRUNCATE TABLE plan_table Table truncated EXPLAIN PLAN SET STATEMENT_ID='REWRITE_TEST1' FOR SELECT p.peep_name, b.bev_description FROM peoples p, beverages b WHERE p.bev_id = b.bev_id Explained SELECT LPAD (' ', 2 * (LEVEL - 1)) || operation || ' ' || options || ' ' || object_name || ' ' || DECODE (object_node, '', '', '[' || object_node || '] ') QUERY, other, COST FROM plan_table WHERE STATEMENT_ID = 'REWRITE_TEST1' START WITH ID = 0 CONNECT BY PRIOR ID = parent_id QUERY -------------------------------------------------------------------------------- SELECT STATEMENT MAT_VIEW REWRITE ACCESS FULL WHO_IS_CRUNK_MV 2 rows selected SELECT p.peep_name, b.bev_description FROM peoples p, beverages b WHERE p.bev_id = b.bev_id PEEP_NAME -------------------------------------------------------------------------------- Ja Kizzle Ja Kizzle Ja Kizzle 3 rows selected /* Test 2 ..notice how on commit keeps materialized view current therefore, re-w TRUNCATE TABLE plan_table Table truncated INSERT INTO peoples VALUES (4, 1, 'Master James') 1 row inserted INSERT INTO peoples VALUES (5, 2, 'Master James') 1 row inserted INSERT INTO peoples VALUES (6, 3, 'Master James') 1 row inserted COMMIT Commit complete EXPLAIN PLAN SET STATEMENT_ID='REWRITE_TEST2' FOR SELECT p.peep_name, b.bev_description FROM peoples p, beverages b WHERE p.bev_id = b.bev_id Explained SELECT LPAD (' ', 2 * (LEVEL - 1)) || operation || ' ' || options || ' ' || object_name || ' ' || DECODE (object_node, '', '', '[' || object_node || '] ') QUERY, other, COST FROM plan_table WHERE STATEMENT_ID = 'REWRITE_TEST2' START WITH ID = 0 CONNECT BY PRIOR ID = parent_id QUERY -------------------------------------------------------------------------------- SELECT STATEMENT MAT_VIEW REWRITE ACCESS FULL WHO_IS_CRUNK_MV 2 rows selected SELECT p.peep_name, b.bev_description FROM peoples p, beverages b WHERE p.bev_id = b.bev_id PEEP_NAME -------------------------------------------------------------------------------- Ja Kizzle Ja Kizzle Ja Kizzle Master James Master James Master James 6 rows selected /* Test 3 ..What would happen if we added data and the Materialized view was not fast refreshable on commit - a.k.a. what if the materialized view is stale */ TRUNCATE TABLE plan_table Table truncated ALTER MATERIALIZED VIEW who_is_crunk_mv REFRESH FAST ON DEMAND Snapshot altered INSERT INTO peoples VALUES (7, 1, 'Yo Mama') 1 row inserted INSERT INTO peoples VALUES (8, 2, 'Yo Mama') 1 row inserted INSERT INTO peoples VALUES (9, 3, 'Yo Mama') 1 row inserted COMMIT Commit complete EXPLAIN PLAN SET STATEMENT_ID='REWRITE_TEST3' FOR SELECT p.peep_name, b.bev_description FROM peoples p, beverages b WHERE p.bev_id = b.bev_id Explained SELECT LPAD (' ', 2 * (LEVEL - 1)) || operation || ' ' || options || ' ' || object_name || ' ' || DECODE (object_node, '', '', '[' || object_node || '] ') QUERY, other, COST FROM plan_table WHERE STATEMENT_ID = 'REWRITE_TEST3' START WITH ID = 0 CONNECT BY PRIOR ID = parent_id QUERY -------------------------------------------------------------------------------- SELECT STATEMENT HASH JOIN TABLE ACCESS FULL PEOPLES TABLE ACCESS FULL BEVERAGES 4 rows selected SELECT p.peep_name, b.bev_description FROM peoples p, beverages b WHERE p.bev_id = b.bev_id PEEP_NAME -------------------------------------------------------------------------------- Yo Mama Master James Ja Kizzle Yo Mama Master James Ja Kizzle Yo Mama Master James Ja Kizzle 9 rows selected