A dimension declares a parent/child relationship between pairs of columns. We can use it to describe to Oracle that, within a row of a table, the MONTH column implies the
value you’ll find in the QTR column, the QTR column implies the value you’ll find in the YEAR column, and so on. Using a dimension, we can have a materialized view that has fewer details than the detailed records (summarized to the monthly level perhaps). This may still be at a higher level of aggregation than the query requests (the query wants data by quarter, say), but Oracle will recognize that it can use the materialized view to get the answer.
create table sales
2 (trans_date date, cust_id int, sales_amount number );
Now we need to set up our TIME_HIERARCHY table, to roll up the date field by month, year, quarter, and so on:
create table time_hierarchy
2 (day primary key, mmyyyy, mon_yyyy, qtr_yyyy, yyyy)
3 organization index
4 as
5 select distinct
6 trans_date DAY,
7 cast (to_char(trans_date,’mmyyyy’) as number) MMYYYY,
8 to_char(trans_date,’mon-yyyy’) MON_YYYY,
9 ‘Q’ || ceil( to_char(trans_date,’mm’)/3) || ‘ FY’
10 || to_char(trans_date,’yyyy’) QTR_YYYY,
11 cast( to_char( trans_date, ‘yyyy’ ) as number ) YYYY
12 from sales
create materialized view sales_mv
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select sales.cust_id, sum(sales.sales_amount) sales_amount,
7 time_hierarchy.mmyyyy
8 from sales, time_hierarchy
9 where sales.trans_date = time_hierarchy.day
10 group by sales.cust_id, time_hierarchy.mmyyyy
create dimension time_hierarchy_dim
2 level day is time_hierarchy.day
3 level mmyyyy is time_hierarchy.mmyyyy
4 level qtr_yyyy is time_hierarchy.qtr_yyyy
5 level yyyy is time_hierarchy.yyyy
6 hierarchy time_rollup
7 (
8 day child of
9 mmyyyy child of
10 qtr_yyyy child of
11 yyyy
12 )
13 attribute mmyyyy
14 determines mon_yyyy;
This tells Oracle that the DAY column of the TIME_HIERARCHY table implies MMYYYY, which in turn implies QTR_YYYY. Finally, QTR_YYYY implies YYYY.
By creating the above dimension, the above MV will be used by query re-writes even if higher levels of aggregation was used in calling sqls.
value you’ll find in the QTR column, the QTR column implies the value you’ll find in the YEAR column, and so on. Using a dimension, we can have a materialized view that has fewer details than the detailed records (summarized to the monthly level perhaps). This may still be at a higher level of aggregation than the query requests (the query wants data by quarter, say), but Oracle will recognize that it can use the materialized view to get the answer.
create table sales
2 (trans_date date, cust_id int, sales_amount number );
Now we need to set up our TIME_HIERARCHY table, to roll up the date field by month, year, quarter, and so on:
create table time_hierarchy
2 (day primary key, mmyyyy, mon_yyyy, qtr_yyyy, yyyy)
3 organization index
4 as
5 select distinct
6 trans_date DAY,
7 cast (to_char(trans_date,’mmyyyy’) as number) MMYYYY,
8 to_char(trans_date,’mon-yyyy’) MON_YYYY,
9 ‘Q’ || ceil( to_char(trans_date,’mm’)/3) || ‘ FY’
10 || to_char(trans_date,’yyyy’) QTR_YYYY,
11 cast( to_char( trans_date, ‘yyyy’ ) as number ) YYYY
12 from sales
create materialized view sales_mv
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select sales.cust_id, sum(sales.sales_amount) sales_amount,
7 time_hierarchy.mmyyyy
8 from sales, time_hierarchy
9 where sales.trans_date = time_hierarchy.day
10 group by sales.cust_id, time_hierarchy.mmyyyy
create dimension time_hierarchy_dim
2 level day is time_hierarchy.day
3 level mmyyyy is time_hierarchy.mmyyyy
4 level qtr_yyyy is time_hierarchy.qtr_yyyy
5 level yyyy is time_hierarchy.yyyy
6 hierarchy time_rollup
7 (
8 day child of
9 mmyyyy child of
10 qtr_yyyy child of
11 yyyy
12 )
13 attribute mmyyyy
14 determines mon_yyyy;
This tells Oracle that the DAY column of the TIME_HIERARCHY table implies MMYYYY, which in turn implies QTR_YYYY. Finally, QTR_YYYY implies YYYY.
By creating the above dimension, the above MV will be used by query re-writes even if higher levels of aggregation was used in calling sqls.