2006-02-28 16:33
我们再创建一张customer_hierarchy表,用于存储客户代码、邮政编码和地区的关系,然后我们将按不同邮编或地区来查询各自的月度、季度或者年度销量信息。
Roby@XUE> 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.
Roby@XUE> analyze table customer_hierarchy compute statistics;
Table analyzed.
改写物化视图,查询方案中添加按不同邮编的月度统计销量。
Roby@XUE> drop materialized view mv_sales;
Materialized view dropped.
Roby@XUE> 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.
Roby@XUE> set autotrace traceonly
Roby@XUE> 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
可以看到如果按不同邮编、不同月度来统计查询的话,优化器将会查询物化视图中的查询方案,性能也是比较可观的。假如我们查不同地区年度的统计销量信息,结果又会是怎样?
Roby@XUE> 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,用于说明客户代码和邮编、地区间的关系:
Roby@XUE> drop dimension time_hierarchy_dim
2
/
Dimension dropped.
Roby@XUE> 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.
再回到原来的查询,我们可以看到查询性能有了大幅的提升:
Roby@XUE> set autotrace on
Roby@XUE> 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
Roby@XUE> set autot trace
Roby@XUE> 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
Roby@XUE> 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》
--End--
分享到:
相关推荐
linux下 oracle程序 自动安装、配置。 linux下 oracle程序 自动安装、配置。
通常linux下用安装oracle后,只有切换至oracle用户下方可执行相关的数据库的sqlplus及imp\exp相关命令的操作,经过这里的此配置后方可解决此问题,所有用户下均可以执行上述相关操作
win7下安装oracle win7下安装oracle win7下安装oracle
在linux下手动创建oracle10g数据库
Oracle 9i Client (Oracle 9i 客户端) 简化版 (不安装Oracle客户端,也可以使用PLSQL Developer 不用安装Oracle客户端也可以使用PLSQL Developer 绿色! 安全! 轻便! 可靠! 1、本软件可作为简单的Oracle9i客户端...
Linux下彻底删除11g oracle,简单方便,快速,命令删除。
c# 导出oracle数据库下所有表结构 c# 导出oracle数据库下所有表结构
oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件
Linux下ORACLE安装说明.docx Linux下ORACLE安装说明.docx Linux下ORACLE安装说明.docx Linux下ORACLE安装说明.docx Linux下ORACLE安装说明.docx Linux下ORACLE安装说明.docx Linux下ORACLE安装说明.docx Linux下...
OracleLinux6.5下安装Oracle11gRAC_Multipath_ASM20140606发行版
删除oracle中用户下所有的表,不是系统用户哦!不然会删掉系统的数据表
linux suse下安装oracle linux suse下安装oracle
Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典...
oracle 9i所有版本最新下载链接 直接迅雷下载 Oracle9i Database Release 2 Enterprise/Standard/Personal Edition for Windows NT/2000/XP ...
1:Windows Server 2019 下Oracle 19c安装 2:NIC网络聚合配置说明 3:Oracle 19c Clent数据源连接详解(解决部分情况下客户端不能连接数据库问题)
Windows 下 oracle 服务自动启动设置
解决linux下oracle中文乱码问题,添加中文支持解决linux下oracle中文乱码问题,添加中文支持解决linux下oracle中文乱码问题,添加中文支持解决linux下oracle中文乱码问题,添加中文支持解决linux下oracle中文乱码...
此版本集成了Oracle 11g 11.2.0.1.0中的实用工具tnsping、exp/imp,支持 Object Browser、TOAD等 Oracle 管理工具,并在Windows XP、Windows Vista、Windows 7平台下测试通过。 注意: 1. 请勿使用高版本的exp/imp...
使用了,ODP.NET 方式链接数据库,只要把Oracle.ManagedDataAccess.dll引入取代以前的Oracle.DataAccess.dll即可。 这种方式也是oracle公司提供的,稳定性要比之前那种更好,而且也是免安装客户端的,目前还没测试...
下载后,只需要将Oracle压缩文件解压,建议放在D:\Oracle目录下,运行OracleSetup32.exe,点击确定按钮就配置好了,适用于Oracle客户端的快速部署。 可以在network\admin目录下配置tnsnames.ora,使用服务名访问...