Oracle Dimension Learning Example ____oracle

--create Sales Table
CREATE TABLE Sales
(trans_date date, cust_id INT, sales_amount number);

--insert Sale rows
INSERT/*+ APPEND * *
Into SALES
SELECT TRUNC (Sysdate, ' year ') + MOD (rownum, 366) trans_date,
MOD (RowNum, cust_id),
ABS (Dbms_random. RANDOM)/Sales_Amount
From All_objects;

SELECT COUNT (*) from sales;

--go on inserting rows
BEGIN
For I in 1. 6 LOOP
INSERT/*+ APPEND * *
Into SALES
SELECT Trans_date,
cust_id,
ABS (Dbms_random. RANDOM)/Sales_Amount
From SALES;
COMMIT;
End LOOP;
End;

--create Index organized table
CREATE TABLE time_hierarchy (Day PRIMARY KEY, mmyyyy, mon_yyyy, QTR_YYYY, YYYY) organization INDEX AS
SELECT DISTINCT Trans_date Day,
CAST (To_char (trans_date, ' mmyyyy ') as number) mmyyyy,
To_char (trans_date, ' mon-yyyy ') mon_yyyy,
' Q ' | | Ceil (To_char (trans_date, ' mm ')/3) | | ' FY ' | |
To_char (trans_date, ' yyyy ') qtr_yyyy,
CAST (To_char (trans_date, ' yyyy ') as number) yyyy
From SALES;

--create materialized View
CREATE materialized VIEW mv_sales build IMMEDIATE REFRESH on DEMAND ENABLE QUERY REWRITE as
SELECT SALES. cust_id,
SUM (SALES. Sales_Amount) Sales_Amount,
Time_hierarchy. Mmyyyy
From SALES, Time_hierarchy
WHERE SALES. Trans_date = Time_hierarchy. Day
GROUP by SALES. cust_id, Time_hierarchy. mmyyyy;

Analyze table sales compute statistics;
Analyze table Time_hierarchy compute statistics;

Alter session set Query_rewrite_enabled=true;
Alter session set query_rewrite_integrity=trusted;

--query by Month
SELECT Time_hierarchy. MMYYYY, SUM (Sales_Amount)
From SALES, Time_hierarchy
WHERE SALES. Trans_date = Time_hierarchy. Day
GROUP by Time_hierarchy. Mmyyyy

--query by quarter
SELECT Time_hierarchy. QTR_YYYY, SUM (Sales_Amount)
From SALES, Time_hierarchy
WHERE SALES. Trans_date = Time_hierarchy. Day
GROUP by Time_hierarchy. qtr_yyyy;

--create Dimension
CREATE DIMENSION Time_hierarchy_dim
Level ' is time_hierarchy. Day
Level mmyyyy is time_hierarchy. Mmyyyy
Level qtr_yyyy is time_hierarchy. Qtr_yyyy
Level YYYY is time_hierarchy. YYYY
Hierarchy Time_rollup (Day of the mmyyyy child of QTR_YYYY Child of YYYY)
ATTRIBUTE mmyyyy determines mon_yyyy;

--drop Dimension
DROP DIMENSION Time_hierarchy_dim;

--yearly Query
SELECT Time_hierarchy. YYYY, SUM (Sales_Amount)
From SALES, Time_hierarchy
WHERE SALES. Trans_date = Time_hierarchy. Day
GROUP by Time_hierarchy. YYYY;

--create Index organized table
CREATE TABLE customer_hierarchy (cust_id PRIMARY KEY, Zip_Code, REGION) organization INDEX AS
SELECT cust_id,
MOD (rownum, 6) | | To_char (MOD (rownum, 1000), ' fm0000 ') Zip_Code,
MOD (rownum, 6) REGION
From (SELECT DISTINCT cust_id from SALES);

Analyze table Customer_hierarchy compute statistics;

Drop materialized view mv_sales;

--recreate materialized View
CREATE materialized view mv_sales build IMMEDIATE REFRESH on DEMAND ENABLE QUERY Rewrit E as
    SELECT customer_hierarchy. Zip_Code,
           time_hierarchy. MMYYYY,
           SUM (SALES. Sales_Amount) Sales_Amount
      from SALES, Time_hierarchy, Customer_hierarchy
     WHERE SALES. Trans_date = Time_hierarchy. Day
       and SALES. cust_id = Customer_hierarchy. cust_id
     GROUP by Customer_hierarchy. Zip_Code, Time_hierarchy. mmyyyy;

--query by month
SELECT Customer_hierarchy. Zip_Code,
Time_hierarchy. MMYYYY,
SUM (SALES. Sales_Amount) Sales_Amount
From SALES, Time_hierarchy, Customer_hierarchy
WHERE SALES. Trans_date = Time_hierarchy. Day
and SALES. cust_id = Customer_hierarchy. cust_id
GROUP by Customer_hierarchy. Zip_Code, Time_hierarchy. Mmyyyy

--query by year
SELECT Customer_hierarchy. REGION,
Time_hierarchy. YYYY,
SUM (SALES. Sales_Amount) Sales_Amount
From SALES, Time_hierarchy, Customer_hierarchy
WHERE SALES. Trans_date = Time_hierarchy. Day
and SALES. cust_id = Customer_hierarchy. cust_id
GROUP by Customer_hierarchy. REGION, Time_hierarchy. YYYY;

DROP DIMENSION Time_hierarchy_dim;

CREATE DIMENSION sales_dimension
       level cust_id is customer_hierarchy. cust_id
       level Zip_Code is customer_hierarchy. Zip_Code
       level REGION is customer_hierarchy. REGION
       level ' is time_hierarchy. Day
       level mmyyyy is time_hierarchy. MMYYYY
       level qtr_yyyy is time_hierarchy. QTR_YYYY
       level YYYY is time_hierarchy. YYYY
Hierarchy Cust_rollup (cust_id child, Zip_Code Child, REGION)
Hierarchy Time_rollup (Day child of Mmyyyy CH ILD of QTR_YYYY Child of YYYY)
ATTRIBUTE mmyyyy determines mon_yyyy;

SELECT Customer_hierarchy. REGION,
Time_hierarchy. YYYY,
SUM (SALES. Sales_Amount) Sales_Amount
From SALES, Time_hierarchy, Customer_hierarchy
WHERE SALES. Trans_date = Time_hierarchy. Day
and SALES. cust_id = Customer_hierarchy. cust_id
GROUP by Customer_hierarchy. REGION, Time_hierarchy. YYYY;

--drop DIMENSION sales_dimension;

ALTER SYSTEM FLUSH Buffer_cache;
ALTER SYSTEM FLUSH Shared_pool;

SELECT * from Dba_dimensions;
SELECT * from user_indexes WHERE index_type = ' iot-top ';
SELECT * from User_indextypes;

SELECT * from Time_hierarchy;
SELECT * from Customer_hierarchy;