oracle优化sql的内部过程分析

发布时间:2021-08-31 23:42:24 作者:chen
来源:亿速云 阅读:130

本篇内容主要讲解“oracle优化sql的内部过程分析”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“oracle优化sql的内部过程分析”吧!

Oracle对sql的优化过程如下
oracle优化sql的内部过程分析



查询重写

独立于优化器 ,分两部分

1 子查询展开

分为相关子查询和非相关子查询,将其转化为等价join;
Hint:unnest/no_unnest/hash_sj/hash_aj
通常子查询在最后执行,通过Push_subq可尽早执行http://blog.itpub.net/15480802/viewspace-688364/
参考案例
http://blog.itpub.net/15480802/viewspace-703260/
http://blog.itpub.net/15480802/viewspace-688361/

2 视图合并

或将视图展开,或把外部条件推入视图;不能合并的视图,执行计划会显示View关键字;
Hint: merge/no_merge
限制条件
1 集合操作 union/intersect/minus/union all
2 connect by
3 rownum


查询优化

1 In-list/OR

优化器有3种处理方法
1 IN-list迭代器:
将row source每一行同IN-list值逐一比较,列必须有索引;10157事件可禁用此功能;

2 IN-list扩展:
将IN-list或OR扩展成UNION ALL;CBO必须对每个扩展子句评估cost,且执行时每个分支都要读一次表,可使用NO_EXPAND禁用(与USE_CONCAT相反),或将IN-list值存入lookup表并join改进;

3 Filter过滤:
采用filter对取出的结果集进行过滤;


案例

DB:11203
create table temp as select object_id,object_name,status,owner from dba_objects;
exec dbms_stats.gather_table_stats('SYS','TEMP');

select object_name,status from temp where object_id in (1,2,3);

1 filter
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1896031711

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   108 |   188   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| TEMP |     3 |   108 |   188   (2)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3)

13 rows selected.

2 OR展开

use_concat在11203已经不好使了,需要改为USE_CONCAT( OR_PREDICATES(1) )
select /*+ use_concat */ object_name,status from temp where object_id in (1,2,3); --依旧使用filter
select /*+ USE_CONCAT( OR_PREDICATES(1) ) */ object_name,status from temp where object_id in (1,2,3)--与下面的union all等价
select object_name,status from temp where object_id =1
union all
select object_name,status from temp where object_id =2
union all
select object_name,status from temp where object_id =3;

--使用USE_CONCAT(OR_PREDICATES(1))
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   108 |   562   (1)| 00:00:07 |
|   1 |  CONCATENATION     |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
|*  3 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
|*  4 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"=1)
   3 - filter("OBJECT_ID"=2)
   4 - filter("OBJECT_ID"=3)


--直接使用union all
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |   108 |   562  (67)| 00:00:07 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
|*  3 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
|*  4 |   TABLE ACCESS FULL| TEMP |     1 |    36 |   187   (1)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID"=1)
   3 - filter("OBJECT_ID"=2)
   4 - filter("OBJECT_ID"=3)


3 IN-list遍历
要先创建索引
create index ind1 on temp(object_id);


-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     3 |   108 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEMP |     3 |   108 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND1 |     3 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3)


2 星型转换

适用于事实表很小,维度表很大且缺失连接条件

到此,相信大家对“oracle优化sql的内部过程分析”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

推荐阅读:
  1. MySQL的查询过程分析
  2. Oracle优化——单表分页优化

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

oracle

上一篇:为什么继承Python内置类型会出问题

下一篇:mysql忘记密码了怎么办

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》