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:11203create 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 filterSQL> 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); --依旧使用filterselect /*+ 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 allselect object_name,status from temp where object_id =2union allselect 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的内部过程分析”有了更深的了解,不妨来实际操作一番吧!这里是网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!