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的內部過程分析”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！