/* 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;
ALTER SESSION SET query_rewrite_integrity=enforced;

/* Create the Objects for our example */

DROP TABLE peoples;

CREATE TABLE peoples (peep_id NUMBER(10),bev_id NUMBER(10), peep_name VARCHAR2(100));

ALTER TABLE peoples ADD CONSTRAINT peep_pk PRIMARY KEY (peep_id) USING INDEX;

DROP TABLE beverages;

CREATE TABLE beverages (bev_id NUMBER(10), bev_description VARCHAR2(255));

ALTER TABLE beverages ADD CONSTRAINT bev_pk PRIMARY KEY (bev_id) USING INDEX;

ALTER TABLE peoples ADD CONSTRAINT peep_bev_fk FOREIGN KEY (bev_id) REFERENCES beverages(bev_id);

INSERT INTO beverages
     VALUES (1, 'Ruby Micro');
INSERT INTO beverages
     VALUES (2, 'Jack Daniels');
INSERT INTO beverages
     VALUES (3, 'Crunk Juice');

INSERT INTO peoples
     VALUES (1, 1, 'Ja Kizzle');
INSERT INTO peoples
     VALUES (2, 2, 'Ja Kizzle');
INSERT INTO peoples
     VALUES (3, 3, 'Ja Kizzle');

COMMIT ;

ANALYZE TABLE peoples ESTIMATE STATISTICS;

ANALYZE TABLE beverages ESTIMATE STATISTICS;

DROP MATERIALIZED VIEW LOG ON peoples;

CREATE MATERIALIZED VIEW LOG ON peoples WITH SEQUENCE, ROWID (peep_id, peep_name);

ALTER MATERIALIZED VIEW LOG ON peoples ADD PRIMARY KEY;

DROP MATERIALIZED VIEW LOG ON beverages;

CREATE MATERIALIZED VIEW LOG ON beverages WITH SEQUENCE, ROWID ( bev_id, bev_description);

ALTER MATERIALIZED VIEW LOG ON beverages ADD PRIMARY KEY;

DROP MATERIALIZED VIEW who_is_crunk_mv;

CREATE MATERIALIZED VIEW  who_is_crunk_mv  BUILD IMMEDIATE REFRESH FAST ON COMMIT  ENABLE QUERY REWRITE  AS
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;

/* Test 1 ..see how oracle rewrites a query to use the pre-aggregated data */

TRUNCATE TABLE plan_table;

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;

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;

SELECT  p.peep_name,
                   b.bev_description
FROM peoples p,
          beverages b
WHERE p.bev_id = b.bev_id;

/* Test 2 ..notice how on commit keeps materialized view current therefore, re-write happens regardless!  */

TRUNCATE TABLE plan_table;

INSERT INTO peoples
     VALUES (4, 1, 'Master James');
INSERT INTO peoples
     VALUES (5, 2, 'Master James');
INSERT INTO peoples
     VALUES (6, 3, 'Master James');
COMMIT ;

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;

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;

SELECT  p.peep_name,
                   b.bev_description
FROM peoples p,
          beverages b
WHERE p.bev_id = b.bev_id;

/* 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;

ALTER MATERIALIZED VIEW who_is_crunk_mv REFRESH FAST ON DEMAND;

INSERT INTO peoples
     VALUES (7, 1, 'Yo Mama');
INSERT INTO peoples
     VALUES (8, 2, 'Yo Mama');
INSERT INTO peoples
     VALUES (9, 3, 'Yo Mama');
COMMIT ;

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;

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;

SELECT  p.peep_name,
                   b.bev_description
FROM peoples p,
          beverages b
WHERE p.bev_id = b.bev_id;