SET pages    999
SET linesize 300
SET trimout   ON
SET trimspool ON
SET timing    OFF
SET echo      OFF
SET feedback  ON
-- ****************************************************************************
--    Cube MVs:  Query a fact table with and without query rewrite
-- ****************************************************************************

--     To turn on/off rewrite across database sessions, run the
--     "ALTER materialized view" command below. 

-- Rewrite whenever possible
-- ALTER SESSION SET query_rewrite_integrity = stale_tolerated;
-- ALTER SESSION SET query_rewrite_enabled   = force;  
  
-- Turn off rewrite for session   
-- ALTER SESSION SET query_rewrite_enabled = false;  
  
-- Turn on/off rewrite on MV
-- Enable/Disable rewrite to the cube MV
-- ALTER materialized VIEW cb$sales_cube disable query rewrite;
-- ALTER materialized VIEW cb$sales_cube enable  query rewrite;

--   Run the queries (F9) and view explain plans (F6) with rewrite enabled and disabled
--   Example queries below were captured from a BI EE ad-hoc query session

-- 1st run: QUERY REWRITE DISABLED
ALTER MATERIALIZED VIEW cb$sales_cube DISABLE QUERY REWRITE;

-- 1. Quantity and Sales by Year and Product Category
COLUMN quantity FORMAT 999,990.000
COLUMN sales    FORMAT 999,999,990.000
COLUMN cy       FORMAT a10
COLUMN cat      FORMAT a35
SET timing    OFF
EXPLAIN PLAN FOR
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.category_name      AS cat
FROM  times      t
,     products   p
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key  = t.day_key
GROUP BY p.category_name
,        t.calendar_year_name;

@c:\oracle\product\11.1.0\rdbms\admin\utlxplp

SET timing    ON
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.category_name      AS cat
FROM  times      t
,     products   p
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key  = t.day_key
GROUP BY p.category_name
,        t.calendar_year_name;

-- 2. Quantity and Sales by Year, Department, Class and Country
COLUMN dept      FORMAT a30
COLUMN class     FORMAT a30
COLUMN country   FORMAT a30
SET timing    OFF
EXPLAIN PLAN FOR
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.department_name    AS dept
,      c.class_name         AS class
,      cu.country_name      AS country
FROM  times      t
,     products   p
,     channels   c
,     customers  cu
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key  = t.day_key
AND   s.channel  = c.channel_key 
AND   s.customer = cu.customer_key
GROUP BY p.department_name
,        t.calendar_year_name
,        c.class_name
,        cu.country_name;

@c:\oracle\product\11.1.0\rdbms\admin\utlxplp


SET timing    ON
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.department_name    AS dept
,      c.class_name         AS class
,      cu.country_name      AS country
FROM  times      t
,     products   p
,     channels   c
,     customers  cu
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key  = t.day_key
AND   s.channel  = c.channel_key 
AND   s.customer = cu.customer_key
GROUP BY p.department_name
,        t.calendar_year_name
,        c.class_name
,        cu.country_name;

-- 3. Quantity and Sales by Year, Category, Class and Region
COLUMN region   FORMAT a30
SET timing    OFF
EXPLAIN PLAN FOR
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.category_name      AS cat
,      c.class_name         AS class
,      cu.region_name       AS region
FROM  times      t
,     products   p
,     channels   c
,     customers  cu
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key = t.day_key
AND   s.channel = c.channel_key 
AND   s.customer = cu.customer_key
GROUP BY p.category_name
,        t.calendar_year_name
,        c.class_name
,        cu.region_name;    

@c:\oracle\product\11.1.0\rdbms\admin\utlxplp

SET timing    ON
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.category_name      AS cat
,      c.class_name         AS class
,      cu.region_name       AS region
FROM  times      t
,     products   p
,     channels   c
,     customers  cu
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key = t.day_key
AND   s.channel = c.channel_key 
AND   s.customer = cu.customer_key
GROUP BY p.category_name
,        t.calendar_year_name
,        c.class_name
,        cu.region_name;    

-- 4. Quantity & Sales by Year, Department, and Region
SET timing    OFF
EXPLAIN PLAN FOR
SELECT  SUM(s.quantity)      AS quantity
,       SUM(s.sales)         AS sales
,       t.calendar_year_name AS cy
,       p.department_name    AS dept
,       cu.region_name       AS region
FROM  times      t
,     customers  cu
,     products   p
,     sales_fact s
WHERE(cu.customer_key = s.customer
AND   p.item_key      = s.product
AND   s.day_key       = t.day_key)
GROUP BY cu.region_name
,        p.department_name
,        t.calendar_year_name;

@c:\oracle\product\11.1.0\rdbms\admin\utlxplp

SET timing    ON
SELECT  SUM(s.quantity)      AS quantity
,       SUM(s.sales)         AS sales
,       t.calendar_year_name AS cy
,       p.department_name    AS dept
,       cu.region_name       AS region
FROM  times      t
,     customers  cu
,     products   p
,     sales_fact s
WHERE(cu.customer_key = s.customer
AND   p.item_key      = s.product
AND   s.day_key       = t.day_key)
GROUP BY cu.region_name
,        p.department_name
,        t.calendar_year_name;

-- 2nd run: QUERY REWRITE ENABLED
SET timing    OFF
ALTER SESSION SET query_rewrite_integrity = stale_tolerated;
ALTER SESSION SET query_rewrite_enabled   = force;
ALTER MATERIALIZED VIEW cb$sales_cube ENABLE QUERY REWRITE;

-- 1. Quantity and Sales by Year and Product Category
COLUMN quantity FORMAT 999,990.000
COLUMN sales    FORMAT 999,999,990.000
COLUMN cy       FORMAT a10
COLUMN cat      FORMAT a35
SET timing    OFF
EXPLAIN PLAN FOR
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.category_name      AS cat
FROM  times      t
,     products   p
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key  = t.day_key
GROUP BY p.category_name
,        t.calendar_year_name;

@c:\oracle\product\11.1.0\rdbms\admin\utlxplp

SET timing    ON
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.category_name      AS cat
FROM  times      t
,     products   p
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key  = t.day_key
GROUP BY p.category_name
,        t.calendar_year_name;

-- 2. Quantity and Sales by Year, Department, Class and Country
COLUMN dept      FORMAT a30
COLUMN class     FORMAT a30
COLUMN country   FORMAT a30
SET timing    OFF
EXPLAIN PLAN FOR
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.department_name    AS dept
,      c.class_name         AS class
,      cu.country_name      AS country
FROM  times      t
,     products   p
,     channels   c
,     customers  cu
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key  = t.day_key
AND   s.channel  = c.channel_key 
AND   s.customer = cu.customer_key
GROUP BY p.department_name
,        t.calendar_year_name
,        c.class_name
,        cu.country_name;

@c:\oracle\product\11.1.0\rdbms\admin\utlxplp

SET timing    ON
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.department_name    AS dept
,      c.class_name         AS class
,      cu.country_name      AS country
FROM  times      t
,     products   p
,     channels   c
,     customers  cu
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key  = t.day_key
AND   s.channel  = c.channel_key 
AND   s.customer = cu.customer_key
GROUP BY p.department_name
,        t.calendar_year_name
,        c.class_name
,        cu.country_name;

-- 3. Quantity and Sales by Year, Category, Class and Region
COLUMN region   FORMAT a30
SET timing    OFF
EXPLAIN PLAN FOR
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.category_name      AS cat
,      c.class_name         AS class
,      cu.region_name       AS region
FROM  times      t
,     products   p
,     channels   c
,     customers  cu
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key = t.day_key
AND   s.channel = c.channel_key 
AND   s.customer = cu.customer_key
GROUP BY p.category_name
,        t.calendar_year_name
,        c.class_name
,        cu.region_name;    

@c:\oracle\product\11.1.0\rdbms\admin\utlxplp

SET timing    ON
SELECT SUM(s.quantity)      AS quantity
,      SUM(s.sales)         AS sales
,      t.calendar_year_name AS cy
,      p.category_name      AS cat
,      c.class_name         AS class
,      cu.region_name       AS region
FROM  times      t
,     products   p
,     channels   c
,     customers  cu
,     sales_fact s
WHERE p.item_key = s.product
AND   s.day_key = t.day_key
AND   s.channel = c.channel_key 
AND   s.customer = cu.customer_key
GROUP BY p.category_name
,        t.calendar_year_name
,        c.class_name
,        cu.region_name;    

-- 4. Quantity & Sales by Year, Department, and Region
SET timing    OFF
EXPLAIN PLAN FOR
SELECT  SUM(s.quantity)      AS quantity
,       SUM(s.sales)         AS sales
,       t.calendar_year_name AS cy
,       p.department_name    AS dept
,       cu.region_name       AS region
FROM  times      t
,     customers  cu
,     products   p
,     sales_fact s
WHERE(cu.customer_key = s.customer
AND   p.item_key      = s.product
AND   s.day_key       = t.day_key)
GROUP BY cu.region_name
,        p.department_name
,        t.calendar_year_name;

@c:\oracle\product\11.1.0\rdbms\admin\utlxplp

SET timing    ON
SELECT  SUM(s.quantity)      AS quantity
,       SUM(s.sales)         AS sales
,       t.calendar_year_name AS cy
,       p.department_name    AS dept
,       cu.region_name       AS region
FROM  times      t
,     customers  cu
,     products   p
,     sales_fact s
WHERE(cu.customer_key = s.customer
AND   p.item_key      = s.product
AND   s.day_key       = t.day_key)
GROUP BY cu.region_name
,        p.department_name
,        t.calendar_year_name;


