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 星型转换

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



容易忽略的Hint

PUSH_SUBQ
This hint causes subqueries to be evaluated at the earliest possible time in a query block. Subqueries are typically executed as the last operation.

PUSH_PRED and NO_PUSH_PRED
These two hints, respectively, force and prevent join predicates from being pushed into a view.

DRIVING_SITE
指定执行计划在remote还是local，driving_site(local)/driving_site(remote)分别指定运行于local/remote db；
This hint is useful when the local tables are small, a lot of data must be processed at the remote site, and only a small subset of the data (for example, aggregated with
GROUP BY, MIN, MAX) is returned to the originating node. In such cases, it makes sense to use the remote site as the driving site by shipping the small local tables across
for processing.

INDEX_COMBINE
If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use whatever Boolean combination of indexes has the best cost estimate. If
certain indexes are given as arguments, the optimizer will try to use some Boolean combination of those particular indexes. Because the Oracle server can convert ROWIDs to bitmaps, the indexes in the bitmap tree do not have to be bitmap indexes; they can be regular B*-tree indexes as well.

INDEX_JOIN
This hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, there must exist a sufficiently small number of
indexes that contain all the columns required to resolve the query.

AND_EQUAL
Choose an access path that merges the scans on several single-column indexes. The maximum number of indexes that can be merged is five.

PQ_DISTRIBUTE http://blog.itpub.net/15480802/viewspace-741125/ 
This hint improves the performance of parallel join operations. You can specify how rows of joined tables should be distributed among producer and consumer query
servers. Using this hint overrides decisions the optimizer would normally make. The second and third arguments specify the distribution for the inner and the outer table,
respectively