wallimn
- 浏览: 5173346 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
silence19841230：
先拿走看看
SpringBoot2.0开发WebSocket应用完整示例 -
wallimn：
masuweng 写道发下源码下载地址吧!三个相关文件打了个包 ...
SpringBoot2.0开发WebSocket应用完整示例 -
masuweng：
发下源码下载地址吧!
SpringBoot2.0开发WebSocket应用完整示例 -
masuweng：
SpringBoot2.0开发WebSocket应用完整示例 -
wallimn：
水淼火 写道你好,我使用以后,图标不显示,应该怎么引用呢,谢谢 ...
前端框架iviewui使用示例之菜单+多Tab页布局
转自：http://blog.csdn.net/wzy0623/archive/2009/11/13/4805860.aspx
在数据仓库环境中，我们通常利用物化视图强大的查询重写功能来提升统计查询的性能，但是物化视图的查询重写功能有时候无法智能地判断查询中一些相关联的条件，以至于影响性能。比如我们有一张销售表sales，用于存储订单的详细信息，包含交易日期、顾客编号和销售量。我们创建一张物化视图，按月存储累计销量信息，假如这时候我们要查询按季度或者按年度统计销量信息，Oracle是否能够智能地转换查询重写呢？我们知道交易日期中的日期意味着月，月意味着所处的季度，季度意味着年度，但是Oracle却是无法智能地判断这其中的关系，因此无法利用物化视图查询重写来返回我们季度或年度的销量信息，而是直接查询基表，导致性能产生问题。
这时候Dimension就派上用场了。Dimension用于说明列之间的父子对应关系，以使优化器能够自动转换不同列的关系，利用物化视图的查询功能来提升查询统计性能。下面我们首先创建一张销售交易表sales，包含交易日期、顾客编号和销售量这几个列，用于保存销售订单信息，整个表有42万多条记录；创建另一张表time_hierarchy用于存储交易日期中时间的关系，包含交易日期及其对应的月、季度及年度等信息，然后我们将体验Dimension的强大功能。
--建表
create table sales
(trans_date date, cust_id int, sales_amount number );
Table created.
--插入数据
insert /*+ APPEND */ into sales
select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,
mod(rownum,100) CUST_ID,
abs(dbms_random.random)/100 SALES_AMOUNT
from all_objects;
5926 rows created.
commit;
Commit complete.
begin
for i in 1 .. 6
loop
insert /*+ APPEND */ into sales
select trans_date, cust_id, abs(dbms_random.random)/100 SALES_AMOUNT
from sales;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
select count(*) from sales;
COUNT(*)
----------
426672
创建索引组织表time_hierarchy，里面生成了交易日期中日期DAY、月MMYYYY、季度QTY_YYYY、年度YYYY的关系。
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
/
Table created.
接下我们创建一张物化视图mv_sales，用于存储每个客户对应每个月的销量统计信息。
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
/
Materialized view created.
我们对基表进行分析，以使优化器能够物化视图的查询重写功能：
analyze table sales compute statistics;
Table analyzed.
analyze table time_hierarchy compute statistics;
Table analyzed.
设置会话的查询重写功能：
alter session set query_rewrite_enabled=true;
Session altered.
alter session set query_rewrite_integrity=trusted;
Session altered.
接下来我们按月统计总的销量：
select time_hierarchy.mmyyyy, sum(sales_amount)
from sales, time_hierarchy
where sales.trans_date = time_hierarchy.day
group by time_hierarchy.mmyyyy
/
MMYYYY SUM(SALES_AMOUNT)
---------- -----------------
12006 4.0574E+11
12007 1.2297E+10
22006 3.6875E+11
32006 3.9507E+11
42006 3.7621E+11
52006 3.8549E+11
62006 3.6641E+11
72006 3.8110E+11
82006 3.8502E+11
92006 3.7278E+11
102006 3.7983E+11
112006 3.7210E+11
122006 3.8364E+11
13 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=327 Bytes=8502)
1 0 SORT (GROUP BY) (Cost=4 Card=327 Bytes=8502)
2 1 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=327 Bytes=8502)
Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
25 consistent gets
4 physical reads
我们可以看到查询使用了查询重写的功能，直接查询物化视图中的查询方案，而不是查询其表，逻辑IO只有25个，性能相当良好。
假如这时候我们要按季度来查询统计销量信息，结果又会是怎样呢？
select time_hierarchy.qtr_yyyy, sum(sales_amount)
2 from sales, time_hierarchy
3 where sales.trans_date = time_hierarchy.day
4 group by time_hierarchy.qtr_yyyy
5 /
QTR_YYYY SUM(SALES_AMOUNT)
------------------------------------------------ -----------------
Q1 FY2006 1.1696E+12
Q1 FY2007 1.2297E+10
Q2 FY2006 1.1281E+12
Q3 FY2006 1.1389E+12
Q4 FY2006 1.1356E+12
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1681 Card=5 Bytes=145)
1 0 SORT (GROUP BY) (Cost=1681 Card=5 Bytes=145)
2 1 NESTED LOOPS (Cost=35 Card=426672 Bytes=12373488)
3 2 TABLE ACCESS (FULL) OF 'SALES' (Cost=35 Card=426672
4 2 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_7828' (UNIQUE)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
428048 consistent gets
599 physical reads
可以看到查询将直接查询基表产生了将近428048个逻辑IO，性能无法满足需求。
接下我们创建一个Dimension表time_hierarchy_dim，用于提醒优化器time_hierarchy表中的DAY列暗示着MMYYYY，MMYYYY又意味着QTY_YYYY，QTY_YYYY又意味着YYYY。然后我们将重新运行上面那个查询，看执行计划发生了怎样的变更。
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;
Dimension created.
select time_hierarchy.qtr_yyyy, sum(sales_amount)
2 from sales, time_hierarchy
3 where sales.trans_date = time_hierarchy.day
4 group by time_hierarchy.qtr_yyyy
5 /
QTR_YYYY SUM(SALES_AMOUNT)
------------------------------------------------ -----------------
Q1 FY2006 1.1696E+12
Q1 FY2007 1.2297E+10
Q2 FY2006 1.1281E+12
Q3 FY2006 1.1389E+12
Q4 FY2006 1.1356E+12
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=5 Bytes=195)
1 0 SORT (GROUP BY) (Cost=14 Card=5 Bytes=195)
2 1 HASH JOIN (Cost=7 Card=1157 Bytes=45123)
3 2 VIEW (Cost=4 Card=46 Bytes=598)
4 3 SORT (UNIQUE) (Cost=4 Card=46 Bytes=598)
5 4 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828' (UNIQUE)
6 2 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=327
Statistics
----------------------------------------------------------
193 recursive calls
0 db block gets
49 consistent gets
2 physical reads
可以看到创建Dimension后，Oracle已经能够智能地理解交易日期中月度和季度的转换关系，查询使用到物化视图，逻辑IO由原来的428048个减少到49个，性能有了大幅的提升。
同样我们再来统计一下年度的销量信息：
select time_hierarchy.yyyy, sum(sales_amount)
2 from sales, time_hierarchy
3 where sales.trans_date = time_hierarchy.day
4 group by time_hierarchy.yyyy
5 /
YYYY SUM(SALES_AMOUNT)
---------- -----------------
2006 4.5721E+12
2007 1.2297E+10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=2 Bytes=66)
1 0 SORT (GROUP BY) (Cost=10 Card=2 Bytes=66)
2 1 HASH JOIN (Cost=7 Card=478 Bytes=15774)
我们再创建一张customer_hierarchy表，用于存储客户代码、邮政编码和地区的关系，然后我们将按不同邮编或地区来查询各自的月度、季度或者年度销量信息。
create table customer_hierarchy
2 ( cust_id primary key, zip_code, region )
3 organization index
4 as
5 select cust_id,
6 mod( rownum, 6 ) || to_char(mod( rownum, 1000 ), 'fm0000') zip_code,
7 mod( rownum, 6 ) region
8 from ( select distinct cust_id from sales)
9 /
Table created.
analyze table customer_hierarchy compute statistics;
Table analyzed.
改写物化视图，查询方案中添加按不同邮编的月度统计销量。
drop materialized view mv_sales;
Materialized view dropped.
create materialized view mv_sales
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select customer_hierarchy.zip_code,
7 time_hierarchy.mmyyyy,
8 sum(sales.sales_amount) sales_amount
9 from sales, time_hierarchy, customer_hierarchy
10 where sales.trans_date = time_hierarchy.day
11 and sales.cust_id = customer_hierarchy.cust_id
12 group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy
13 /
Materialized view created.
set autotrace traceonly
select customer_hierarchy.zip_code,
2 time_hierarchy.mmyyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy
8 /
1216 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=409 Bytes=20450)
1 0 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409 Bytes=20450)
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
116 consistent gets
5 physical reads
可以看到如果按不同邮编、不同月度来统计查询的话，优化器将会查询物化视图中的查询方案，性能也是比较可观的。假如我们查不同地区年度的统计销量信息，结果又会是怎样？
select customer_hierarchy.region,
2 time_hierarchy.yyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.region, time_hierarchy.yyyy
8 /
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1681 Card=9 Bytes=261)
1 0 SORT (GROUP BY) (Cost=1681 Card=9 Bytes=261)
2 1 NESTED LOOPS (Cost=35 Card=426672 Bytes=12373488)
3 2 NESTED LOOPS (Cost=35 Card=426672 Bytes=8106768)
4 3 TABLE ACCESS (FULL) OF 'SALES' (Cost=35 Card=426672
5 3 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE)
6 2 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_7828' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
428047 consistent gets
745 physical reads
可以看到查询性能大有影响。接下我们同样创建dimension sales_dimension，用于说明客户代码和邮编、地区间的关系：
drop dimension time_hierarchy_dim
2 /
Dimension dropped.
create dimension sales_dimension
2 level cust_id is customer_hierarchy.cust_id
3 level zip_code is customer_hierarchy.zip_code
4 level region is customer_hierarchy.region
5 level day is time_hierarchy.day
6 level mmyyyy is time_hierarchy.mmyyyy
7 level qtr_yyyy is time_hierarchy.qtr_yyyy
8 level yyyy is time_hierarchy.yyyy
9 hierarchy cust_rollup
10 (
11 cust_id child of
12 zip_code child of
13 region
14 )
15 hierarchy time_rollup
16 (
17 day child of
18 mmyyyy child of
19 qtr_yyyy child of
20 yyyy
21 )
22 attribute mmyyyy
23 determines mon_yyyy;
Dimension created.
再回到原来的查询，我们可以看到查询性能有了大幅的提升：
set autotrace on
select customer_hierarchy.region,
2 time_hierarchy.yyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.region, time_hierarchy.yyyy
8 /
REGION YYYY SALES_AMOUNT
---------- ---------- ------------
0 2006 7.3144E+11
0 2007 4484956329
1 2006 7.8448E+11
2 2006 7.7257E+11
2 2007 4684418980
3 2006 7.7088E+11
4 2006 7.8004E+11
4 2007 3127953246
5 2006 7.3273E+11
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=9 Bytes=576)
1 0 SORT (GROUP BY) (Cost=15 Card=9 Bytes=576)
2 1 HASH JOIN (Cost=10 Card=598 Bytes=38272)
3 2 VIEW (Cost=3 Card=100 Bytes=700)
4 3 SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)
5 4 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE)
6 2 HASH JOIN (Cost=7 Card=598 Bytes=34086)
7 6 VIEW (Cost=4 Card=19 Bytes=133)
8 7 SORT (UNIQUE) (Cost=4 Card=19 Bytes=133)
9 8 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828'
10 6 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409
Statistics
----------------------------------------------------------
364 recursive calls
0 db block gets
88 consistent gets
0 physical reads
set autot trace
select customer_hierarchy.region,
2 time_hierarchy.qtr_yyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.region, time_hierarchy.qtr_yyyy;
27 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=22 Bytes=154
1 0 SORT (GROUP BY) (Cost=23 Card=22 Bytes=1540)
2 1 HASH JOIN (Cost=11 Card=1447 Bytes=101290)
3 2 VIEW (Cost=3 Card=100 Bytes=700)
4 3 SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)
5 4 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE) (
6 2 HASH JOIN (Cost=7 Card=1447 Bytes=91161)
7 6 VIEW (Cost=4 Card=46 Bytes=598)
8 7 SORT (UNIQUE) (Cost=4 Card=46 Bytes=598)
9 8 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828' (UN
10 6 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409 B
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
19 consistent gets
0 physical reads
select customer_hierarchy.region,
2 time_hierarchy.mon_yyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.region, time_hierarchy.mon_yyyy;
75 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=56 Bytes=386
1 0 SORT (GROUP BY) (Cost=41 Card=56 Bytes=3864)
2 1 HASH JOIN (Cost=11 Card=3775 Bytes=260475)
3 2 VIEW (Cost=4 Card=120 Bytes=1440)
4 3 SORT (UNIQUE) (Cost=4 Card=120 Bytes=1440)
5 4 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828' (UNIQ
6 2 HASH JOIN (Cost=6 Card=409 Bytes=23313)
7 6 VIEW (Cost=3 Card=100 Bytes=700)
8 7 SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)
9 8 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE)
10 6 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409 B
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
参考：Tomates Kyte 《Expert One-on-One Oracle》
在数据仓库环境中，我们通常利用物化视图强大的查询重写功能来提升统计查询的性能，但是物化视图的查询重写功能有时候无法智能地判断查询中一些相关联的条件，以至于影响性能。比如我们有一张销售表sales，用于存储订单的详细信息，包含交易日期、顾客编号和销售量。我们创建一张物化视图，按月存储累计销量信息，假如这时候我们要查询按季度或者按年度统计销量信息，Oracle是否能够智能地转换查询重写呢？我们知道交易日期中的日期意味着月，月意味着所处的季度，季度意味着年度，但是Oracle却是无法智能地判断这其中的关系，因此无法利用物化视图查询重写来返回我们季度或年度的销量信息，而是直接查询基表，导致性能产生问题。
这时候Dimension就派上用场了。Dimension用于说明列之间的父子对应关系，以使优化器能够自动转换不同列的关系，利用物化视图的查询功能来提升查询统计性能。下面我们首先创建一张销售交易表sales，包含交易日期、顾客编号和销售量这几个列，用于保存销售订单信息，整个表有42万多条记录；创建另一张表time_hierarchy用于存储交易日期中时间的关系，包含交易日期及其对应的月、季度及年度等信息，然后我们将体验Dimension的强大功能。
--建表
create table sales
(trans_date date, cust_id int, sales_amount number );
Table created.
--插入数据
insert /*+ APPEND */ into sales
select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,
mod(rownum,100) CUST_ID,
abs(dbms_random.random)/100 SALES_AMOUNT
from all_objects;
5926 rows created.
commit;
Commit complete.
begin
for i in 1 .. 6
loop
insert /*+ APPEND */ into sales
select trans_date, cust_id, abs(dbms_random.random)/100 SALES_AMOUNT
from sales;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
select count(*) from sales;
COUNT(*)
----------
426672
创建索引组织表time_hierarchy，里面生成了交易日期中日期DAY、月MMYYYY、季度QTY_YYYY、年度YYYY的关系。
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
/
Table created.
接下我们创建一张物化视图mv_sales，用于存储每个客户对应每个月的销量统计信息。
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
/
Materialized view created.
我们对基表进行分析，以使优化器能够物化视图的查询重写功能：
analyze table sales compute statistics;
Table analyzed.
analyze table time_hierarchy compute statistics;
Table analyzed.
设置会话的查询重写功能：
alter session set query_rewrite_enabled=true;
Session altered.
alter session set query_rewrite_integrity=trusted;
Session altered.
接下来我们按月统计总的销量：
select time_hierarchy.mmyyyy, sum(sales_amount)
from sales, time_hierarchy
where sales.trans_date = time_hierarchy.day
group by time_hierarchy.mmyyyy
/
MMYYYY SUM(SALES_AMOUNT)
---------- -----------------
12006 4.0574E+11
12007 1.2297E+10
22006 3.6875E+11
32006 3.9507E+11
42006 3.7621E+11
52006 3.8549E+11
62006 3.6641E+11
72006 3.8110E+11
82006 3.8502E+11
92006 3.7278E+11
102006 3.7983E+11
112006 3.7210E+11
122006 3.8364E+11
13 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=327 Bytes=8502)
1 0 SORT (GROUP BY) (Cost=4 Card=327 Bytes=8502)
2 1 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=327 Bytes=8502)
Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
25 consistent gets
4 physical reads
我们可以看到查询使用了查询重写的功能，直接查询物化视图中的查询方案，而不是查询其表，逻辑IO只有25个，性能相当良好。
假如这时候我们要按季度来查询统计销量信息，结果又会是怎样呢？
select time_hierarchy.qtr_yyyy, sum(sales_amount)
2 from sales, time_hierarchy
3 where sales.trans_date = time_hierarchy.day
4 group by time_hierarchy.qtr_yyyy
5 /
QTR_YYYY SUM(SALES_AMOUNT)
------------------------------------------------ -----------------
Q1 FY2006 1.1696E+12
Q1 FY2007 1.2297E+10
Q2 FY2006 1.1281E+12
Q3 FY2006 1.1389E+12
Q4 FY2006 1.1356E+12
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1681 Card=5 Bytes=145)
1 0 SORT (GROUP BY) (Cost=1681 Card=5 Bytes=145)
2 1 NESTED LOOPS (Cost=35 Card=426672 Bytes=12373488)
3 2 TABLE ACCESS (FULL) OF 'SALES' (Cost=35 Card=426672
4 2 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_7828' (UNIQUE)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
428048 consistent gets
599 physical reads
可以看到查询将直接查询基表产生了将近428048个逻辑IO，性能无法满足需求。
接下我们创建一个Dimension表time_hierarchy_dim，用于提醒优化器time_hierarchy表中的DAY列暗示着MMYYYY，MMYYYY又意味着QTY_YYYY，QTY_YYYY又意味着YYYY。然后我们将重新运行上面那个查询，看执行计划发生了怎样的变更。
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;
Dimension created.
select time_hierarchy.qtr_yyyy, sum(sales_amount)
2 from sales, time_hierarchy
3 where sales.trans_date = time_hierarchy.day
4 group by time_hierarchy.qtr_yyyy
5 /
QTR_YYYY SUM(SALES_AMOUNT)
------------------------------------------------ -----------------
Q1 FY2006 1.1696E+12
Q1 FY2007 1.2297E+10
Q2 FY2006 1.1281E+12
Q3 FY2006 1.1389E+12
Q4 FY2006 1.1356E+12
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=5 Bytes=195)
1 0 SORT (GROUP BY) (Cost=14 Card=5 Bytes=195)
2 1 HASH JOIN (Cost=7 Card=1157 Bytes=45123)
3 2 VIEW (Cost=4 Card=46 Bytes=598)
4 3 SORT (UNIQUE) (Cost=4 Card=46 Bytes=598)
5 4 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828' (UNIQUE)
6 2 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=327
Statistics
----------------------------------------------------------
193 recursive calls
0 db block gets
49 consistent gets
2 physical reads
可以看到创建Dimension后，Oracle已经能够智能地理解交易日期中月度和季度的转换关系，查询使用到物化视图，逻辑IO由原来的428048个减少到49个，性能有了大幅的提升。
同样我们再来统计一下年度的销量信息：
select time_hierarchy.yyyy, sum(sales_amount)
2 from sales, time_hierarchy
3 where sales.trans_date = time_hierarchy.day
4 group by time_hierarchy.yyyy
5 /
YYYY SUM(SALES_AMOUNT)
---------- -----------------
2006 4.5721E+12
2007 1.2297E+10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=2 Bytes=66)
1 0 SORT (GROUP BY) (Cost=10 Card=2 Bytes=66)
2 1 HASH JOIN (Cost=7 Card=478 Bytes=15774)
我们再创建一张customer_hierarchy表，用于存储客户代码、邮政编码和地区的关系，然后我们将按不同邮编或地区来查询各自的月度、季度或者年度销量信息。
create table customer_hierarchy
2 ( cust_id primary key, zip_code, region )
3 organization index
4 as
5 select cust_id,
6 mod( rownum, 6 ) || to_char(mod( rownum, 1000 ), 'fm0000') zip_code,
7 mod( rownum, 6 ) region
8 from ( select distinct cust_id from sales)
9 /
Table created.
analyze table customer_hierarchy compute statistics;
Table analyzed.
改写物化视图，查询方案中添加按不同邮编的月度统计销量。
drop materialized view mv_sales;
Materialized view dropped.
create materialized view mv_sales
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select customer_hierarchy.zip_code,
7 time_hierarchy.mmyyyy,
8 sum(sales.sales_amount) sales_amount
9 from sales, time_hierarchy, customer_hierarchy
10 where sales.trans_date = time_hierarchy.day
11 and sales.cust_id = customer_hierarchy.cust_id
12 group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy
13 /
Materialized view created.
set autotrace traceonly
select customer_hierarchy.zip_code,
2 time_hierarchy.mmyyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.zip_code, time_hierarchy.mmyyyy
8 /
1216 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=409 Bytes=20450)
1 0 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409 Bytes=20450)
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
116 consistent gets
5 physical reads
可以看到如果按不同邮编、不同月度来统计查询的话，优化器将会查询物化视图中的查询方案，性能也是比较可观的。假如我们查不同地区年度的统计销量信息，结果又会是怎样？
select customer_hierarchy.region,
2 time_hierarchy.yyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.region, time_hierarchy.yyyy
8 /
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1681 Card=9 Bytes=261)
1 0 SORT (GROUP BY) (Cost=1681 Card=9 Bytes=261)
2 1 NESTED LOOPS (Cost=35 Card=426672 Bytes=12373488)
3 2 NESTED LOOPS (Cost=35 Card=426672 Bytes=8106768)
4 3 TABLE ACCESS (FULL) OF 'SALES' (Cost=35 Card=426672
5 3 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE)
6 2 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_7828' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
428047 consistent gets
745 physical reads
可以看到查询性能大有影响。接下我们同样创建dimension sales_dimension，用于说明客户代码和邮编、地区间的关系：
drop dimension time_hierarchy_dim
2 /
Dimension dropped.
create dimension sales_dimension
2 level cust_id is customer_hierarchy.cust_id
3 level zip_code is customer_hierarchy.zip_code
4 level region is customer_hierarchy.region
5 level day is time_hierarchy.day
6 level mmyyyy is time_hierarchy.mmyyyy
7 level qtr_yyyy is time_hierarchy.qtr_yyyy
8 level yyyy is time_hierarchy.yyyy
9 hierarchy cust_rollup
10 (
11 cust_id child of
12 zip_code child of
13 region
14 )
15 hierarchy time_rollup
16 (
17 day child of
18 mmyyyy child of
19 qtr_yyyy child of
20 yyyy
21 )
22 attribute mmyyyy
23 determines mon_yyyy;
Dimension created.
再回到原来的查询，我们可以看到查询性能有了大幅的提升：
set autotrace on
select customer_hierarchy.region,
2 time_hierarchy.yyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.region, time_hierarchy.yyyy
8 /
REGION YYYY SALES_AMOUNT
---------- ---------- ------------
0 2006 7.3144E+11
0 2007 4484956329
1 2006 7.8448E+11
2 2006 7.7257E+11
2 2007 4684418980
3 2006 7.7088E+11
4 2006 7.8004E+11
4 2007 3127953246
5 2006 7.3273E+11
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=9 Bytes=576)
1 0 SORT (GROUP BY) (Cost=15 Card=9 Bytes=576)
2 1 HASH JOIN (Cost=10 Card=598 Bytes=38272)
3 2 VIEW (Cost=3 Card=100 Bytes=700)
4 3 SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)
5 4 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE)
6 2 HASH JOIN (Cost=7 Card=598 Bytes=34086)
7 6 VIEW (Cost=4 Card=19 Bytes=133)
8 7 SORT (UNIQUE) (Cost=4 Card=19 Bytes=133)
9 8 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828'
10 6 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409
Statistics
----------------------------------------------------------
364 recursive calls
0 db block gets
88 consistent gets
0 physical reads
set autot trace
select customer_hierarchy.region,
2 time_hierarchy.qtr_yyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.region, time_hierarchy.qtr_yyyy;
27 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=22 Bytes=154
1 0 SORT (GROUP BY) (Cost=23 Card=22 Bytes=1540)
2 1 HASH JOIN (Cost=11 Card=1447 Bytes=101290)
3 2 VIEW (Cost=3 Card=100 Bytes=700)
4 3 SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)
5 4 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE) (
6 2 HASH JOIN (Cost=7 Card=1447 Bytes=91161)
7 6 VIEW (Cost=4 Card=46 Bytes=598)
8 7 SORT (UNIQUE) (Cost=4 Card=46 Bytes=598)
9 8 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828' (UN
10 6 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409 B
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
19 consistent gets
0 physical reads
select customer_hierarchy.region,
2 time_hierarchy.mon_yyyy,
3 sum(sales.sales_amount) sales_amount
4 from sales, time_hierarchy, customer_hierarchy
5 where sales.trans_date = time_hierarchy.day
6 and sales.cust_id = customer_hierarchy.cust_id
7 group by customer_hierarchy.region, time_hierarchy.mon_yyyy;
75 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=56 Bytes=386
1 0 SORT (GROUP BY) (Cost=41 Card=56 Bytes=3864)
2 1 HASH JOIN (Cost=11 Card=3775 Bytes=260475)
3 2 VIEW (Cost=4 Card=120 Bytes=1440)
4 3 SORT (UNIQUE) (Cost=4 Card=120 Bytes=1440)
5 4 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_7828' (UNIQ
6 2 HASH JOIN (Cost=6 Card=409 Bytes=23313)
7 6 VIEW (Cost=3 Card=100 Bytes=700)
8 7 SORT (UNIQUE) (Cost=3 Card=100 Bytes=700)
9 8 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_7833' (UNIQUE)
10 6 TABLE ACCESS (FULL) OF 'MV_SALES' (Cost=2 Card=409 B
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
参考：Tomates Kyte 《Expert One-on-One Oracle》
分享到：
发表评论
-
Oracle连接故障的排除
2024-09-09 22:33 634Oracle版本为11G，操作系统为Windows Ser ... -
Oracle数据库相关系统突然提示“SQLException:违反协议”
2024-02-19 15:50 5113SQLException:违反协议这个异常可能由很多的 ... -
CentOS在Docker中安装Oracle
2024-02-06 12:13 12671.拉取Oracle镜像，并检 ... -
Windows Server安装oracle数据库一直停在82%
2023-02-04 12:01 619网上有个说法：服务器超过一定数量的CPU后，将不能正常安装 ... -
ORA-04030错误处理
2023-02-04 11:52 2698【错误描述】 错误信息如下： ORA-04030：在尝 ... -
ORA-04030错误处理
2023-02-04 11:45 403【错误描述】 错误信息如下： ORA-04030：在尝 ... -
Linux安装MySQL数据库
2019-06-10 22:27 18211.进入安装包所在目录，解压： tar zxvf mysql- ... -
确定MySQL在Linux系统中配置文件的位置
2019-04-14 19:30 27861.通过which mysql命令来查看mysql的安装位置。 ... -
mysql set names 命令和 mysql 字符编码问题
2019-04-12 00:34 1164转自：https://www.cnblogs.com/digd ... -
MYSQL中取当前周/月/季/年的第一天与最后一天
2018-11-17 23:16 2221转自：https://blog.csdn.net/ ... -
Oracle删除大量数据的实践
2016-11-07 18:03 5840一、引言 从来没有 ... -
Oracle 数据库简明教程 V0.1
2016-03-23 21:01 2071供初学者入门学习使用，以开发者常见、常用的知识为主，基本上 ... -
Oracle拆分字符串函数
2016-03-23 10:58 3372create or replace type string ... -
Oracle数据库远程连接无响应
2016-03-21 10:20 4329故障现象： 服务器本机使用sqlplus / as s ... -
Oracle PGA详解
2015-10-21 15:34 11494转自：http://yanguz123.iteye.com/b ... -
Oracle12C导入dmp数据
2015-10-08 23:43 20557Oracle12C，发生了较大的变化。以前熟悉的东西变得陌 ... -
SQLLDR数据导入小结
2015-07-25 22:06 75451.创建数据表 CREATE TABLE ... -
Window7安装Oracle10
2015-03-06 12:14 1627每次安装都要百度，转到自己的博客上，找起来方便，还能增加访 ... -
Oracle SQL Developer 连接 Mysql 数据库
2015-02-25 19:36 3689下载JDBC包，解压缩这里只要mysql-connector- ... -
Mysql数据备份与恢复
2015-02-25 19:15 1369备份/恢复策略 1. 要定期做 mysql备份，并考虑系统可以 ...
相关推荐
- **维 (DIMENSION)**：在OLAP环境中定义维度属性。 - **Java对象**：支持在数据库中存储和执行Java代码。 - **实体视图 (Materialized View)**：预先计算的结果集，用于提高查询性能。 - **概要 (OUTLINE)**：...
- **Dimension table（维度表）**：存储对事实表某些属性的描述信息。 9. **Fact table上需要建立的索引**： - 位图索引（bitmap index）。 10. **相关约束**： - **主键**：确保列值唯一且非空。 - **外键**...
- **维度表（Dimension Table）**：提供对事实表的描述性信息。 10. **位图索引**：在事实表上常用于处理高基数的分类列，节省空间但不适合频繁更新。 11. **相关约束**： - **主键**：唯一标识表中每一行的非空...
9. **星型架构中的表**：事实表（Fact Table）包含业务的核心数据，维度表（Dimension Table）存储对事实表的描述性信息。 10. **事实表上的索引**：位图索引适合于高基数低选择性的列，能有效减少I/O，提高聚合...
- **Dimension Table**：存储描述性信息，如日期、产品、地区等。 10. **Fact Table上的索引**：通常使用位图索引，适用于低基数（非唯一值）的列。 11. **相关约束**： - **主键**：确保表中记录的唯一性。 - ...
Oracle 11g提供了数据仓库建模工具和OLAP（在线分析处理）功能，如星型和雪花型模型，以及Cube和Dimension的概念，为数据分析提供强大支持。 总之，Oracle 11g数据库高级管理员不仅需要熟悉数据库的基本操作，还要...
9. **STAR SCHEMA**：包括Fact Tables（包含业务事实数据）和Dimension Tables（包含描述性数据，如时间、地点等维度信息）。 10. **Fact Table索引**：在Fact Table上创建位图索引可以优化多对多关系的查询效率。 ...
9. **STAR SCHEMA中的表类型**：事实表（Fact table）包含业务度量数据，维度表（Dimension table）存储描述性信息，支持事实表。 10. **FACT Table的索引**：在事实表上通常建立位图索引，减少存储空间占用，适用...
在数据库设计中，尤其是星型模式（Star Schema）中，存在两种类型的表：事实表（Fact Tables）和维度表（Dimension Tables）。事实表通常包含大量的测量值或度量标准，而维度表则包含了描述事实表中记录的维度属性...
Fact tables 包含大量的主要信息，dimension tables 存放对 fact table 某些属性描述的信息。 10. 在 FACT Table 上建立何种索引： 可以在 FACT Table 上建立位图索引 (bitmap index)。 11. 两种相关约束： 主键和...
1. 维（Dimension）：是人们观察数据的特定角度，是考虑问题时的一类属性，属性集合构成一个维（时间维、地理维等）。 2. 维的层（Hierarchy）：人们观察数据的某个特定角度（即某个维）还可以存在细节程度不同的...
本主题聚焦于如何使用C#来读取Excel数据，并将其存储到Oracle 11g数据库中。这涉及到两个主要的技术领域：处理Excel文件和与Oracle数据库的交互。 首先，让我们详细了解一下如何在C#中读取Excel数据。通常，我们...
- **Dimension Table**：描述Fact Table中数据的附加信息，如日期维度、产品维度等。 **10. FACT Table上使用的索引类型** - **位图索引**：适用于低基数字段，如分类标识符。 #### 六、表约束 **11. 相关约束**...
Hyperion Planning还定义了稠密维度(Dense Dimension)，这是指在信息交叉引用中使用比率较高的维度，这类维度通常包含较高百分比的维度交叉点，其中包含数据。稠密维度对于处理和分析大量数据非常有用。Hyperion ...
星型模型是一种常见的数据仓库设计模式，它由事实表（Fact Table）和维度表（Dimension Table）组成。事实表包含了业务活动的核心度量值，而维度表则包含了描述这些活动的详细信息。 - **事实表**：存储业务交易...
Oracle面试题常见的问题 Oracle面试题是一个非常重要的知识点，涵盖了Oracle数据库管理系统的各个方面。下面我们将逐一解释每个问题和答案： 1. 解释冷备份和热备份的不同点以及各自的优点 冷备份和热备份是两种...
在Oracle数据库领域，DBA（数据库管理员）的职责包括设计、实施、维护和优化数据库系统。以下是Oracle数据库DBA面试中可能遇到的知识点： 1. 数据库备份类型及优缺点 热备份是在数据库处于归档模式下，并且数据库在...
在Oracle数据库中，行列转换是一项常用且强大的功能，它允许数据在不同的维度上进行转换，以便于数据分析和报告。本文将深入探讨Oracle中实现行列转换的几种方法，包括使用`UNION ALL`、`MODEL`子句以及集合类型(`...
- **Oracle 7.1.6 (1994)**：首次引入 **MultiDimension**，这标志着Oracle开始涉足空间数据领域。 - **Oracle 7.3.3 (1995)**：引入 **Spatial Data Option**，进一步增强了空间数据处理能力。 - **Oracle 8i (1997...