SQL优化之内联视图，合并到底好不好？

最新推荐文章于 2024-04-24 21:39:01 发布

yrg5101

最新推荐文章于 2024-04-24 21:39:01 发布

阅读量844

点赞数 1

分类专栏： oracle sql 语句调优及相关技术

本文链接：https://blog.csdn.net/yrg5101/article/details/88825306

版权

oracle sql 语句调优及相关技术专栏收录该内容

106 篇文章 1 订阅

订阅专栏

关注我的微信公众号：pythonislover,领取python,大数据，SQL优化相关视频资料！~

Python大数据与SQL优化笔 QQ群：771686295

首先说下什么叫做内联视图

select * from () view_a v , table_name t where v.id = t.id

就是说from后面有视图存在。

在这样的情况下优化器对安装CBO来决定是不是要把这个视图给拆开，也就是是否合并视图。我们判断内联视图有没有合并，可以通过执行计划来查看，如果执行计划中看到VIEW的关键字，说明视图没有合并，视图是作为一个整体与其他部分连接，如果没看到VIEW，说明视图被优化器合并了。我们来看看下面的例子

举例：
SQL> conn scott/tigerConnected.SQL>SQL> create table t_obj as select * from dba_objects;
Table created.
SQL> create table t_user as select * from dba_users;
Table created.
SQL> create table t_segments as select * from dba_segments;
Table created.
--建立一个视图create view v_test as select o.*,u.ACCOUNT_STATUS,u.LOCK_DATE,u.EXPIRY_DATEfrom t_obj oinner joint_user uon o.OWNER= u.USERNAME;
--查看内联视图sql的执行计划SQL> explain plan forselect * fromv_test vinner joint_segments son v.OWNER=s.OWNER;  2    3    4    5    6
Explained.
SQL> set linesize 400SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1435736869
----------------------------------------------------------------------------------| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |            |    33M|    18G|   434  (25)| 00:00:06 ||*  1 |  HASH JOIN          |            |    33M|    18G|   434  (25)| 00:00:06 ||   2 |   TABLE ACCESS FULL | T_SEGMENTS |  5913 |  1882K|    34   (0)| 00:00:01 ||*  3 |   HASH JOIN         |            | 78247 |    19M|   296   (1)| 00:00:04 ||   4 |    TABLE ACCESS FULL| T_USER     |    35 |  1855 |     3   (0)| 00:00:01 ||   5 |    TABLE ACCESS FULL| T_OBJ      | 78247 |    15M|   292   (1)| 00:00:04 |
PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - access("O"."OWNER"="S"."OWNER")   3 - access("O"."OWNER"="U"."USERNAME")
Note-----   - dynamic sampling used for this statement (level=2)

上面的执行计划我们看不到VIEW关键字，但是原SQL是是有VIEW的，说明发生了视图合并的操作

SQL> explain plan forselect /*+ no_merge(v)*/* fromv_test vinner joint_segments son v.OWNER=s.OWNER;  2    3    4    5    6
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2926968361
-----------------------------------------------------------------------------------| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |            |    33M|    17G|   434  (25)| 00:00:06 ||*  1 |  HASH JOIN           |            |    33M|    17G|   434  (25)| 00:00:06 ||   2 |   TABLE ACCESS FULL  | T_SEGMENTS |  5913 |  1882K|    34   (0)| 00:00:01 ||   3 |   VIEW               | V_TEST     | 78247 |    18M|   296   (1)| 00:00:04 ||*  4 |    HASH JOIN         |            | 78247 |    19M|   296   (1)| 00:00:04 ||   5 |     TABLE ACCESS FULL| T_USER     |    35 |  1855 |     3   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|   6 |     TABLE ACCESS FULL| T_OBJ      | 78247 |    15M|   292   (1)| 00:00:04 |-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - access("V"."OWNER"="S"."OWNER")   4 - access("O"."OWNER"="U"."USERNAME")
Note-----
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   - dynamic sampling used for this statement (level=2)
23 rows selected.

在看看上面的执行计划我们在原sql里加了一个hint. /*+ no_merge(v)*/这个就相当于告诉优化器，别自作多情，我的视图不需要合并，也就出现了VIEW的关键字

上面就是视图合并的大概了，但是大家看到这里肯定会说，这些我也知道啊，他合并不合并有什么区别，和我有什么关系，那么请看下面的总结

视图合并对打乱原来视图里面表的连接顺序，但是有时候我们是不想的，我们就是想视图先执行，然后生成一个结果集与其他表连接

create view v_test as select o.*,u.ACCOUNT_STATUS,u.LOCK_DATE,u.EXPIRY_DATEfrom t_obj oinner joint_user uon o.OWNER= u.USERNAME;
select * fromv_test vinner joint_segments son v.OWNER=s.OWNER; 
也就是对于上面的sql, 最后d 执行顺序可能是t_obj 先和t_segments 连接，然后在和t_user连接， 这样有时候，会出现问题，可能会产生笛卡尔积哦，死翘翘！~

2. 对于如下形式的sql

select ....... from () t1 ,() t2 where t1.id=t2.id

这样的sql跑起来很慢，但是但是执行t1里面的select很快，单独执行t2里面的select也很快，但是放一起就死慢，那就要注意是不是视图合并坑了你，我们是不是要手动加hint /*+ no_merge(t1) */或者 hint /*+ use_hash(t1,t2) */, 多个内联视图推荐后面的方法。然后再去试试性能吧，也行是你意向不到的。

3. 在大数量情况下，数据库的优化器还是很智能的，CBO来决定合并或者不合并，但是有时候发现我们有视图就是不能合并，怎么办呢？

那就看看你的视图是不是含有下面一些关键字：

union, union all, start with connect by, rownum, cube

如果视图里面有上面的东东，抱歉，视图只能作为一个整体，不能合并，不要错怪优化器哦。下面我们来看一个例子。

所以这里强调一点，上面的东东，写sql的时候要注意哦，可能不经意间就坑了你。

新建一个带有rownum的视图，没建hint /*+ no_merge(v) */哦，但是我们还是能看到VIEW，说明视图没有合并

SQL> create view v_test_rownum asselect o.*,u.ACCOUNT_STATUS,u.LOCK_DATE,u.EXPIRY_DATEfromt_obj oinner joint_user uon o.OWNER= u.USERNAMEwhere rownum < =1000;  2    3    4    5    6    7    8
View created.
SQL> explain plan forselect * fromv_test_rownum vinner joint_segments son v.OWNER=s.OWNER;  2    3    4    5    6
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 4218700277
---------------------------------------------------------------------------------------| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |               |   422K|   229M|    46   (7)| 00:00:01 ||*  1 |  HASH JOIN            |               |   422K|   229M|    46   (7)| 00:00:01 ||   2 |   VIEW                | V_TEST_ROWNUM |  1000 |   237K|    10  (10)| 00:00:01 ||*  3 |    COUNT STOPKEY      |               |       |       |            |          ||*  4 |     HASH JOIN         |               | 78247 |    34M|    10  (10)| 00:00:01 ||   5 |      TABLE ACCESS FULL| T_USER        |    35 |  1855 |     3   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|   6 |      TABLE ACCESS FULL| T_OBJ         | 78247 |    15M|     7  (15)| 00:00:01 ||   7 |   TABLE ACCESS FULL   | T_SEGMENTS    |  5913 |  1882K|    34   (0)| 00:00:01 |---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - access("V"."OWNER"="S"."OWNER")   3 - filter(ROWNUM<=1000)   4 - access("O"."OWNER"="U"."USERNAME")

PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Note-----   - dynamic sampling used for this statement (level=2)