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