oracle优化sql的内部过程分析

113次阅读

共计 4263 个字符，预计需要花费 11 分钟才能阅读完成。

本篇内容主要讲解“oracle 优化 sql 的内部过程分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“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 的内部过程分析”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

正文完