/*
|| Oracle 10gR2 Materialized View Enhancements Listing 1
||
|| Demonstrates new Oracle 10gR2 Materialized View features, including:
|| - Examples of fast-refreshable materialized join views (MJVs)
|| - Using the new REWRITE_OR_ERROR optimizer hint
|| - Using DBMS_MVIEW.EXPLAIN_REWRITE to find out why a query rewrite
||   operation failed
|| - Using DBMS_MVIEW.EXPLAIN_MVIEW to determine a materialized view's
||   query rewrite capabilities
|| - Using DBMS_MVIEW.TUNE_MVIEW to tune a materialized view proactively
|| - Using trusted constraints in materialized views
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g
|| materialized view enhancements, and it should be carefully proofread before
|| executing it against any existing Oracle database to insure that no
|| potential damage can occur.
||
*/

/* 
|| Listing 1.1: Create sample materialized view logs and a materialized view
||              for demonstration purposes
*/

-----
-- Create materialized view logs on HR.DEPARTMENTS, HR.EMPLOYEES, and 
-- HR.LOCATIONS to support fast refresh for materialized views
-----
DROP MATERIALIZED VIEW LOG ON hr.departments;
CREATE MATERIALIZED VIEW LOG ON hr.departments
    WITH SEQUENCE, ROWID (
         department_id 
        ,department_name
        ,manager_id
        ,location_id
    )
    INCLUDING NEW VALUES;
    
DROP MATERIALIZED VIEW LOG ON hr.employees;
CREATE MATERIALIZED VIEW LOG ON hr.employees
    WITH SEQUENCE, ROWID (
         employee_id 
        ,first_name 
        ,last_name 
        ,email 
        ,phone_number 
        ,hire_date 
        ,job_id 
        ,salary 
        ,commission_pct 
        ,manager_id 
        ,department_id
    )
    INCLUDING NEW VALUES
;

DROP MATERIALIZED VIEW LOG ON hr.locations;
CREATE MATERIALIZED VIEW LOG ON hr.locations
    WITH SEQUENCE, ROWID (
         location_id 
        ,street_address 
        ,postal_code 
        ,city 
        ,state_province 
        ,country_id
    )
    INCLUDING NEW VALUES
;

-----
-- Create a join-only materialized view on joined result set
-- from HR.EMPLOYEES, HR.DEPARTMENTS, and HR.LOCATIONS.
-- Note that:
-- 1. It will be built immediately.
-- 2. It will be refreshed if any table is changed because each 
--    table has a materialized log view.
-- 3. QUERY REWRITE is enabled.
-----
DROP MATERIALIZED VIEW hr.mjv_loc_dept_emps;
CREATE MATERIALIZED VIEW hr.mjv_loc_dept_emps
    PARALLEL 
    BUILD IMMEDIATE
    REFRESH FAST
    ENABLE QUERY REWRITE
AS
    SELECT
         L.rowid loc_rid
        ,D.rowid dpt_rid
        ,E.rowid emp_rid
        ,L.city
        ,E.department_id
        ,D.department_name
        ,E.employee_id
        ,E.first_name 
        ,E.last_name 
        ,E.hire_date
        ,E.salary
      FROM 
         hr.locations L
        ,hr.departments D
        ,hr.employees E
     WHERE E.department_id = D.department_id(+) 
       AND D.location_id = L.location_id(+)
;

/* 
|| Listing 1.2: Analyzing MVs for efficiency using Oracle 10g's new
||              EXPLAIN PLAN features
*/

EXPLAIN PLAN
    SET STATEMENT_ID = 'MJV.1.0'
    FOR
SELECT /*+REWRITE_OR_ERROR */
     DEPT.department_name
    ,EMP.employee_id
    ,EMP.last_name || ', ' || EMP.first_name 
    ,TO_CHAR(EMP.hire_date, 'mm-dd-yyyy')
    ,EMP.salary
    ,LOC.city
  FROM 
     hr.locations LOC
    ,hr.departments DEPT
    ,hr.employees EMP
 WHERE EMP.department_id = DEPT.department_id(+) 
   AND DEPT.location_id = LOC.location_id(+)
;

EXPLAIN PLAN
    SET STATEMENT_ID = 'MJV.1.1'
    FOR
SELECT /*+REWRITE_OR_ERROR */
     DEPT.department_name
    ,EMP.employee_id
    ,EMP.last_name || ', ' || EMP.first_name 
    ,TO_CHAR(EMP.hire_date, 'mm-dd-yyyy')
    ,EMP.salary
    ,LOC.city
    ,EMP.commission_pct
  FROM 
     hr.locations LOC
    ,hr.departments DEPT
    ,hr.employees EMP
 WHERE EMP.department_id = DEPT.department_id(+) 
   AND DEPT.location_id = LOC.location_id(+)
;

SET ECHO OFF
SET PAGESIZE 0
SET LINESIZE 110
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','MJV.1.0','TYPICAL'));
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','MJV.1.1','TYPICAL'));

>>> Resulting output:

Plan hash value: 2370561235
--------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Co
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |   107 |  5671 |
|   1 |  PX COORDINATOR                 |                   |       |       |
|   2 |   PX SEND QC (RANDOM)           | :TQ10000          |   107 |  5671 |
|   3 |    PX BLOCK ITERATOR            |                   |   107 |  5671 |
|   4 |    MAT_VIEW REWRITE ACCESS FULL | MVJ_LOC_DEPT_EMPS |   107 |  5671 |
--------------------------------------------------------------------------------

11 rows selected
Plan hash value: 2841907003
--------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | C
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |   107 |  6313 |
|   1 |  PX COORDINATOR                  |                   |       |       |
|   2 |   PX SEND QC (RANDOM)            | :TQ10000          |   107 |  6313 |
|   3 |    NESTED LOOPS                  |                   |   107 |  6313 |
|   4 |     PX BLOCK ITERATOR            |                   |       |       |
|   5 |      MAT_VIEW REWRITE ACCESS FULL| MVJ_LOC_DEPT_EMPS |   107 |  2782 |
|   6 |     TABLE ACCESS BY INDEX ROWID  | EMPLOYEES         |     1 |    33 |
|*  7 |      INDEX UNIQUE SCAN           | EMP_EMP_ID_PK     |     1 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("EMP"."EMPLOYEE_ID"="MVJ_LOC_DEPT_EMPS"."EMPLOYEE_ID")

19 rows selected

-----
-- Example of REWRITE_OR_ERROR optimizer hint
-----

SQL> SELECT /*+REWRITE_OR_ERROR*/
  2       city
  3      ,state_province
  4      ,EMP.department_id
  5      ,DPT.department_name
  6      ,EMP.last_name || ', ' || EMP.first_name full_name
  7      ,EMP.hire_date
  8      ,EMP.salary
  9    FROM
 10       hr.locations LOC
 11      ,hr.departments DPT
 12      ,hr.employees EMP
 13   WHERE EMP.department_id = DPT.department_id(+)
 14     AND DPT.location_id = LOC.location_id(+)
 15     AND EMP.department_id = 40;
     hr.locations LOC
        *
ERROR at line 10:
ORA-30393: a query block in the statement did not rewrite

/* 
|| Listing 1.3: Analyze materialized views for efficiency under Oracle 10g
*/

-----
-- Create the REWRITE_TABLE analysis table
-----
SQL> @?/rdbms/admin/utlxrw.sql;

-----
-- Run DBMS_MVIEW.EXPLAIN_REWRITE for sample queries that attempt to 
-- access the MJV and find out why QUERY REWRITE failed for the queries
-----
DECLARE
    qtxt1 VARCHAR2(4000) := 
        'SELECT DEPT.department_name, EMP.employee_id, EMP.last_name, ' ||
        'EMP.first_name, EMP.hire_date, EMP.salary, LOC.city, ' || 
        'EMP.commission_pct ' || 
        'FROM hr.locations LOC, hr.departments DEPT, hr.employees EMP ' ||
        'WHERE EMP.department_id = DEPT.department_id(+) ' || 
        'AND DEPT.location_id = LOC.location_id(+)' 
    ;

    qtxt2 VARCHAR2(4000) := 
        'SELECT L.city, E.department_id, D.department_name, E.employee_id, '||  
        'E.first_name, E.last_name, E.hire_date, E.salary ' || 
        'FROM hr.locations L, hr.departments D, hr.employees E ' || 
        'WHERE E.department_id = D.department_id(+) ' || 
        'AND D.location_id = L.location_id(+) AND E.department_id = 40' 
    ;

    qtxt3 VARCHAR2(4000) := 
        'SELECT L.city, E.department_id, D.department_name, E.employee_id, ' || 
        'E.first_name, E.last_name, E.hire_date, E.salary ' ||
        'FROM hr.locations L, hr.departments D, hr.employees E ' || 
        'WHERE E.department_id = D.department_id(+) AND D.location_id = L.location_id(+)'
    ;

BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE REWRITE_TABLE';
    DBMS_MVIEW.EXPLAIN_REWRITE(
         query => qtxt1
        ,mv => 'HR.MJV_LOC_DEPT_EMPS'
        ,statement_id => 'expwrt1'
    );
    DBMS_MVIEW.EXPLAIN_REWRITE(
         query => qtxt2
        ,mv => 'HR.MJV_LOC_DEPT_EMPS'
        ,statement_id => 'expwrt2'
    );
    DBMS_MVIEW.EXPLAIN_REWRITE(
         query => qtxt3
        ,mv => 'HR.MJV_LOC_DEPT_EMPS'
        ,statement_id => 'expwrt3'
    );
    COMMIT;
END;
/

-- Queries against the REWRITE_TABLE table
TTITLE 'Current Contents of REWRITE_TABLE'
COL mv_owner            FORMAT A06  HEADING 'MV|Owner'
COL mv_name             FORMAT A18  HEADING 'MV Name'
COL message             FORMAT A20  HEADING 'Reason for Failure' WRAP
COL query               FORMAT A32  HEADING 'Original Query' WRAP
COL rewritten_txt       FORMAT A32  HEADING 'Rewritten Text' WRAP
SELECT
     mv_owner
    ,mv_name
    ,message
    ,query
    ,rewritten_txt
  FROM rewrite_table
;
TTITLE OFF 

>>> Resulting output:

Sun Aug 20               Current Contents of REWRITE_TABLE               Page 1

               
MV
Owner  MV Name            Reason for Failure   Original Query
------ ------------------ -------------------- --------------------------------
Rewritten Text
--------------------------------
HR     MJV_LOC_DEPT_EMPS  QSM-01150: query did SELECT DEPT.department_name, EMP
                           not rewrite         .employee_id, EMP.last_name, EMP
                                               .first_name, EMP.hire_date, EMP.
                                               salary, LOC.city, EMP.commission
                                               _pct FROM hr.locations LOC, hr.d
                                               epartments DEPT, hr.employees EM
                                               P WHERE EMP.department_id = DEPT
                                               .department_id(+) AND DEPT.locat
                                               ion_id = LOC.location_id(+)
SELECT DEPT.department_name, EMP
.employee_id, EMP.last_name, EMP
.first_name, EMP.hire_date, EMP.
salary, LOC.city, EMP.commission
_pct FROM hr.locations LOC, hr.d
epartments DEPT, hr.employees EM
P WHERE EMP.department_id = DEPT
.department_id(+) AND DEPT.locat
ion_id = LOC.location_id(+)

HR     MJV_LOC_DEPT_EMPS  QSM-01150: query did SELECT L.city, E.department_id,
                           not rewrite         D.department_name, E.employee_id
                                               , E.first_name, E.last_name, E.h
                                               ire_date, E.salary FROM hr.locat
                                               ions L, hr.departments D, hr.emp
                                               loyees E WHERE E.department_id =
                                                D.department_id(+) AND D.locati
                                               on_id = L.location_id(+) AND E.d
                                               epartment_id = 40
SELECT L.city, E.department_id,
D.department_name, E.employee_id
, E.first_name, E.last_name, E.h
ire_date, E.salary FROM hr.locat
ions L, hr.departments D, hr.emp
loyees E WHERE E.department_id =
 D.department_id(+) AND D.locati
on_id = L.location_id(+) AND E.d
epartment_id = 40

HR     MJV_LOC_DEPT_EMPS  QSM-01150: query did SELECT L.city, E.department_id,
                           not rewrite         D.department_name, E.employee_id
                                               , E.first_name, E.last_name, E.h
                                               ire_date, E.salary FROM hr.locat
                                               ions L, hr.departments D, hr.emp
                                               loyees E WHERE E.department_id =
                                                D.department_id(+) AND D.locati
                                               on_id = L.location_id(+)
SELECT L.city, E.department_id,
D.department_name, E.employee_id
, E.first_name, E.last_name, E.h
ire_date, E.salary FROM hr.locat
ions L, hr.departments D, hr.emp
loyees E WHERE E.department_id =
 D.department_id(+) AND D.locati
on_id = L.location_id(+)

/* 
|| Listing 1.4: Using DBMS_MVIEW.EXPLAIN_MVIEW to show all capabilities 
||              of a materialized view
*/

-----
-- Create the MV_CAPABILITIES_TABLE analysis table
-----
SQL> @?/rdbms/admin/utlxmv.sql;


-----
-- Run DBMS_MVIEW.EXPLAIN_MVIEW to populate MV_CAPABILITIES_TABLE
-----
TRUNCATE TABLE mv_capabilities_table;
BEGIN 
     DBMS_MVIEW.EXPLAIN_MVIEW(
          mv => 'HR.MJV_LOC_DEPT_EMPS'
         ,stmt_id => 'MJV_1.1'
     );
     COMMIT;
END;
/

-- What capabilities does this MV have?
TTITLE 'Current Contents of MV_CAPABILITIES_TABLE'
COL capability_name     FORMAT A30  HEADING 'Capability'
COL possible            FORMAT A05  HEADING 'Poss-|ible?'
COL related_text        FORMAT A20  HEADING 'Related Text' WRAP
COL msgtxt              FORMAT A36  HEADING 'Message Text' WRAP
SELECT 
     capability_name
    ,possible
    ,related_text 
    ,msgtxt
  FROM mv_capabilities_table
 WHERE mvowner = 'HR'
   AND mvname = 'MJV_LOC_DEPT_EMPS'
 ORDER BY capability_name
;
TTITLE OFF 

>>> Resulting output:

Sun Aug 20                   Current Contents of MV_CAPABILITIES_TABLE           page    1

                                  

                               Poss-
Capability                     ible? Related Text     Message Text
------------------------------ ----- ---------------- ------------------------------------
PCT                            N
PCT_TABLE                      N     HR.LOCATIONS     relation is not a partitioned table
PCT_TABLE                      N     HR.DEPARTMENTS   relation is not a partitioned table
PCT_TABLE                      N     HR.EMPLOYEES     relation is not a partitioned table
PCT_TABLE_REWRITE              N     HR.EMPLOYEES     relation is not a partitioned table
PCT_TABLE_REWRITE              N     HR.DEPARTMENTS   relation is not a partitioned table
PCT_TABLE_REWRITE              N     HR.LOCATIONS     relation is not a partitioned table
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y
REFRESH_FAST_AFTER_ANY_DML     Y
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  Y
REFRESH_FAST_PCT               N                      PCT is not possible on any of the de
                                                      tail tables in the materialized view

REWRITE                        Y
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_GENERAL                Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_PCT                    N                      general rewrite is not possible or P
                                                      CT is not possible on any of the det
                                                      ail tables

/* 
|| Listing 1.5: Tuning a materialized view with DBMS_ADVISOR.TUNE_MVIEW
*/

-----
-- Create a materialized view with an INLINE view that can't be "flattened"
-- and therefore is not refreshable
-----
DROP MATERIALIZED VIEW sh.cust_avgsales_mv;
CREATE MATERIALIZED VIEW sh.cust_avgsales_mv
    REFRESH FAST 
    ENABLE QUERY REWRITE 
    AS
    SELECT cust_id, avg(dollar_sales)
      FROM (
        SELECT 
             P.prod_name
            ,S.cust_id
            ,SUM(S.amount_sold) AS dollar_sales
          FROM 
             sh.sales S
            ,sh.products P
         WHERE S.prod_id = P.prod_id
         GROUP BY P.prod_name, S.cust_id
         ) prod_sales
     GROUP BY cust_id
;
-----
-- Use DBMS_ADVISOR.TUNE_MVIEW to determine how best to tune this MV
-----
SET SERVEROUTPUT ON
DECLARE
    task_name VARCHAR2(32);
    mv_statement VARCHAR2(1000);
   
BEGIN
    mv_statement := 
        'CREATE MATERIALIZED VIEW prod_mv ' || 
        'REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS ' || 
        'SELECT DISTINCT prod_name, prod_category FROM sh.products' ;
    DBMS_ADVISOR.TUNE_MVIEW (
         task_name
        ,mv_statement
    ); 
    DBMS_OUTPUT.PUT_LINE('Task Name: ' || task_name);
END;
/

-----
-- List recommendations for MV performance improvements from DBA_TUNE_MVIEW
-----
SET PAGESIZE 50
SET LINESIZE 110
TTITLE 'Recommendations from Latest Run of DBMS_MVIEW.TUNE_MVIEW'
COL script_type     FORMAT A15  HEADING 'Script|Type'
COL repair_stmt     FORMAT A65  HEADING 'SQL Statement' WRAP
SELECT 
    script_type
   ,TO_CHAR(statement) repair_stmt
  FROM dba_tune_mview
 ORDER BY action_id
;
TTITLE OFF  

>>> Output from query:

Fri Aug 18      Recommendations from Latest Run of DBMS_MVIEW.TUNE_MVIEW      Page    1 

Script
Type            SQL Statement
--------------- -----------------------------------------------------------------
IMPLEMENTATION  CREATE MATERIALIZED VIEW LOG ON "SH"."PRODUCTS" WITH ROWID, SEQUE
                NCE ("PROD_NAME","PROD_CATEGORY")  INCLUDING NEW VALUES

IMPLEMENTATION  ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."PRODUCTS" ADD ROWID, S
                EQUENCE ("PROD_NAME","PROD_CATEGORY")  INCLUDING NEW VALUES

IMPLEMENTATION  CREATE MATERIALIZED VIEW SYS.PROD_MV   REFRESH FAST WITH ROWID EN
                ABLE QUERY REWRITE AS SELECT SH.PRODUCTS.PROD_CATEGORY C1, SH.PRO
                DUCTS.PROD_NAME C2, COUNT(*) M1 FROM SH.PRODUCTS GROUP BY SH.PROD
                UCTS.PROD_CATEGORY, SH.PRODUCTS.PROD_NAME

UNDO            DROP MATERIALIZED VIEW SYS.PROD_MV	

/* 
|| Listing 1.6: Using Trusted Constraints in a materialized view
*/

-----
-- GRANT necessary privileges to HR objects
-----
GRANT SELECT     ON hr.employees TO sh;
GRANT REFERENCES ON hr.employees TO sh;

-----
-- Create SALES_AREAS table
-----
DROP TABLE sh.sales_areas CASCADE CONSTRAINTS;
CREATE TABLE sh.sales_areas (
     sales_area_id      NUMBER(5)    NOT NULL
    ,hierarchy_level    NUMBER(3)    NOT NULL
    ,abbr               CHAR(4)      NOT NULL
    ,description        VARCHAR2(32) DEFAULT 'Undefined' NOT NULL
)
  TABLESPACE example
;

ALTER TABLE sh.sales_areas
    ADD CONSTRAINT sales_areas_pk
    PRIMARY KEY (sales_area_id)
    USING INDEX (
        CREATE INDEX sh.sales_areas_pk_idx
            ON sh.sales_areas (sales_area_id)
            TABLESPACE example
    )
;

-----
-- Create SALES_AREA_HIERARCHIES table
-----
DROP TABLE sh.sales_area_hierarchies CASCADE CONSTRAINTS;
CREATE TABLE sh.sales_area_hierarchies (
     parent_sales_area_id   NUMBER(5)   NOT NULL
    ,child_sales_area_id    NUMBER(5)   NOT NULL
    ,hierarchy_id           NUMBER(3)   NOT NULL
)
  TABLESPACE example
;

ALTER TABLE sh.sales_hierarchies
    ADD CONSTRAINT sales_hierarchies_pk
    PRIMARY KEY (parent_sales_area_id, child_sales_area_id)
    USING INDEX(
        CREATE INDEX sh.sales_hierarchies_pk_idx
            ON sh.sales_hierarchies (parent_sales_area_id, child_sales_area_id)
            TABLESPACE example
    ) 
;

ALTER TABLE sh.sales_hierarchies 
    ADD CONSTRAINT sales_hierarchies_areas_fk
    FOREIGN KEY (parent_sales_area_id)
    REFERENCES sh.sales_areas (sales_area_id);

ALTER TABLE sh.sales_force_assignments
    ADD CONSTRAINT sales_force_assign_emp_fk
    FOREIGN KEY (employee_id)
    REFERENCES hr.employees (employee_id)
    DISABLE NOVALIDATE;

-----
-- Create SALES_FORCE_ASSIGNMENTS table
-----
DROP TABLE sh.sales_force_assignments CASCADE CONSTRAINTS;
CREATE TABLE sh.sales_force_assignments
(
     sales_area_id  NUMBER(5)   NOT NULL
    ,employee_id    NUMBER(10)  NOT NULL
    ,effect_dt      DATE        NOT NULL
    ,status_ind     CHAR(1)     DEFAULT 'Y' NOT NULL 
)
  TABLESPACE example
;

ALTER TABLE sh.sales_force_assignments
    ADD CONSTRAINT sales_force_assignments_pk
    PRIMARY KEY (sales_area_id, employee_id, effect_dt)
    USING INDEX (
        CREATE INDEX sh.sales_force_assignments_pk_idx
            ON sh.sales_force_assignments (sales_area_id, employee_id, effect_dt)
            TABLESPACE example
    );

ALTER TABLE sh.sales_force_assignments
    ADD CONSTRAINT sales_force_assign_area_fk
    FOREIGN KEY (sales_area_id)
    REFERENCES sh.sales_areas (sales_area_id);

ALTER TABLE sh.sales_force_assignments
    ADD CONSTRAINT sales_force_assign_emp_fk
    FOREIGN KEY (employee_id)
    REFERENCES hr.employees (employee_id)
    DISABLE NOVALIDATE;

-----
-- Perform initial data loads of reference data
-----
INSERT INTO sh.sales_areas VALUES ( 0, 0, 'CMPY', 'Company');
INSERT INTO sh.sales_areas VALUES ( 1, 1, 'NE00', 'Northeastern United States');
INSERT INTO sh.sales_areas VALUES ( 2, 1, 'SE00', 'Southeastern United States');
INSERT INTO sh.sales_areas VALUES ( 3, 1, 'CN00', 'Central United States');
INSERT INTO sh.sales_areas VALUES ( 4, 1, 'SW00', 'Southwestern United States');
INSERT INTO sh.sales_areas VALUES ( 5, 1, 'NW00', 'Northwestern United States');
INSERT INTO sh.sales_areas VALUES ( 6, 2, 'NE10', 'New England');
INSERT INTO sh.sales_areas VALUES ( 7, 2, 'NE20', 'New York');
INSERT INTO sh.sales_areas VALUES ( 8, 2, 'SE10', 'MidAtlantic');
INSERT INTO sh.sales_areas VALUES ( 9, 2, 'SE20', 'Deep South');
INSERT INTO sh.sales_areas VALUES (10, 2, 'CN10', 'Midwest');
INSERT INTO sh.sales_areas VALUES (11, 2, 'CN20', 'Great Plains');
INSERT INTO sh.sales_areas VALUES (12, 2, 'SW10', 'Pacific Coast');
INSERT INTO sh.sales_areas VALUES (13, 2, 'SW20', 'West Central');
INSERT INTO sh.sales_areas VALUES (14, 2, 'NW10', 'Oregon-Washington');
INSERT INTO sh.sales_areas VALUES (15, 2, 'NW20', 'Mid NorthWest');
COMMIT;

INSERT INTO sh.sales_area_hierarchies VALUES ( 0,  1, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 0,  2, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 0,  3, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 0,  4, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 0,  5, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 1,  6, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 1,  7, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 2,  8, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 2,  9, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 3, 10, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 3, 11, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 4, 12, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 4, 13, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 5, 14, 1);
INSERT INTO sh.sales_area_hierarchies VALUES ( 5, 15, 1);
COMMIT;

-----
-- Perform initial data load of reference data 
-----
INSERT INTO sh.sales_force_assignments 
VALUES ( 0, 101, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES ( 1, 110, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES ( 2, 111, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES ( 3, 112, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES ( 4, 113, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES ( 5, 114, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES ( 6, 121, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES ( 7, 122, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES ( 8, 123, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES ( 9, 124, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (10, 125, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (11, 126, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (12, 127, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (13, 128, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (14, 129, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (15, 130, TO_DATE('01/01/1998','mm/dd/yyyy'), 'Y');
COMMIT;

-----
-- Now add data to the SH.SALES_FORCE_ASSIGNMENTS table that will refer to
-- employees that don't exist
-----
INSERT INTO sh.sales_force_assignments 
VALUES (10, 901, TO_DATE('01/01/2006','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (10, 902, TO_DATE('02/01/2006','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (10, 903, TO_DATE('03/01/2006','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (15, 901, TO_DATE('01/01/2006','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (15, 902, TO_DATE('02/01/2006','mm/dd/yyyy'), 'Y');
INSERT INTO sh.sales_force_assignments 
VALUES (15, 903, TO_DATE('03/01/2006','mm/dd/yyyy'), 'Y');
COMMIT;

-----
-- Activate the constraint on HR.EMPLOYEES.EMPLOYEE_ID in ENABLE 
-- NOVALIDATE RELY mode clause to indicate that the data can be 
-- trusted
-----
ALTER TABLE sh.sales_force_assignments
    MODIFY CONSTRAINT sales_force_assign_emp_fk ENABLE NOVALIDATE;
ALTER TABLE sh.sales_force_assignments
    MODIFY CONSTRAINT sales_force_assign_emp_fk RELY;    

-----
-- Create materialized view logs
-----
DROP MATERIALIZED VIEW LOG ON sh.sales_areas;
CREATE MATERIALIZED VIEW LOG ON sh.sales_areas
    WITH SEQUENCE, ROWID (
         sales_area_id
        ,hierarchy_level
        ,abbr
        ,description
    )
    INCLUDING NEW VALUES;
    
DROP MATERIALIZED VIEW LOG ON sh.sales_area_hierarchies;
CREATE MATERIALIZED VIEW LOG ON sh.sales_area_hierarchies
    WITH SEQUENCE, ROWID (
         parent_sales_area_id
        ,child_sales_area_id
        ,hierarchy_id
    )
    INCLUDING NEW VALUES
;

DROP MATERIALIZED VIEW LOG ON sh.sales_force_assignments;
CREATE MATERIALIZED VIEW LOG ON sh.sales_force_assignments
    WITH SEQUENCE, ROWID (
         sales_area_id
        ,employee_id
        ,effect_dt
        ,status_ind
    )
    INCLUDING NEW VALUES
;

-----
-- Create a join-only materialized view that gathers data for the
-- current sales force hierarchy.
-- Note that:
-- 1. It will be built immediately.
-- 2. It will be refreshed if any table is changed
--    because each table has a materialized log view.
-- 3. QUERY REWRITE is enabled.
-- 4. USE TRUSTED CONSTRAINTS is activated.
-----
DROP MATERIALIZED VIEW sh.mjv_current_sales_force;
CREATE MATERIALIZED VIEW sh.mjv_current_sales_force
    PARALLEL 
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    USING TRUSTED CONSTRAINTS
    ENABLE QUERY REWRITE
AS
    SELECT 
         SAH.rowid  sah_rid
        ,SPA.rowid  spa_rid
        ,SFP.rowid  sfp_rid
        ,EP.rowid   ep_rid
        ,SCA.rowid  sca_rid
        ,SFC.rowid  sfc_rid
        ,EC.rowid   ec_rid
        ,SAH.parent_sales_area_id
        ,SPA.abbr parent_abbr
        ,SFP.employee_id parent_emp_id
        ,EP.last_name || ', ' || EP.first_name parent_name
        ,SAH.child_sales_area_id 
        ,SCA.abbr child_abbr
        ,SFC.employee_id child_emp_id
        ,EC.last_name || ', ' || EC.first_name child_name
      FROM
         sh.sales_area_hierarchies SAH
        ,sh.sales_areas SPA
        ,sh.sales_areas SCA
        ,sh.sales_force_assignments SFC
        ,sh.sales_force_assignments SFP
        ,hr.employees EP
        ,hr.employees EC
      WHERE SAH.parent_sales_area_id = SPA.sales_area_id    
        AND SAH.child_sales_area_id = SCA.sales_area_id
        AND SAH.parent_sales_area_id = SFP.sales_area_id
        AND SAH.child_Sales_Area_Id = SFC.sales_area_id
        AND SFP.employee_id = EP.employee_id
        AND SFC.employee_id = EC.employee_id
;