Advanced Techniques for optimizing data warehouses Using Dimension objects

ExploitationDimensionAdvanced Techniques for optimizing Data Warehouses

Author:Anysql.netDuring reprinting, be sure to mark the original source and author information in the form of hyperlinks. Link:Http://www.anysql.net/Oracle/Oracle_Olap_dimension.html

InOracleIn the data warehouse (OLAP), the materialized view (mview), query rewrite (query rewrite) andDimension(Dimension) is a very important optimization method. I don't want to repeat the previous two here. I mainly want to try it out.DimensionTo play a roleDimensionI still need to use the previous two. below is only oneDimensionThe simplest example of a table. In addition to connect and resource, database users must also grant query rewrite, create materialized view, and create dimension permissions.

1. CreateDimensionTable protection.

Create Table time_dim
As
Select to_char (sysdate + rownum, 'yyyy') f_year,
To_char (sysdate + rownum, 'yyyy-Q') f_quater,
To_char (sysdate + rownum, 'yyyy-mm') f_month,
Trunc (sysdate + rownum, 'dd') f_day
From dba_objects
Where rownum: <1000;
Alter table time_dim modify f_year not null;
Alter table time_dim modify f_quater not null;
Alter table time_dim modify f_month not null;
Alter table time_dim modify f_day not null;
Alter table time_dim add primary key (f_day );

2. Create a fact table.

Create Table fact_sales
As
Select trunc (sysdate + rownum, 'dd') f_day,
Trunc (dbms_random.value * random, 2) m_amount1,
Trunc (dbms_random.value * random, 2) m_amount2
From dba_objects
Where rownum: <1000;

-- Please execute the following insert multiple times
Insert into fact_sales
Select f_day,
Trunc (dbms_random.value * random, 2) m_amount1,
Trunc (dbms_random.value * random, 2) m_amount2
From fact_sales
/

Alter table fact_sales modify f_day not null;
Alter table fact_sales add foreign key (f_day) References time_dim (f_day );

3. generate an intermediate table by month.

Create materialized view mv_fact_sales
Enable query rewrite
As
Select D. F _ month,
Sum (f.m _ amount1) m_amount1,
Sum (f.m _ amount2) m_amount2
From time_dim D, fact_sales F
Where D. F _ day = f.f _ day
Group by D. F _ month
/

4. Analyze tables and enable query rewriting at the session level.

Analyze table time_dim compute statistics;
Analyze table fact_sales compute statistics;
Analyze table mv_fact_sales compute statistics;
Alter session set query_rewrite_enabled = true;
Alter session set query_rewrite_integrity = trusted;

5. First, let's take a look at the execution plan of the SQL statement summarized by quarter. Theoretically, it can be further summarized and generated from the Summary by month,OracleIt cannot be executed like this.

Asql> select D. F _ quater,
2 sum (f.m _ amount1) m_ammount1,
3 sum (f.m _ amount2) m_ammount2
4 From time_dim D, fact_sales F
5 where D. F _ day = f.f _ day
6 group by D. F _ quater
7/

Execute Plan
Sqlplan cost card Kbyte PS PE
---------------------------------------------------------------------------
0 SELECT statement optimizer = all_rows 626 12 1
1 0 Hash (group by) 626 12 1
2 1 hash join 609 287712 16858
3 2 Table Access (full) of time_dim (table) 4 999 13
4 2 Table Access (full) of fact_sales (table) 602 287712 13206

6. CreateDimensionObject To tellOracleTree relationships exist on the four fields in the time_dim table. If this statement is not found,OracleThe data is not attached to this tree relationship.

Create dimension time_dim
Level year is (time_dim.f_year)
Level quater is (time_dim.f_quater)
Level Month is (time_dim.f_month)
Level day is (time_dim.f_day)
Hierarchy y_q_m_d
(
Day Child
Month child
Quater child of year
)
Hierarchy y_m_d
(
Day Child
Month child of year
)
/

7. BuildDimensionThen, run the SQL statement on a quarterly or yearly basis to check whether their execution plans are different?

Asql> select D. F _ quater,
2 sum (f.m _ amount1) m_ammount1,
3 sum (f.m _ amount2) m_ammount2
4 From time_dim D, fact_sales F
5 where D. F _ day = f.f _ day
6 group by D. F _ quater
7/

Execute Plan
Sqlplan cost card Kbyte PS PE
----------------------------------------------------------------------------
0 SELECT statement optimizer = all_rows 10 12 1
1 0 Hash (group by) 10 12 1
2 1 hash join 9 289 17
3 2 mat_view rewrite access (full) of mv_fact _... 3 34 2
4 2 view of 5 289 4
5 4 Hash (unique) 5 289 4
6 5 Table Access (full) of time_dim (table) 4 999 13

Asql> select/* + all_rows */D. F _ year,
2 sum (f.m _ amount1) m_ammount1,
3 sum (f.m _ amount2) m_ammount2
4 From time_dim D, fact_sales F
5 where D. F _ day = f.f _ day
6 group by D. F _ year
7/

Execute Plan
Sqlplan cost card Kbyte PS PE
-----------------------------------------------------------------------------
0 SELECT statement optimizer = hint: all_rows 10 4 0
1 0 Hash (group by) 10 4 0
2 1 hash join 9 97 6
3 2 mat_view rewrite access (full) of mv_fact_s... 3 34 2
4 2 view of 5 97 1
5 4 Hash (unique) 5 97 1
6 5 Table Access (full) of time_dim (table) 4 999 11

I don't know how to explain it in a language table, so I designed this example to explain it. If you don't understand it, please read it several times.