Oracle優化網上常見的5個錯誤觀點 - langtianya－IT工程師數位筆記本

文章出處

最近系統的研究了一下ORACLE SQL語句性能調優，在此大言不慚的得出一個觀點——網上很多性能調優的結論都是錯誤的或者不周全的。
現在的DBA大牛些都太低調了，不出來斧正一下，小弟來借這個機會吐槽一下，說的不對，歡迎拍磚，特別是版本問題：

轉入正題：
網絡上大部分結論“可能”適用于ORACLE8或者以前版本（小弟出道晚沒見過ORACLE 8），但是針對9i及以后的版本，很多結論都是欠周全的。

下面舉幾個最常見的問題：

錯誤觀點1、什么時候用IN 什么時候用EXISTS？子查詢數據量少用IN 量多用EXISTS

如果你說半連接子查詢返回數據少的時候用IN，返回數據多的時候用EXISTS，那么恭喜你，你錯了。

糾正：不要輕信網上是如何說的，學了ORACLE最大的感觸就是不要記結論，自己實踐了才知道，創建兩張表練習一下，由于篇幅原因，我這里還是給一個結論——通過執行計劃看到，大多數情況下IN 和EXISTS的效率是一模一樣的，只是有時候EXISTS不能 SUBSTRING UNNESTING，導致執行計劃走FILTER ，執行計劃一旦走了FILTER，驅動表是改變不了的（12C 能不能我不清楚），想象一下，主表是1000W，子表返回20條，由于驅動表改變不了，很有可能是大表驅動小表了。

錯誤觀點2、NOT IN 與 NOT EXISTS 子查詢量少用NOT IN 否則用NOT EXISTS

NOT IN 與 NOT EXISTS也是一個道理，但是要注意NULL的情況，NULL容易導致無法使用索引，可以創建函數索引或與常量一起做一個組合索引。

錯誤觀點3、WHERE條件有先后順序，后面的先執行或前面的先執行

如果過濾數據量基本持平的話，兩個不同的謂詞過濾條件可能會因為腳本的編寫而有先后順序，但是不要因此推斷出“WHERE條件有先后順序，后面的先執行或前面的先執行”，哪個先執行時CBO根據統計信息分析之后說了算，下面兩條語句的執行計劃的邏輯讀都是一樣的

可以通過下面的列子來測試一下執行效果

CREATE TABLE TEST02 AS SELECT * FROM DBA_OBJECTS;
SELECT COUNT(*) FROM TEST02 A WHERE A.OWNER='SYS' AND A.OBJECT_ID=29 ;
SELECT COUNT(*) FROM TEST02 A WHERE A.OBJECT_ID=29 AND A.OWNER='SYS' ;

錯誤觀點4、FROM 語句有左右順序所以要注意書寫順序

同第3點一樣，給個例子——下面兩個語句的執行計劃也是一樣的，ORACLE知道哪個該做驅動表，因此沒有區別

CREATE TABLE T1 AS SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL<=10000;--大表
CREATE TABLE T2 AS SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL<=10;--小表
SELECT COUNT(1) FROM T1,T2;
SELECT COUNT(1) FROM T2,T1;
執行計劃
----------------------------------------------------------
Plan hash value: 4259280259
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | MERGE JOIN CARTESIAN| | 100K| 60 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 10 | 3 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 10000 | 57 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 10000 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

錯誤觀點5、避免使用OR來連接條件，否則導致引擎放棄使用索引而進行全表掃描

如：SELECT ID FROM T WHERE NUM=10 OR NUM=11 會全表掃描

下面來個例子，

CREATE TABLE TEST02 AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX TEST_02_IDX_01 ON TEST02 (OBJECT_ID);
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='9.2.0';
SELECT COUNT(*) FROM TEST02 A WHERE A.OBJECT_ID=28 OR A.OBJECT_ID=29 ;

查看執行計劃

執行計劃
----------------------------------------------------------
Plan hash value: 3430686514
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 |
| 1 | SORT AGGREGATE | | 1 | 5 | |
| 2 | INLIST ITERATOR | | | | |
|* 3 | INDEX RANGE SCAN| TEST_02_IDX_01 | 2 | 10 | 2 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_ID"=28 OR "A"."OBJECT_ID"=29)

實際上使用了索引

總結：網上的經驗可能是某些人看了某些文章，然后在特定的場景下測試了效果一樣，甚至沒有測試就以訛傳訛，容易誤導人，Oracle腳本中有多優化的觀點不同的版本等場景下因為執行計劃的不同而導致結論不一樣，因此要根據實際出發，最好再親自測一下，DBA不會告訴你這么的，因為他們自己還要混飯吃的。
不過說不定我這盤文章中有些結論也是錯的，自己測一遍才知道

其實ORACLE優化還是有很多小細節需要注意的，也有很多的方法，有機會的話再與大家分享

文章列表