- 浏览: 5103691 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
silence19841230:
先拿走看看
SpringBoot2.0开发WebSocket应用完整示例 -
wallimn:
masuweng 写道发下源码下载地址吧!三个相关文件打了个包 ...
SpringBoot2.0开发WebSocket应用完整示例 -
masuweng:
发下源码下载地址吧!
SpringBoot2.0开发WebSocket应用完整示例 -
masuweng:
SpringBoot2.0开发WebSocket应用完整示例 -
wallimn:
水淼火 写道你好,我使用以后,图标不显示,应该怎么引用呢,谢谢 ...
前端框架iviewui使用示例之菜单+多Tab页布局
物化视图的一个重要的特性就是支持查询重写。
如果初始化参数query_rewrite_enabled 设置为TRUE,且使用CBO时,当发出对基表的查询,Oracle会自动判断是否能利用这个基表的所有指定了ENABLE QUERY REWRITE语句的物化视图,如果可以且根据统计信息判断通过查询物化视图代价更小,则Oracle自动重写查询语句,通过查询物化视图得到正确的结 果。
不过不是所有的物化视图都支持查询重写的,查询重写有一些限制条件,本文首先讨论查询重写对物化视图的限制。
物化视图的查询重写功能的限制来自两个方面,对物化视图的限制和对查询重写的限制。
物化视图的限制:
1.物化视图中不能包括ROWNUM、SYSDATE等不可重复的表达式;
2.物化视图不能包括LONG或LONG RAW或对象参考列;
3.如果物化视图的查询定义中包括集合操作(如minus、union等),则物化视图只支持全文匹配的查询重写;
4.如果以PREBUILD方式建立物化视图,除非指定了WITH REDUCED PRECISION语句,否则列的精度必须满足SELECT表达式的精度;
5.如果物化视图包含一个表的次数多于一次,那么,这个物化视图支持一般类型的查询重写,要求查询中的表的别名必须和物化视图中定义的一致。
首先建立测试环境:
SQL> create table dim_a (id number primary key, name varchar2(30));
表已创建。
SQL> create table dim_b (id number primary key, name varchar2(30));
表已创建。
SQL> create table fact (id number, aid number, bid number, num number);
表已创建。
SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id);
表已更改。
SQL> alter table fact add constraint fk_fact_bid foreign key (bid) references dim_b(id);
表已更改。
SQL> insert into dim_a select rownum, 'a'||rownum from user_objects;
已创建56行。
SQL> insert into dim_b select rownum, 'b'||rownum from user_objects;
已创建56行。
SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2
2 from user_objects;
已创建56行。
SQL> commit;
提交完成。
建立MV_CAPABILITIES_TABLE表:
SQL> @?/rdbms/admin/utlxmv.sql
表已创建。
环境:
SQL> show parameter query_rewrite
NAME TYPE VALUE
------------------------------------ ----------- -------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
物化视图的限制:
1.物化视图中不能包括ROWNUM、SYSDATE等不可重复的表达式;
SQL> create materialized view mv_fact enable query rewrite as
2 select rownum id, a.name, b.num from dim_a a, fact b
3 where a.id = b.aid;
where a.id = b.aid
*
ERROR 位于第 3 行:
ORA-30353: 表达式对查询重写不支持
SQL> begin
2 dbms_mview.explain_mview('select rownum id, a.name, b.num from dim_a a, fact b
3 where a.id = b.aid');
4 end;
5 /
PL/SQL 过程已成功完成。
SQL> col msgtxt format a40
SQL> col related_text format a30
SQL> select capability_name, possible, msgtxt, related_text
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT RELATED_TEXT
------------------------------ - ---------------------------------------- --------------
REWRITE N
REWRITE_FULL_TEXT_MATCH N Oracle 错误: 有关详细信息, 请参阅 RELATE 表达式对查询重写不支持
D_NUM 和 RELATED_TEXT
REWRITE_PARTIAL_TEXT_MATCH N 实体化视图无法支持任何类型的查询重写
REWRITE_GENERAL N 实体化视图无法支持任何类型的查询重写
2.物化视图不能包括LONG或LONG RAW或对象参考列;
SQL> alter table dim_b add (col_long long);
表已更改。
SQL> create materialized view mv_fact as
2 select b.col_long, a.num from dim_b b, fact a
3 where b.id = a.bid;
select b.col_long, a.num from dim_b b, fact a
*
ERROR 位于第 2 行:
ORA-00997: 非法使用 LONG 数据类型
SQL> alter table dim_b drop (col_long);
表已更改。
3.如果物化视图的查询定义中包括集合操作(如minus、union等),则物化视图只支持全文匹配的查询重写;
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name, b.num from dim_a a, fact b
3 where a.id = b.aid
4 union
5 select a.name, b.num from dim_b a, fact b
6 where a.id = b.bid
7 ;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_FACT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 设置在实体化视图中遇到的运算符
REWRITE_GENERAL N 设置在实体化视图中遇到的运算符
根据MV_CAPABILITIES_TABLE中的信息可以看出,物化视图MV_FACT只支持全文匹配的查询。
4.如果以PREBUILD方式建立物化视图,除非指定了WITH REDUCED PRECISION语句,否则列的精度必须满足SELECT表达式的精度;
SQL> drop materialized view mv_fact;
实体化视图已删除。
SQL> create table mv_fact (name varchar2(20), num number);
表已创建。
SQL> create table mv_fact1 (name varchar2(20), num number);
表已创建。
SQL> create materialized view mv_fact on prebuilt table enable query rewrite as
2 select a.name, b.num from dim_a a, fact b
3 where a.id = b.aid;
select a.name, b.num from dim_a a, fact b
*
ERROR 位于第 2 行:
ORA-12060: 预建表的形式与定义查询不匹配
SQL> create materialized view mv_fact1 on prebuilt table with reduced precision
2 enable query rewrite as
3 select a.name, b.num from dim_a a, fact b
4 where a.id = b.aid;
实体化视图已创建。
5.如果物化视图包含一个表的次数多于一次,那么,这个物化视图支持一般类型的查询重写,要求查询中的表的别名必须和物化视图中定义的一致。
SQL> create materialized view mv_fact1 on prebuilt table with reduced precision 2 enable query rewrite as
3 select a.name, b.num from dim_a a, fact b
4 where a.id = b.aid;
实体化视图已创建。
SQL>
SQL> create materialized view mv_dim_a enable query rewrite
2 as select a.id, b.name from dim_a a, dim_a b
3 where a.id = b.id;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_DIM_A');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - ----------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 相同的表或视图的多个例程
REWRITE_GENERAL Y
SQL> set autot on exp
SQL> select a.id, b.name from dim_a a, dim_a b
2 where a.id = b.id;
ID NAME
---------- ------------------------------
1 a1
2 a2
3 a3
.
.
.
56 a56
已选择56行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'DIM_A'
3 1 INDEX (UNIQUE SCAN) OF 'SYS_C001917' (UNIQUE)
由于没有统计信息采用了RBO,下面对表进行分析。
SQL> exec dbms_stats.gather_table_stats(user, 'DIM_A');
PL/SQL 过程已成功完成。
SQL> select a.id, b.name from dim_a a, dim_a b
2 where a.id = b.id;
ID NAME
---------- ------------------------------
1 a1
2 a2
3 a3
.
.
.
56 a56
已选择56行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=327 Bytes=9810)
1 0 TABLE ACCESS (FULL) OF 'MV_DIM_A' (Cost=2 Card=327 Bytes=9810)
SQL> select a.id, c.name from dim_a a, dim_a c
2 where a.id = c.id;
ID NAME
---------- ------------------------------
1 a1
2 a2
3 a3
.
.
.
56 a56
已选择56行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=56 Bytes=504)
1 0 NESTED LOOPS (Cost=2 Card=56 Bytes=504)
2 1 TABLE ACCESS (FULL) OF 'DIM_A' (Cost=2 Card=56 Bytes=336)
3 1 INDEX (UNIQUE SCAN) OF 'SYS_C001917' (UNIQUE)
可以看出,当物化视图中包含一个表多次时,查询重写的要求是查询语句中的别名必须和物化视图定义中的一致。
本文介绍的是一般意义上的限制,对于具体某个SQL能否使用查询重写,可以使用DBMS_MVIEW.EXPLAIN_REWRITE过程。
物化视图的查询首先功能的限制来自两个方面,对物化视图的限制和对查询重写的限制。
查询重写的限制包括:
1.如果物化视图包括本地表和远端表,则只有本地表支持查询重写;
2.基表和物化视图都不能处于SYS用户下;
3.物化视图的查询中如果包含SELECT和GROUP BY列表,则二者必须保持一致;
4.聚集函数必须在表达式的最外层;
5.不允许CONNECT BY语句。
1.如果物化视图包括本地表和远端表,则只有本地表支持查询重写;
SQL> conn yangtk/yangtk@yangtk
已连接。
SQL> create table dim_b (id number primary key, name varchar2(30));
表已创建。
SQL> insert into dim_b select rownum, 'b'||rownum from user_objects;
已创建60行。
SQL> commit;
提交完成。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> create database link yangtk using 'yangtk';
数据库链接已创建。
SQL> select count(*) from dim_b@yangtk;
COUNT(*)
----------
60
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name aname, b.name bname, f.num
3 from dim_a a, dim_b@yangtk b, fact f
4 where a.id = f.aid
5 and b.id = f.bid;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_FACT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------
REWRITE N
REWRITE_FULL_TEXT_MATCH N 实体化视图引用了 FROM 列表中的远程表或视图
REWRITE_PARTIAL_TEXT_MATCH N 实体化视图无法支持任何类型的查询重写
REWRITE_GENERAL N 实体化视图无法支持任何类型的查询重写
优化器无法使用远端数据库中的物化视图,如果在本地建立远端表的物化视图,则Oracle无法确定物化视图中的数据是否和基表中的数据同步,因此包含远端表的物化视图也不支持查询重写。
因此如果一个查询中包含了远端表和本地表,则只有对本地表的访问会被优化器考虑是否使用查询重写,远端表部分不会利用查询重写的功能。
2.基表和物化视图都不能处于SYS用户下;
SQL> conn /@test4 as sysdba
已连接。
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name, b.num from yangtk.dim_a a, yangtk.fact b
3 where a.id = b.aid;
select a.name, b.num from yangtk.dim_a a, yangtk.fact b
*
ERROR 位于第 2 行:
ORA-30359: SYS 具体化视图不支持查询重写
SQL> create table dim_a (id number primary key, name varchar2(30));
表已创建。
SQL> grant select on dim_a to yangtk;
授权成功。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name, b.num from sys.dim_a a, yangtk.fact b
3 where a.id = b.aid;
where a.id = b.aid
*
ERROR 位于第 3 行:
ORA-01031: 权限不足
SQL> conn /@test4 as sysdba
已连接。
SQL> create materialized view yangtk.mv_fact enable query rewrite as
2 select a.name, b.num from sys.dim_a a, yangtk.fact b
3 where a.id = b.aid;
where a.id = b.aid
*
ERROR 位于第 3 行:
ORA-30354: SYS 关系中不允许查询重写
根据简单的测试可以发现,基表和物化视图都不能包括在SYS用户中。
3.物化视图的查询中如果包含SELECT和GROUP BY列表,则二者必须保持一致;
SQL> create materialized view mv_dim_b enable query rewrite as
2 select count(*) from dim_b
3 group by name;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_DIM_B');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 在 SELECT 列表中忽略了分组列
REWRITE_GENERAL N 在 SELECT 列表中忽略了分组列
如果GROUP BY列表和SELECT列表不一致,则只支持全文匹配的查询重写。
4.聚集函数必须在表达式的最外层;
SQL> create materialized view mv_fact enable query rewrite as
2 select avg(avg(num)) from dim_a a, fact b
3 where a.id = b.aid
4 group by name;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_FACT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 在 SELECT 列表中忽略了分组列
REWRITE_GENERAL N 表达式中聚集函数嵌套
REWRITE_GENERAL N 在 SELECT 列表中忽略了分组列
5.不允许CONNECT BY语句。
SQL> create table test_connect (id number, father_id number, name varchar2(30));
表已创建。
SQL> create materialized view mv_test_connect enable query rewrite as
2 select id from test_connect
3 start with id = 1
4 connect by prior id = father_id;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_TEST_CONNECT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - -----------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL N 存在一个 CONNECT BY 子句
来源:http://blog.student163.com/?uid-8883-action-viewspace-itemid-14706
如果初始化参数query_rewrite_enabled 设置为TRUE,且使用CBO时,当发出对基表的查询,Oracle会自动判断是否能利用这个基表的所有指定了ENABLE QUERY REWRITE语句的物化视图,如果可以且根据统计信息判断通过查询物化视图代价更小,则Oracle自动重写查询语句,通过查询物化视图得到正确的结 果。
不过不是所有的物化视图都支持查询重写的,查询重写有一些限制条件,本文首先讨论查询重写对物化视图的限制。
物化视图的查询重写功能的限制来自两个方面,对物化视图的限制和对查询重写的限制。
物化视图的限制:
1.物化视图中不能包括ROWNUM、SYSDATE等不可重复的表达式;
2.物化视图不能包括LONG或LONG RAW或对象参考列;
3.如果物化视图的查询定义中包括集合操作(如minus、union等),则物化视图只支持全文匹配的查询重写;
4.如果以PREBUILD方式建立物化视图,除非指定了WITH REDUCED PRECISION语句,否则列的精度必须满足SELECT表达式的精度;
5.如果物化视图包含一个表的次数多于一次,那么,这个物化视图支持一般类型的查询重写,要求查询中的表的别名必须和物化视图中定义的一致。
首先建立测试环境:
SQL> create table dim_a (id number primary key, name varchar2(30));
表已创建。
SQL> create table dim_b (id number primary key, name varchar2(30));
表已创建。
SQL> create table fact (id number, aid number, bid number, num number);
表已创建。
SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id);
表已更改。
SQL> alter table fact add constraint fk_fact_bid foreign key (bid) references dim_b(id);
表已更改。
SQL> insert into dim_a select rownum, 'a'||rownum from user_objects;
已创建56行。
SQL> insert into dim_b select rownum, 'b'||rownum from user_objects;
已创建56行。
SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2
2 from user_objects;
已创建56行。
SQL> commit;
提交完成。
建立MV_CAPABILITIES_TABLE表:
SQL> @?/rdbms/admin/utlxmv.sql
表已创建。
环境:
SQL> show parameter query_rewrite
NAME TYPE VALUE
------------------------------------ ----------- -------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
物化视图的限制:
1.物化视图中不能包括ROWNUM、SYSDATE等不可重复的表达式;
SQL> create materialized view mv_fact enable query rewrite as
2 select rownum id, a.name, b.num from dim_a a, fact b
3 where a.id = b.aid;
where a.id = b.aid
*
ERROR 位于第 3 行:
ORA-30353: 表达式对查询重写不支持
SQL> begin
2 dbms_mview.explain_mview('select rownum id, a.name, b.num from dim_a a, fact b
3 where a.id = b.aid');
4 end;
5 /
PL/SQL 过程已成功完成。
SQL> col msgtxt format a40
SQL> col related_text format a30
SQL> select capability_name, possible, msgtxt, related_text
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT RELATED_TEXT
------------------------------ - ---------------------------------------- --------------
REWRITE N
REWRITE_FULL_TEXT_MATCH N Oracle 错误: 有关详细信息, 请参阅 RELATE 表达式对查询重写不支持
D_NUM 和 RELATED_TEXT
REWRITE_PARTIAL_TEXT_MATCH N 实体化视图无法支持任何类型的查询重写
REWRITE_GENERAL N 实体化视图无法支持任何类型的查询重写
2.物化视图不能包括LONG或LONG RAW或对象参考列;
SQL> alter table dim_b add (col_long long);
表已更改。
SQL> create materialized view mv_fact as
2 select b.col_long, a.num from dim_b b, fact a
3 where b.id = a.bid;
select b.col_long, a.num from dim_b b, fact a
*
ERROR 位于第 2 行:
ORA-00997: 非法使用 LONG 数据类型
SQL> alter table dim_b drop (col_long);
表已更改。
3.如果物化视图的查询定义中包括集合操作(如minus、union等),则物化视图只支持全文匹配的查询重写;
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name, b.num from dim_a a, fact b
3 where a.id = b.aid
4 union
5 select a.name, b.num from dim_b a, fact b
6 where a.id = b.bid
7 ;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_FACT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 设置在实体化视图中遇到的运算符
REWRITE_GENERAL N 设置在实体化视图中遇到的运算符
根据MV_CAPABILITIES_TABLE中的信息可以看出,物化视图MV_FACT只支持全文匹配的查询。
4.如果以PREBUILD方式建立物化视图,除非指定了WITH REDUCED PRECISION语句,否则列的精度必须满足SELECT表达式的精度;
SQL> drop materialized view mv_fact;
实体化视图已删除。
SQL> create table mv_fact (name varchar2(20), num number);
表已创建。
SQL> create table mv_fact1 (name varchar2(20), num number);
表已创建。
SQL> create materialized view mv_fact on prebuilt table enable query rewrite as
2 select a.name, b.num from dim_a a, fact b
3 where a.id = b.aid;
select a.name, b.num from dim_a a, fact b
*
ERROR 位于第 2 行:
ORA-12060: 预建表的形式与定义查询不匹配
SQL> create materialized view mv_fact1 on prebuilt table with reduced precision
2 enable query rewrite as
3 select a.name, b.num from dim_a a, fact b
4 where a.id = b.aid;
实体化视图已创建。
5.如果物化视图包含一个表的次数多于一次,那么,这个物化视图支持一般类型的查询重写,要求查询中的表的别名必须和物化视图中定义的一致。
SQL> create materialized view mv_fact1 on prebuilt table with reduced precision 2 enable query rewrite as
3 select a.name, b.num from dim_a a, fact b
4 where a.id = b.aid;
实体化视图已创建。
SQL>
SQL> create materialized view mv_dim_a enable query rewrite
2 as select a.id, b.name from dim_a a, dim_a b
3 where a.id = b.id;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_DIM_A');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - ----------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 相同的表或视图的多个例程
REWRITE_GENERAL Y
SQL> set autot on exp
SQL> select a.id, b.name from dim_a a, dim_a b
2 where a.id = b.id;
ID NAME
---------- ------------------------------
1 a1
2 a2
3 a3
.
.
.
56 a56
已选择56行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'DIM_A'
3 1 INDEX (UNIQUE SCAN) OF 'SYS_C001917' (UNIQUE)
由于没有统计信息采用了RBO,下面对表进行分析。
SQL> exec dbms_stats.gather_table_stats(user, 'DIM_A');
PL/SQL 过程已成功完成。
SQL> select a.id, b.name from dim_a a, dim_a b
2 where a.id = b.id;
ID NAME
---------- ------------------------------
1 a1
2 a2
3 a3
.
.
.
56 a56
已选择56行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=327 Bytes=9810)
1 0 TABLE ACCESS (FULL) OF 'MV_DIM_A' (Cost=2 Card=327 Bytes=9810)
SQL> select a.id, c.name from dim_a a, dim_a c
2 where a.id = c.id;
ID NAME
---------- ------------------------------
1 a1
2 a2
3 a3
.
.
.
56 a56
已选择56行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=56 Bytes=504)
1 0 NESTED LOOPS (Cost=2 Card=56 Bytes=504)
2 1 TABLE ACCESS (FULL) OF 'DIM_A' (Cost=2 Card=56 Bytes=336)
3 1 INDEX (UNIQUE SCAN) OF 'SYS_C001917' (UNIQUE)
可以看出,当物化视图中包含一个表多次时,查询重写的要求是查询语句中的别名必须和物化视图定义中的一致。
本文介绍的是一般意义上的限制,对于具体某个SQL能否使用查询重写,可以使用DBMS_MVIEW.EXPLAIN_REWRITE过程。
物化视图的查询首先功能的限制来自两个方面,对物化视图的限制和对查询重写的限制。
查询重写的限制包括:
1.如果物化视图包括本地表和远端表,则只有本地表支持查询重写;
2.基表和物化视图都不能处于SYS用户下;
3.物化视图的查询中如果包含SELECT和GROUP BY列表,则二者必须保持一致;
4.聚集函数必须在表达式的最外层;
5.不允许CONNECT BY语句。
1.如果物化视图包括本地表和远端表,则只有本地表支持查询重写;
SQL> conn yangtk/yangtk@yangtk
已连接。
SQL> create table dim_b (id number primary key, name varchar2(30));
表已创建。
SQL> insert into dim_b select rownum, 'b'||rownum from user_objects;
已创建60行。
SQL> commit;
提交完成。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> create database link yangtk using 'yangtk';
数据库链接已创建。
SQL> select count(*) from dim_b@yangtk;
COUNT(*)
----------
60
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name aname, b.name bname, f.num
3 from dim_a a, dim_b@yangtk b, fact f
4 where a.id = f.aid
5 and b.id = f.bid;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_FACT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------
REWRITE N
REWRITE_FULL_TEXT_MATCH N 实体化视图引用了 FROM 列表中的远程表或视图
REWRITE_PARTIAL_TEXT_MATCH N 实体化视图无法支持任何类型的查询重写
REWRITE_GENERAL N 实体化视图无法支持任何类型的查询重写
优化器无法使用远端数据库中的物化视图,如果在本地建立远端表的物化视图,则Oracle无法确定物化视图中的数据是否和基表中的数据同步,因此包含远端表的物化视图也不支持查询重写。
因此如果一个查询中包含了远端表和本地表,则只有对本地表的访问会被优化器考虑是否使用查询重写,远端表部分不会利用查询重写的功能。
2.基表和物化视图都不能处于SYS用户下;
SQL> conn /@test4 as sysdba
已连接。
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name, b.num from yangtk.dim_a a, yangtk.fact b
3 where a.id = b.aid;
select a.name, b.num from yangtk.dim_a a, yangtk.fact b
*
ERROR 位于第 2 行:
ORA-30359: SYS 具体化视图不支持查询重写
SQL> create table dim_a (id number primary key, name varchar2(30));
表已创建。
SQL> grant select on dim_a to yangtk;
授权成功。
SQL> conn yangtk/yangtk@test4
已连接。
SQL> create materialized view mv_fact enable query rewrite as
2 select a.name, b.num from sys.dim_a a, yangtk.fact b
3 where a.id = b.aid;
where a.id = b.aid
*
ERROR 位于第 3 行:
ORA-01031: 权限不足
SQL> conn /@test4 as sysdba
已连接。
SQL> create materialized view yangtk.mv_fact enable query rewrite as
2 select a.name, b.num from sys.dim_a a, yangtk.fact b
3 where a.id = b.aid;
where a.id = b.aid
*
ERROR 位于第 3 行:
ORA-30354: SYS 关系中不允许查询重写
根据简单的测试可以发现,基表和物化视图都不能包括在SYS用户中。
3.物化视图的查询中如果包含SELECT和GROUP BY列表,则二者必须保持一致;
SQL> create materialized view mv_dim_b enable query rewrite as
2 select count(*) from dim_b
3 group by name;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_DIM_B');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 在 SELECT 列表中忽略了分组列
REWRITE_GENERAL N 在 SELECT 列表中忽略了分组列
如果GROUP BY列表和SELECT列表不一致,则只支持全文匹配的查询重写。
4.聚集函数必须在表达式的最外层;
SQL> create materialized view mv_fact enable query rewrite as
2 select avg(avg(num)) from dim_a a, fact b
3 where a.id = b.aid
4 group by name;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_FACT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - --------------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH N 在 SELECT 列表中忽略了分组列
REWRITE_GENERAL N 表达式中聚集函数嵌套
REWRITE_GENERAL N 在 SELECT 列表中忽略了分组列
5.不允许CONNECT BY语句。
SQL> create table test_connect (id number, father_id number, name varchar2(30));
表已创建。
SQL> create materialized view mv_test_connect enable query rewrite as
2 select id from test_connect
3 start with id = 1
4 connect by prior id = father_id;
实体化视图已创建。
SQL> truncate table mv_capabilities_table;
表已截掉。
SQL> begin
2 dbms_mview.explain_mview('MV_TEST_CONNECT');
3 end;
4 /
PL/SQL 过程已成功完成。
SQL> select capability_name, possible, msgtxt
2 from mv_capabilities_table
3 where capability_name like '%REWRITE%'
4 and capability_name not like '%PCT%';
CAPABILITY_NAME P MSGTXT
------------------------------ - -----------------------------
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL N 存在一个 CONNECT BY 子句
来源:http://blog.student163.com/?uid-8883-action-viewspace-itemid-14706
发表评论
-
Oracle数据库相关系统突然提示“SQLException:违反协议”
2024-02-19 15:50 371SQLException:违反协议这个异常可能由很多的 ... -
CentOS在Docker中安装Oracle
2024-02-06 12:13 5171.拉取Oracle镜像,并检 ... -
Windows Server安装oracle数据库一直停在82%
2023-02-04 12:01 414网上有个说法:服务器超过一定数量的CPU后,将不能正常安装 ... -
ORA-04030错误处理
2023-02-04 11:52 2210【错误描述】 错误信息如下: ORA-04030:在尝 ... -
ORA-04030错误处理
2023-02-04 11:45 403【错误描述】 错误信息如下: ORA-04030:在尝 ... -
Linux安装MySQL数据库
2019-06-10 22:27 16231.进入安装包所在目录,解压: tar zxvf mysql- ... -
确定MySQL在Linux系统中配置文件的位置
2019-04-14 19:30 26301.通过which mysql命令来查看mysql的安装位置。 ... -
mysql set names 命令和 mysql 字符编码问题
2019-04-12 00:34 1015转自:https://www.cnblogs.com/digd ... -
MYSQL中取当前周/月/季/年的第一天与最后一天
2018-11-17 23:16 2096转自:https://blog.csdn.net/ ... -
Oracle删除大量数据的实践
2016-11-07 18:03 5677一、引言 从来没有 ... -
Oracle 数据库简明教程 V0.1
2016-03-23 21:01 1922供初学者入门学习使用,以开发者常见、常用的知识为主,基本上 ... -
Oracle拆分字符串函数
2016-03-23 10:58 3234create or replace type string ... -
Oracle数据库远程连接无响应
2016-03-21 10:20 4155故障现象: 服务器本机使用sqlplus / as s ... -
Oracle PGA详解
2015-10-21 15:34 11274转自:http://yanguz123.iteye.com/b ... -
Oracle12C导入dmp数据
2015-10-08 23:43 20417Oracle12C,发生了较大的变化。以前熟悉的东西变得陌 ... -
SQLLDR数据导入小结
2015-07-25 22:06 73891.创建数据表 CREATE TABLE ... -
Window7安装Oracle10
2015-03-06 12:14 1506每次安装都要百度,转到自己的博客上,找起来方便,还能增加访 ... -
Oracle SQL Developer 连接 Mysql 数据库
2015-02-25 19:36 3558下载JDBC包,解压缩这里只要mysql-connector- ... -
Mysql数据备份与恢复
2015-02-25 19:15 1250备份/恢复策略 1. 要定期做 mysql备份,并考虑系统可以 ... -
Oracle中merge into的使用
2015-01-23 22:57 3913引自:http://www.cnblogs.com/highr ...
相关推荐
介绍通过物化视图对查询进行重写的一个例子,帮助大家理解查询重写的含义
ORACLE使用物化视图和查询重写功能
利用强制查询重写和新的强大的调整顾问程序—它们使您不再需要凭猜测进行工作 ,在 10g 中管理物化视图变得更加容易。文中介绍了物化视图的定义及其一些功能。
我们已经支持当前版本(0.4.0): 物化视图重写。 数据沿袭分析该项目正在积极开发中,并且***尚未准备好进行生产***。在线API 我们提供了免费的API http://sql-booster.mlsql.tech 。 您可以访问...
数据仓库关于使用维进行查询重写,建立自己的物化视图
传统的基于物化视图的RDF模式匹配方法虽然能降低表的自连接操作次数,加快查询模式重写过程,但在视图集中检索模式匹配的视图等价于子图同构这一NP-hard问题。为了减小查询模式重写代价,提高RDF模式匹配过程效率,...
主要介绍了Oracle CBO几种基本的查询转换详解,包含视图合并、子查询解嵌套、谓语前推、物化视图查询重写等内容,需要的朋友可以参考下
7.4.4 物化视图的数据更新 135 7.4.5 查询重写 136 7.5 本章小结 136 7.6 本章实例 137 7.7 习题 137 第8章 函数与存储过程(教学视频:48分钟) 138 8.1 函数 138 8.1.1 函数简介 138 8.1.2 创建函数 139 ...
2.10 使用物化视图进行查询重写 44 2.11 确定执行计划 46 2.12 执行计划并取得数据行 50 2.13 SQL执行——总览 52 2.14 小结 53 第3章 访问和联结方法 55 第4章 SQL是关于集合的 95 第5章 关于问题 116 第6章 SQL...
SQL(Structured Query Language)结构化查询语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。 SQL语言主要包含5个部分 数据定义...
4.10 子查询:简单子查询和带连接的相关比较 107 4.10.1 简单子查询 107 4.10.2 带连接的相关子查询 108 4.11 集合操作符:union、intersect和minus 108 4.11.1 union 109 4.11.2 union all 109 4.11.3 ...
第一部分sql基础 9 基本查询语句 9 实验1:书写一个最简单的sql语句,查询一张表的所有行和所有列 9 ...实验161:物化视图的建立 253 实验162:查询重写 255 实验163:最后的sql优化办法,使用hints
2.10 使用物化视图进行查询重写 44 2.11 确定执行计划 46 2.12 执行计划并取得数据行 50 2.13 SQL执行——总览 52 2.14 小结 53 第3章 访问和联结方法 55 3.1 全扫描访问方法 55 3.1.1 如何选择全扫描操作 56...