表太多unionall會有效能問題嗎

1、選擇合適的最佳化器

A、RULE-基於規則

B、COST-基於成本

C、CHOOSE-選擇性

現在ORACLE資料庫版本預設是基於成本的最佳化器模式執行的。

2、選擇最優的表訪問方式

A、全表掃描方式

全表掃描就是順序地訪問表中每條記錄。 ORACLE採用一次讀入多個數據塊的方式最佳化全表掃描。這種方式通常情況效率低。

B、透過ROWID訪問表

採用基於ROWID的訪問方式訪問表， ROWID包含了表中記錄的物理位置資訊，ORACLE採用索引（INDEX）實現了資料和存放資料的物理位置（ROWID）之間的聯絡。 通常索引提供了快速訪問 ROWID的方法，因此那些基於索引列的查詢就可以得到效能上的提高。這種方式通常情況效率高。

3、 合理調整FROM後面的表順序

ORACLE解析器是按照從右到左的順序處理FROM之後的表，FROM子句中寫在最後的表（驅動表driving table）將被最先處理，如果FROM子句中包含多個表時，為了高效率需要選擇記錄最少的表作為驅動表。如果是三個或者以上的表關聯查詢， 那就需要選擇中間表作為基礎表， 交叉表是指那個被其他表所引用的表。

4、WHERE子句的連線順序

ORACLE採用自下而上的順序解析WHERE子句，所以要將過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。

5、SELECT子句中不要使用 '* '

ORACLE在解析的過程中， 會將‘*’ 依次轉換成所有的列名， 這個工作需要透過查詢資料字典完成的， 故而需要消耗更多的查詢時間以及資源。

6、最大限度減少訪問資料庫的次數

ORACLE在內部執行了許多工作，解析SQL語句， 估算索引的利用率， 繫結變數 ， 讀資料塊等，這些工作需要消耗大量的資源以及時間。

7、在SQL*Plus , SQL*Forms和Pro*C中重新設定ARRAYSIZE引數

， 可以增加每次資料庫訪問的檢索資料量 ，建議值為200

8、使用DECODE函式來減少處理時間

使用DECODE函式可以避免重複掃描相同記錄或重複連線相同的表

9、 整合簡單SQL語句,減少資料庫訪問

幾條簡單的資料庫查詢語句，可以把它們整合到一個查詢

10、使用ROWID刪除重複記錄

最高效的刪除重複記錄方法 （ 因為使用了ROWID）例子：

DELETE FROM COTRACT E WHERE E。ROWID > （SELECT MIN（X。ROWID）

FROM COTRACT X WHERE X。CONTRACT_ID = E。CONTRACT_ID ）；

11、清空表用TRUNCATE,刪除部份行用DELETE

當刪除表中的記錄時，在通常情況下， 回滾段用來存放可以被恢復的資訊。 如果你沒有COMMIT事務，ORACLE會將資料恢復到刪除之前的狀態（準確地說是恢復到執行刪除命令之前的狀況） 而當運用TRUNCATE時， 回滾段不再存放任何可被恢復的資訊。當命令執行後，資料不能被恢復。因此很少的資源被呼叫，執行時間也會很短。

12、 合理使用COMMIT

在程式中合理使用COMMIT， 這樣程式的效能得到提高，需求也會因為COMMIT所釋放的資源而減少：

a。 釋放回滾段上用於恢復資料的資訊。

b。 釋放被程式語句獲得的鎖

c。 釋放redo log buffer 中的空間

d。 ORACLE為管理上述3種資源中的內部花費

13、用Where子句替換HAVING子句

避免使用HAVING子句， HAVING 只會在檢索出所有記錄之後才對結果集進行過濾，這個處理需要排序，總計等操作。；如果能透過WHERE子句限制記錄的數目，那就能減少這方面的開銷。 （非oracle中）on、where、having這三個都可以加條件的子句中，on是最先執行，where次之，having最後，因為on是先把不符合條件的記錄過濾後才進行統計，它就可以減少中間運算要處理的資料，按理說應該速度是最快的，where也應該比having快點的，因為它過濾資料後才進行sum，在兩個表聯接時才用on的，所以在一個表的時候，就剩下where跟having比較了。在這單表查詢統計的情況下，如果要過濾的條件沒有涉及到要計算欄位，那它們的結果是一樣的，只是where可以使用rushmore技術，而having就不能，在速度上後者要慢如果要涉及到計算的欄位，就表示在沒計算之前，這個欄位的值是不確定的，根據上篇寫的工作流程，where的作用時間是在計算之前就完成的，而having就是在計算後才起作用的，所以在這種情況下，兩者的結果會不同。在多表聯接查詢時，on比where更早起作用。系統首先根據各個表之間的聯接條件，把多個表合成一個臨時表後，再由where進行過濾，然後再計算，計算完後再由having進行過濾。由此可見，要想過濾條件起到正確的作用，首先要明白這個條件應該在什麼時候起作用，然後再決定放在那個位置。

14、減少對錶的查詢

在含有子查詢的SQL語句中，要特別注意減少對錶的查詢，例如：

SELECT TAB_NAME FROM TABLES WHERE （TAB_NAME，VER） = （ SELECT

TAB_NAME，VER FROM TAB_COLUMNS WHERE VERSION = 64）

15、透過內部函式提高SQL效率

複雜的SQL往往犧牲了執行效率，能夠掌握ORACLE內部函式解決問題，在實際工作中對提升效率是非常有益的。

16、使用表的別名

在SQL語句中連線多個表時， 請使用表的別名並把別名字首於每個Column上，這樣可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。

17、用EXISTS替代IN、用NOT EXISTS替代NOT IN

在許多基於基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接。在這種情況下， 使用EXISTS（或NOT EXISTS）通常將提高查詢的效率。。在子查詢中，NOT IN子句將執行一個內部的排序和合並。，無論在哪種情況下，NOT IN都是最低效的 （因為它對子查詢中的表執行了一個全表遍歷）。 為了避免使用NOT IN ，我們可以把它改寫成外連線（Outer Joins）或NOT EXISTS。

例如：

A、高效率

SELECT * FROM EMP （基礎表） WHERE EMPNO > 0

AND

EXISTS

（SELECT ‘X‘ FROM DEPT WHERE DEPT。DEPTNO = EMP。DEPTNO AND LOC = ‘MELB’）

B、低效率

SELECT * FROM EMP （基礎表） WHERE EMPNO > 0

AND DEPTNO

IN

（SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB‘）

18、識別'低效執行'的SQL語句

雖然目前各種關於SQL最佳化的圖形化工具層出不窮，但是寫出自己的SQL工具來解決問題始終是一個最好的方法：

SELECT EXECUTIONS ， DISK_READS， BUFFER_GETS，

ROUND（（BUFFER_GETS-DISK_READS）/BUFFER_GETS，2） Hit_radio，

ROUND（DISK_READS/EXECUTIONS，2） Reads_per_run，

SQL_TEXT

FROM V$SQLAREA

WHERE EXECUTIONS>0

AND BUFFER_GETS > 0

AND （BUFFER_GETS-DISK_READS）/BUFFER_GETS < 0。8

ORDER BY 4 DESC；

19 、使用索引提高效率

索引是表的一個概念部分，用來提高檢索資料的效率，ORACLE使用了一個複雜的自平衡B-tree結構。 一般情況下透過索引查詢資料比全表掃描要快。當ORACLE找出執行查詢和Update語句的最佳路徑時， ORACLE最佳化器將使用索引。。同樣在聯結多個表時使用索引也可以提高效率。，另一個使用索引的好處是，它提供了主鍵（primary key）的唯一性驗證，那些LONG或LONG RAW資料型別， 你可以索引幾乎所有的列。。通常， 在大型表中使用索引特別有效。 當然你也會發現， 在掃描小表時，使用索引同樣能提高效率。 雖然使用索引能得到查詢效率的提高，但是我們也必須注意到它的代價。 索引需要空間來儲存，也需要定期維護， 每當有記錄在表中增減或索引列被修改時， 索引本身也會被修改。所以每條記錄的INSERT ， DELETE ， UPDATE將為此多付出4 ， 5 次的磁碟I/O 。因為索引需要額外的儲存空間和處理，那些不必要的索引反而會使查詢反應時間變慢。定期的重構索引是有必要的。

索引重構：ALTER INDEX REBUILD

20、 用EXISTS替換DISTINCT

當提交一個包含一對多表資訊（比如部門表和僱員表）的查詢時，避免在SELECT子句中使用DISTINCT。 一般可以考慮用EXIST替換， EXISTS 使查詢更為迅速，因為RDBMS核心模組將在子查詢的條件一旦滿足後，立刻返回結果。 例子：

A、高效

SELECT CONTRACT_NO，CUST_NAME FROM CONTRACT D WHERE EXISTS （ SELECT ‘X’

FROM CONTRACT E WHERE E。CONTRACT_NO = D。CONTRACT_NO）

B、低效

SELECT DISTINCT CONTRACT_NO，CUST_NAME FROM CONTRACT D ， EMP E

WHERE D。CONTRACT_NO = E。CONTRACT_NO

21、sql語句用大寫的

因為oracle總是先解析sql語句，把小寫的字母轉換成大寫的再執行

22、 在java程式碼中儘量少用連線符“＋”連線字串！

23、 避免在索引列上使用NOT

要避免在索引列上使用NOT， NOT會產生在和在索引列上使用函式相同的影響。 當ORACLE”遇到”NOT，他就會停止使用索引轉而執行全表掃描。

24、 避免在索引列上使用計算

WHERE子句中，如果索引列是函式的一部分最佳化器將不使用索引而使用全表掃描

A、低效

SELECT … FROM contract WHERE SAL * 12 > 24000；

B、高效

SELECT … FROM contract WHERE SAL > 24000/12；

25、用>=替代>

A、高效

SELECT * FROM contract WHERE contract_id >=10

B、低效

SELECT * FROM contract WHERE contract_id >9

兩者的區別在於， 前者DBMS將直接跳到第一個contract_id 等於10的記錄而後者將首先定位到contract_id=9的記錄並且向前掃描到第一個contract 大於9的記錄。

26、用UNION替換OR

通常情況下， 用UNION替換WHERE子句中的OR將會起到較好的效果。 對索引列使用OR將造成全表掃描，注意， 以上規則只針對多個索引列有效。 如果有column沒有被索引， 查詢效率可能會因為你沒有選擇OR而降低。 在下面的例子中， LOC_ID 和REGION上都建有索引。

A、高效：

SELECT LOC_ID ， LOC_DESC ， REGION

FROM LOCATION

WHERE LOC_ID = 20

UNION

SELECT LOC_ID ， LOC_DESC ， REGION

FROM LOCATION

WHERE REGION = “MEL”

B、低效

SELECT LOC_ID ， LOC_DESC ， REGION

FROM LOCATION

WHERE LOC_ID = 20 OR REGION = “MEL”

如果你堅持要用OR， 那就需要返回記錄最少的索引列寫在最前面。

27、 用IN來替換OR

這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的。

A、低效

SELECT。。 FROM LOC

WHERE LOC_ID = 20 OR LOC_ID = 30 OR LOC_ID = 40

B、高效

SELECT… FROM LOC WHERE LOC_ID IN （20，30，40）

28、避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引。對於單列索引，如果列包含空值，索引中將不存在此記錄。 對於複合索引，如果每個列都為空，索引中同樣不存在此記錄。如果至少有一個列不為空，則記錄存在於索引中．舉例： 如果唯一性索引建立在表的A列和B列上， 並且表中存在一條記錄的A，B值為（12，null） ， ORACLE將不接受下一條具有相同A，B值（12，null）的記錄（插入）。 然而如果所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等於空。 因此你可以插入10000 條具有相同鍵值的記錄，當然它們都是空！ 因為空值不存在於索引列中，所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引。

A、低效： （索引失效）

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL；

B、高效： （索引有效）

SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0；

29、總是使用索引的第一個列

如果索引是建立在多個列上， 只有在它的第一個列（leading column）被where子句引用時，最佳化器才會選擇使用該索引。 這也是一條簡單而重要的規則，當僅引用索引的第二個列時，最佳化器使用了全表掃描而忽略了索引。

30、用UNION-ALL 替換UNION

（ 前提條件查詢不去重或者資料本身不存在重複）

當SQL語句需要UNION兩個查詢結果集合時，這兩個結果集合會以UNION-ALL的方式被合併， 然後在輸出最終結果前進行排序。 如果用UNION ALL替代UNION， 這樣排序就不是必要了。 效率就會因此得到提高。 需要注意的是，UNION ALL 將重複輸出兩個結果集合中相同記錄。 因此各位還是要從業務需求分析使用UNION ALL的可行性。 UNION 將對結果集合排序，這個操作會使用到SORT_AREA_SIZE這塊記憶體。 對於這塊記憶體的最佳化也是相當重要的。 下面的SQL可以用來查詢排序的消耗量

A、低效：

SELECT ACCT_NUM， BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ‘31-DEC-95’

UNION

SELECT ACCT_NUM， BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ‘31-DEC-95’

B、高效：

SELECT ACCT_NUM， BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ‘31-DEC-95’

UNION ALL

SELECT ACCT_NUM， BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ‘31-DEC-95’

31、 用WHERE替代ORDER BY

ORDER BY 子句只在兩種嚴格的條件下使用索引。

ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序。

ORDER BY中所有的列必須定義為非空。

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能並列。

低效： （索引不被使用）

SELECT CONTRACT_NO FROM CONTRACT ORDER BY CONTR_TYPE

高效： （使用索引）

SELECT CONTRACT_NO FROM CONTRACT WHERE CONTR_TYPE > 0

32、 避免改變索引列的型別

當比較不同資料型別的資料時， ORACLE自動對列進行簡單的型別轉換。

假設 EMPNO是一個數值型別的索引列。

SELECT … FROM CONTRACT WHERE CONTR_TYPE = ‘1234‘

實際上，經過ORACLE型別轉換， 語句轉化為：

SELECT …FROM CONTRACT WHERE CONTR_TYPE = TO_NUMBER（‘1234’）

幸運的是，型別轉換沒有發生在索引列上，索引的用途沒有被改變。

現在，假設EMP_TYPE是一個字元型別的索引列。

SELECT … FROM CONTRACT WHERE CONTR_TYPE = 1234

這個語句被ORACLE轉換為：

SELECT … FROM CONTRACT WHERE TO_NUMBER（CONTR_TYPE）=1234

因為內部發生的型別轉換， 這個索引將不會被用到！ 為了避免ORACLE對你的SQL進行隱式的型別轉換， 最好把型別轉換用顯式表現出來。 注意當字元和數值比較時， ORACLE會優先轉換數值型別到字元型別

33、需要當心的WHERE子句

某些SELECT 語句中的WHERE子句不使用索引

在下面的例子裡， （1）‘！=‘ 將不使用索引。 記住， 索引只能告訴你什麼存在於表中， 而不能告訴你什麼不存在於表中。 （2） ‘||’是字元連線函式。 就象其他函式那樣， 停用了索引。 （3） ‘+‘是數學函式。 就象其他數學函式那樣， 停用了索引。 （4）相同的索引列不能互相比較，這將會啟用全表掃描。

34、

正確使用索引

a。 如果檢索資料量超過30%的表中記錄數。使用索引將沒有顯著的效率提高。

b。 在特定情況下， 使用索引也許會比全表掃描慢， 但這是同一個數量級上的區別。 而通常情況下，使用索引比全表掃描要快幾倍乃至幾千倍

35、

避免使用耗費資源的操作

帶有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL語句會啟動SQL引擎

執行耗費資源的排序（SORT）功能。 DISTINCT需要一次排序操作， 而其他的至少需要執行兩次排序。 通常， 帶有UNION， MINUS ， INTERSECT的SQL語句都可以用其他方式重寫。 如果你的資料庫的SORT_AREA_SIZE調配得好， 使用UNION ， MINUS， INTERSECT也是可以考慮的， 畢竟它們的可讀性很強。

36、

最佳化GROUP BY

提高GROUP BY 語句的效率， 可以透過將不需要的記錄在GROUP BY 之前過濾掉。下面兩個查詢返回相同結果但第二個明顯就快了許多。

A、低效

SELECT JOB ， AVG（SAL）

FROM CONTRACT

GROUP BUY JOB

HAVING JOB = ‘PRESIDENT’

OR JOB = ‘MANAGER‘

B、高效

SELECT JOB ， AVG（SAL）

FROM CONTRACT

WHERE JOB = ‘PRESIDENT’

OR JOB = ‘MANAGER‘

GROUP BUY JOB

37、帶萬用字元(%)的like語句

A、高效

select * from contract where cust_name like ’B%‘

B、低效

select * from contract where cust_name like ’%B%‘

38、正確使用hint

Hint 是Oracle 提供的一種SQL語法，它允許使用者在SQL語句中插入相關的語法，從而影響SQL的執行方式。因為Hint的特殊作用，所以對於開發人員不應該在程式碼中使用它，Hint 更像是Oracle提供給DBA用來分析問題的工具 。在SQL程式碼中使用Hint，可能導致非常嚴重的後果，因為資料庫的資料是變化的，在某一時刻使用這個執行計劃是最優的，在另一個時刻，卻可能很差，這也是CBO 取代RBO的原因之一，規則是死的，而資料是時刻變化的，為了獲得最正確的執行計劃，只有知道表中資料的實際情況，透過計算各種執行計劃的成本，則其最優，才是最科學的，這也是CBO的工作機制。 在SQL程式碼中加入Hint，特別是效能相關的Hint是很危險的做法。在使用Hint時需要注意的一點是，並非任何時刻Hint都起作用。 導致HINT 失效的原因有如下兩點：

A、如果CBO 認為使用Hint 會導致錯誤的結果時，Hint將被忽略。如索引中的記錄因為空值而和表的記錄不一致時，結果就是錯誤的，會忽略hint。

B、如果表中指定了別名，那麼Hint中也必須使用別名，否則Hint也會忽略。

39、<> 運算子（不等於）

不等於運算子是永遠不會用到索引的，因此對它的處理只會產生全表掃描，所以我們需要少用不等於。

40、在一般情況下當查詢表的資料小於表總行數5%以內都需要建立索引，當查詢結構大於表的5%時需要走全表掃描。

41、列基數定義：是某一列的唯一鍵個數，主鍵列基數等於總行數。

42、列選擇性：是某行列基數除以表總行數乘以100%

43、當一個列出現在where條件中，該列沒有建立索引並且選擇性大於20%，那麼該列就必須建立索引。

44、查詢表上那些列需要建立索引

45、回表定義：透過索引中記錄的rowid訪問表中的資料就叫回表；回表一般是單塊讀；回表次數太多會嚴重影響SQL效能；（TABLE ACCESS BY INDEX ROWID）

46、獲取表資料存放的塊數量

47、叢集因子：叢集因子用於判斷索引回表需要消耗的物理I/O次數；叢集因子介於表的塊數和錶行數之間；如果叢集因子與塊數接近，說明表的資料基本上時有序的，而且其順序基本與索引順序一樣。這樣在進行索引範圍或者索引全掃描的時候，回表只需要讀取少量的資料塊就能完成；如果叢集因子與表記錄接近，說明表的資料和索引順序差異較大，在進行索引範圍掃描或索引全掃描的時候，回表會讀取更多的資料塊；叢集因子只會影響索引範圍掃描（index range scan）以及索引全掃描（index full scan），只有這兩種索引掃描方式會有大量資料回表；叢集因子影響索引回表的物理I/O次數

48、統計表的總行數（num_rows）、塊數（blocks）、平均長度（avg_row_len），可透過DBA_TABLES獲取

49、大表不用upper（）和lower

50、用ExplainPlan分析SQL語句

51、表示全表掃描，一般情況下是多塊讀；等待事件為 db fifile scattered read ，如果是並行全表掃描，等待事件為direct path read；Oracle 11g中在對一個大表進行全表掃描的時候，會將表直接讀入PGA，繞過buffffer cache，事件direct path read 在開啟了非同步I/O（ disk_asynch_io）的情況下統計是不準確的，可以altersystem set “_serial_direct_read”=false“禁用direct path read

52、TABLE ACCESS BY USER ROWID 表示直接用ROWID獲取資料，單塊讀（效能最高）

53、TABLE ACCESS BY ROWID RANGE 表示ROWID範圍掃描，多塊讀

54、TABLE ACCESS BY INDEX RANGE表示回表，單塊讀

55、INDEX UNIQUE SCAN表示索引唯一掃描，單塊讀

56、INDEX RANGE SCAN表示索引範圍掃描，單塊讀，返回的資料是有序的，預設升序；HINT：INDEX（表名/別名 索引名）對唯一索引或者主鍵進行範圍查詢，對非唯一索引進行等值查詢，範圍查詢，就會發生INDEX RANGESCAN。等待事件為 db fifile sequential read ；INDEX RANGE SCAN DESCENDING表示索引降序範圍掃描；

57、INDEX SKIP SCAN表示索引跳躍掃描，單塊讀，返回的資料是有序的（預設升序）；HINT：INDEX_SS（表名/別名 索引名）當組合索引的引導列（第一個列）沒有在where條件中，並且組合索引的引導列/前幾個的列的基數很低，where過濾條件對組合索引中非引導列進行過濾的時候就會發生索引跳躍掃描，等待事件為 dbfifile sequential read ；當執行計劃中出現了INDEX SKIP SCAN，我們可以直接在過濾列上建立索引，使用INDEX RANGESCAN代替INDEX SKIP SCAN

58、INDEX FULL SCAN表示索引全掃描，單塊讀，返回的資料是有序的（預設升序）；HINT：INDEX（表名/別名 索引名）等待事件為db fifile sequential read

59、INDEX FAST FULL SCAN表示索引快速全掃描，多塊讀；HINT：INDEX_FFS（表名/別名 索引名）；等待事件為db fifile scattered read，如果是並行掃描，等待事件為 direct path read

60、INDEX FULL SCAN（MIN/MAX）表示索引最小/最大值掃描，單塊讀；發生在select max（column） from table 或 select min（column） from table等SQL中

61、MAT_VIEW REWRITE ACCESS FULL表示物化檢視全表掃描，多塊讀

62、單塊讀與多塊讀從磁碟1次讀取1個塊到buffffer cache就叫單塊讀，從磁碟1次讀取多個塊到buffffer cache就叫多塊讀；一次I/O最多隻能讀取或者寫入1MB資料；在判斷哪個訪問路徑效能好的時候，通常是估算每個訪問路徑的I/O次數，誰的I/O次數少，誰的效能就好

63、為什麼有時候索引掃描比全表掃描更慢回表消耗較多的物理I/O

64、巢狀迴圈（NESTED LOOPS）巢狀迴圈的演算法：驅動表返回一行資料，透過連線列傳值給被驅動表，驅動表返回多少行，被驅動表就要被掃描多少次。驅動表應該返回少量資料；巢狀迴圈被驅動表必須走索引；巢狀迴圈被驅動表索引只能走INDEX UNIQUE SCAN或者INDEX RANGE SCAN；巢狀迴圈被驅動表的連線列基數應該很高；兩表關聯返回少量資料才能走巢狀迴圈；巢狀迴圈不需要消耗PGA

65、HASH連線（HASH JOIN）兩表關聯返回大量資料應該走HASH連線；HASH連線的演算法：兩表等值關聯，返回大量資料，將較小的表選為驅動表，將驅動表的“select列和join列”讀入PGA中的work area，然後對驅動表的連線列進行hash運算生成hash table，當驅動表的所有資料完全讀入PAG中的work area之後，再讀取被驅動表（被驅動表不需要讀入PAG的work area），對被驅動表的連線列也進行hash運算，然後到PGA中的work area去探測hash table，找到資料就關聯上，沒找到資料就沒關聯上。雜湊連線支援支等值連線；執行計劃中離HASH連線關鍵字最近的表就是驅動表

66、排序合併連線（SORT MERGE JOIN）排序合併連線主要用於處理兩表非等值關聯，比如>，>=，<，<=，<>，但是不能用於instr、substr、like、regexp_like關聯，instr、substr、like、regexp_like關聯只能走巢狀迴圈。排序合併連線需要將兩個表都放入PGA中；排序合併連線的演算法：兩表關聯，先對兩個表根據連線列進行排序，將較小的表作為驅動表（Oracle官方認為排序合併連線沒有驅動表，筆者認為是有的），然後從驅動表中取出連線列的值，到已經排好序的被驅動表中匹配資料，如果匹配上資料，就關聯成功。驅動表返回多少行，被驅動表就要被匹配多少次，這個匹配的過程類似巢狀迴圈，但是巢狀迴圈是從被驅動表的索引中匹配資料，而排序合併連線是在記憶體中（PGA中的work area）匹配資料

67、標量子查詢（SCALAR SUBQUERY）當一個子查詢介於select與from之間，這種子查詢就叫標量子查詢；標量子查詢可以等價改寫為外連線

68、IN和EXIST誰快in和exists是半連線，半連線也屬於表連線，那麼既然是表連線，我們需要關心兩表的大小以及兩表之間究竟走什麼連線方式，才能去最佳化SQL；

69、SQL語句的本質標量子查詢可以改寫為外連線（需要注意表與表之間關係，去重）；半連線可以改寫為內連線（需要注意表與表之間關係，去重）；反連線可以改寫為外連線（不需要注意表與表之間的關係，也不需要去重）；

70、全表掃描成本計算

全表掃描成本的計算方式如下：

Cost = （#SRds * sreadtim +#MRds * mreadtim +CPUCycles / cpuspeed） / sreadtime

#SRds - number of single block reads 表示單塊讀次數

#MRds - number of multi block reads 表示多塊讀次數

#CPUCyles - number of CPU cycles CPU時鐘週期數

sreadtim - single block read time 一次單塊讀耗時，單位毫秒

mreadtim - multi block read time 一次多塊讀耗時，單位毫秒

cpuspeed - CPU cycles per second 每秒CPU時鐘週期數

71、SQL最佳化核心思想方設法減少SQL的物理I/O次數（不管是單塊讀次數還是多塊讀次數）

72、子查詢非巢狀子查詢非巢狀（Subquery Unnesting）：當where子查詢中有in、not in、exists、not exists等，CBO會嘗試將子查詢展開（unnest），從而消除FILTER，這個過程就叫作子查詢非巢狀。子查詢非巢狀的目的就是消除FILTER。為什麼要消除FILTER呢？因為FILTER的驅動表是固定的，一旦驅動表被固定，那麼執行計劃也就被固定了。對於DBA來說這並不是好事，因為一旦固定的執行計劃本身是錯誤的（低效的），就會引起效能問題，想要提升效能必須改寫SQL語句，但是這時SQL已經上線，無法更改，所以，一定要消除FILTER。

73、檢視合併（view merge）檢視合併（View Merge）：當SQL語句中有內聯檢視（in-line view，from後面的子查詢），或者SQL語句中有用create view建立的檢視，CBO會嘗試將內聯檢視/檢視拆開，進行等價的改寫，這個過程就叫作檢視合併。如果沒有發生檢視合併，在執行計劃中，我們可以看到VIEW關鍵字，而且檢視/子查詢會作為一個整體。如果發生了檢視合併，那麼檢視/子查詢就會被拆開，而且執行計劃中檢視/子查詢部分就沒有VIEW關鍵字。/*+ no_merge */

74、謂詞推入（pushing predicate）謂詞推入（Pushing Predicate）：當SQL語句中包含不能合併的檢視，同時檢視有謂詞過濾（也就是where過濾條件），CBO會將謂詞過濾條件推入檢視中，這個過程就叫作謂詞推入。謂詞推入的主要目的就是讓Oracle儘可能早地過濾掉無用的資料，從而提升查詢效能。

75、分頁語句最佳化思路：要利用索引已經排序特性，將order by的列包含在索引中，同時也要利用rownum的COUNTSTOPKEY特性來最佳化分頁SQL。如果分頁中沒有排序，可以直接利用rownum的COUNT STOPKEY特性來最佳化分頁SQL。現在我們繼續完善分頁語句的最佳化思路：如果分頁語句中有排序（order by），要利用索引已經排序特性，將order by的列按照排序的先後順序包含在索引中，同時要注意排序是升序還是降序。如果分頁語句中有過濾條件，我們要注意過濾條件是否有等值過濾條件，如果有等值過濾條件，要將等值過濾條件優先組合在一起，然後將排序列放在等值過濾條件後面，最後將非等值過濾列放排序列後面。如果分頁語句中沒有等值過濾條件，我們應該先將排序列放在索引前面，將非等值過濾列放後面，最後利用rownum的COUNT STOPKEY特性來最佳化分頁SQL。如果分頁中沒有排序，可以直接利用rownum的 COUNT STOPKEY特性來最佳化分頁SQL。

76、多表關聯分頁最佳化思路，多表關聯分頁語句，如果有排序，只能對其中一個表進行排序，讓參與排序的表作為巢狀迴圈的驅動表，並且要控制驅動表返回的資料順序與排序的順序一致，其餘表的連線列要建立好索引。如果有外連線，我們只能選擇主表的列作為排序列，語句中不能有distinct、groupby、max、min、avg、union、union all，執行計劃中不能出現SORT ORDER BY

77、查詢外來鍵沒建立索引的表

with cons as （select /*+ materialize */ owner，

table_name， constraint_name

from dba_constraints

where owner = ’SCOTT‘

AND constraint_type = ’R‘），

idx as （select /*+ materialize */ table_owner， table_name， column_name

from dba_ind_columns

where table_owner = ’SCOTT‘）

select owner， table_name， constraint_name， column_name

from dba_cons_columns

where （owner， table_name， constraint_name）

in （select * from cons）

and （owner， table_name， column_name）

not in （select * from idx）

78、查詢需要直方圖的列

select a。owner， a。table_name，

a。column_name， b。num_rows，

a。num_distinct Cardinality，

round（a。num_distinct / b。num_rows * 100， 2） selectivity

from dba_tab_col_statistics a， dba_tables b

where a。owner = b。owner

and a。table_name = b。table_name

and a。owner = ’SCOTT‘

and round（a。num_distinct / b。num_rows * 100， 2） < 5

and num_rows > 50000

and （a。table_name， a。column_name）

in （select o。name， c。name from sys。col_usage$ u， sys。obj$ o， sys。col$ c， sys。user$ r

where o。obj# = u。obj#

and c。obj# = u。obj#

and c。col# = u。intcol#

and r。name = ’SCOTT‘）

79、查詢必須建立索引的列

select owner， table_name，

column_name， num_rows，

Cardinality， selectivity

from （select a。owner， a。table_name， a。column_name， b。num_rows， a。num_distinct Cardinality， round（a。num_distinct / b。num_rows * 100， 2）

selectivity

from dba_tab_col_statistics a， dba_tables b

where a。owner = b。owner

and a。table_name = b。table_name and a。owner = ’SCOTT‘）

where selectivity >= 20

and num_rows > 50000

and （table_name， column_name）

not in （select table_name， column_name

from dba_ind_columns where table_owner = ’SCOTT‘

and column_position = 1）

and （table_name， column_name）

in （select o。name， c。name

from sys。col_usage$ u， sys。obj$ o， sys。col$ c， sys。user$ r

where o。obj# = u。obj#

and c。obj# = u。obj#

and c。col# = u。intcol#

and r。name = ’SCOTT‘）；

80、查詢帶select * 的SQL

select a。sql_id， a。sql_text，

c。owner， d。table_name，

d。column_cnt， c。size_mb

from v$sql a， v$sql_plan b，

（select owner， segment_name， sum（bytes / 1024 / 1024） size_mb

from dba_segments

group by owner， segment_name） c，

（select owner， table_name， count（*） column_cnt

from dba_tab_cols

group by owner， table_name） d where a。sql_id = b。sql_id

and a。child_number = b。child_number

and b。object_owner = c。owner

and b。object_name = c。segment_name

and b。object_owner = d。owner

and b。object_name = d。table_name and REGEXP_COUNT（b。projection， ’］‘） = d。column_cnt

and c。owner = ’SCOTT‘

order by 6 desc；

81、查查有標量子查詢的SQL

select sql_id， sql_text， module

from v$sql

where parsing_schema_name = ’SCOTT‘

and module = ’SQL*Plus‘

AND sql_id in （select sql_id

from （select sql_id， count（*） over（partition by sql_id， child_number， depth） cnt

from V$SQL_PLAN

where depth = 1

and （object_owner = ’SCOTT‘ or object_owner is null））

where cnt >= 2）；

82、查詢自定義函式的SQL

select distinct sql_id， sql_text， module

from V$SQL，

（select object_name

from DBA_OBJECTS O

where owner = ’SCOTT‘

and object_type in （’FUNCTION‘， ’PACKAGE‘））

where （instr（upper（sql_text）， object_name） > 0）

and plsql_exec_time > 0

and regexp_like（upper（sql_fulltext）， ’^［SELECT］‘）

and parsing_schema_name = ’SCOTT‘；

83、查詢表被多次反覆呼叫SQL

select a。parsing_schema_name schema，

a。sql_id， a。sql_text， b。object_name，

b。cnt

from v$sql a，

（select * from

（select sql_id， child_number， object_owner， object_name， object_type， count（*） cnt

from v$sql_plan

where object_owner = ’SCOTT‘

group by sql_id， child_number， object_owner， object_name， object_type）

where cnt >= 2） b

where a。sql_id = b。sql_id

and a。child_number = b。child_number；

84、查詢走了FILTER的SQL

select parsing_schema_name schema，

sql_id， sql_text

from v$sql

where parsing_schema_name = ’SCOTT‘

and （sql_id， child_number） in

（select sql_id， child_number

from v$sql_plan

where operation = ’FILTER‘

and filter_predicates like ’%IS NOT NULL%‘ minus

select sql_id， child_number

from v$sql_plan

where object_owner = ’SYS‘）

85、查詢返回行數較多的巢狀迴圈SQL

select * from

（select parsing_schema_name schema， sql_id， sql_text， rows_processed / executions rows_processed

from v$sql

where parsing_schema_name = ’SCOTT‘

and executions > 0

and rows_processed / executions > 10000 order by 4 desc） a

where a。sql_id in

（select sql_id from v$sql_plan

where operation like ’%NESTED LOOPS%‘

and id <= 5）；

86、查詢NL被驅動表走了全表掃描的SQL

select c。sql_text， a。sql_id，

b。object_name， d。mb

from v$sql_plan a，

（select * from （

select sql_id， child_number，

object_owner， object_name，

parent_id， operation，

options， row_number（） over（partition by sql_id， child_number， parent_id order by id） rn

from v$sql_plan）

where rn = 2） b， v$sql c，

（select owner， segment_name， sum（bytes / 1024 / 1024） mb

from dba_segments

group by owner， segment_name） d

where b。sql_id = c。sql_id

and b。child_number = c。child_number

and b。object_owner = ’SCOTT‘

and a。sql_id = b。sql_id

and a。child_number = b。child_number

and a。operation like ’%NESTED LOOPS%‘

and a。id = b。parent_id

and b。operation = ’TABLE ACCESS‘

and b。options = ’FULL‘ and b。object_owner = d。owner

and b。object_name = d。segment_name

order by 4 desc；

87、查詢走了TABLE ACCESS FULL的SQL

select a。sql_id， a。sql_text， d。table_name，

REGEXP_COUNT（b。projection， ’］‘） || ’/‘ || d。column_cnt column_cnt，

c。size_mb， b。FILTER_PREDICATES filter

from v$sql a， v$sql_plan b，

（select owner， segment_name， sum（bytes / 1024 / 1024） size_mb

from dba_segments

group by owner， segment_name） c，

（select owner， table_name， count（*） column_cnt

from dba_tab_cols

group by owner， table_name） d

where a。sql_id = b。sql_id

and a。child_number = b。child_number

and b。object_owner = c。owner

and b。object_name = c。segment_name

and b。object_owner = d。owner

and b。object_name = d。table_name

and c。owner = ’SCOTT‘

and b。operation = ’TABLE ACCESS‘

and b。options = ’FULL‘

order by 5 desc；

88、查詢出走了INDEX FULL SCAN的SQL

select c。sql_text， c。sql_id， b。object_name， d。mb

from v$sql_plan b， v$sql c，

（select owner， segment_name， sum（bytes / 1024 / 1024） mb

from dba_segments

group by owner， segment_name） d

where b。sql_id = c。sql_id

and b。child_number = c。child_number

and b。object_owner = ’SCOTT‘

and b。operation = ’INDEX‘

and b。options = ’FULL SCAN‘

and b。object_owner = d。owner

and b。object_name = d。segment_name

order by 4 desc；

89、查詢走了INDEX SKIP SCAN的SQL

select c。sql_text， c。sql_id， b。object_name， d。mb

from v$sql_plan b， v$sql c，

（select owner， segment_name， sum（bytes / 1024 / 1024） mb

from dba_segments group by owner， segment_name） d

where b。sql_id = c。sql_id

and b。child_number = c。child_number

and b。object_owner = ’SCOTT‘

and b。operation = ’INDEX‘

and b。options = ’SKIP SCAN‘

and b。object_owner = d。owner

and b。object_name = d。segment_name

order by 4 desc；

90、查詢出索引被哪些SQL引用

select a。sql_id， a。sql_text，

d。table_name， REGEXP_COUNT（b。projection， ’］‘） || ’/‘ || d。column_cnt column_cnt， c。size_mb，

b。FILTER_PREDICATES filter

from v$sql a， v$sql_plan b，

（select owner， segment_name， sum（bytes / 1024 / 1024） size_mb

from dba_segments

group by owner， segment_name） c，

（select owner， table_name， count（*） column_cnt

from dba_tab_cols

group by owner， table_name） d

where a。sql_id = b。sql_id

and a。child_number = b。child_number

and b。object_owner = c。owner

and b。object_name = c。segment_name

and b。object_owner = d。owner

and b。object_name = d。table_name

and c。owner = ’SCOTT‘

and b。operation = ’TABLE ACCESS‘

and b。options = ’BY INDEX ROWID‘

and REGEXP_COUNT（b。projection， ’］‘） / d。column_cnt < 0。25

order by 5 desc；

select a。sql_text， a。sql_id， b。object_owner， b。object_name， b。object_type

from v$sql a， v$sql_plan b where a。sql_id = b。sql_id

and a。child_number = b。child_number and object_owner = ’SCOTT‘

and object_type like ’%INDEX%‘

order by 3， 4， 5；

91、查詢走了笛卡兒積的SQL

select c。sql_text， a。sql_id，

b。object_name， a。filter_predicates filter，

a。access_predicates predicate， d。mb

from v$sql_plan a，

（select * from （select sql_id， child_number，

object_owner， object_name，

parent_id， operation，

options，row_number（） over（partition by sql_id， child_number， parent_id

order by id） rn

from v$sql_plan）

where rn = 1） b， v$sql c，

（select owner， segment_name， sum（bytes / 1024 / 1024） mb

from dba_segments group by owner， segment_name） d

where b。sql_id = c。sql_id

and b。child_number = c。child_number

and b。object_owner = ’SCOTT‘

and a。sql_id = b。sql_id

and a。child_number = b。child_number

and a。operation = ’MERGE JOIN‘

and a。id = b。parent_id

and a。options = ’CARTESIAN‘

and b。object_owner = d。owner

and b。object_name = d。segment_name

order by 4 desc；

92、查詢走了錯誤的排序合併連線的SQL

select c。sql_id， c。sql_text，

d。owner， d。segment_name， d。mb

from v$sql_plan a， v$sql_plan b， v$sql c，

（select owner， segment_name， sum（bytes / 1024 / 1024） mb

from dba_segments

group by owner， segment_name） d

where a。sql_id = b。sql_id

and a。child_number = b。child_number

and b。operation = ’SORT‘

and b。options = ’JOIN‘

and b。access_predicates like ’%”=“%‘

and a。parent_id = b。id

and a。object_owner = ’SCOTT‘

and b。sql_id = c。sql_id

and b。child_number = c。child_number

and a。object_owner = d。owner

and a。object_name = d。segment_name

order by 4 desc；

93、查詢LOOP套LOOP的PSQL

with x as

（select / * +materialize * / owner， name， type， line， text， rownum rn

from dba_source

where （upper（text） like ’%END%LOOP%‘ or upper（text） like ’%FOR%LOOP%‘））

select a。owner， a。name， a。type

from x a， x b

where （（upper（a。text） like ’%END%LOOP%‘

and upper（b。text） like ’%END%LOOP%‘

and a。rn + 1 = b。rn） or （upper（a。text）

like ’%FOR%LOOP%‘ and upper（b。text） like ’%FOR%LOOP%‘ a

nd a。rn + 1 = b。rn））

and a。owner = b。owner

and a。name = b。name

and a。type = b。type

and a。owner = ’SCOTT‘；

93、查詢走了低選擇性索引的SQL

select c。sql_id， c。sql_text，

b。index_name， e。table_name，

trunc（d。num_distinct / e。num_rows * 100， 2） selectivity，

d。num_distinct， e。num_rows

from v$sql_plan a，

（select * from

（select index_owner， index_name， table_owner， table_name， column_name，

count（*） over（partition by index_owner， index_name， table_owner， table_name） cnt

from dba_ind_columns）

where cnt = 1） b， v$sql c， dba_tab_col_statistics d， dba_tables e

where a。object_owner = b。index_owner and a。object_name = b。index_name

and b。index_owner = ’SCOTT‘

and a。access_predicates is not null

and a。sql_id = c。sql_id

and a。child_number = c。child_number

and d。owner = e。owner

and d。table_name = e。table_name

and b。table_owner = e。owner

and b。table_name = e。table_name

and d。column_name = b。column_name

and d。table_name = b。table_name

and d。num_distinct / e。num_rows < 0。1；

94、查詢抓出可以建立組合索引的SQL

SELECT a。sql_id， a。sql_text，

f。table_name， c。size_mb，e。column_name，

round（e。num_distinct / f。num_rows * 100， 2） selectivity

FROM v$sql a， v$sql_plan b，

（ SELECT owner， segment_name， sum（bytes / 1024 / 1024） size_mb

FROM dba_segments

GROUP BY owner， segment_name） c， dba_tab_col_statistics e， dba_tables f

WHERE a。sql_id = b。sql_id

AND a。child_number = b。child_number AND b。object_owner = c。owner

AND b。object_name = c。segment_name

AND e。owner = f。owner

AND e。table_name = f。table_name

AND b。object_owner = f。owner

AND b。object_name = f。table_name

AND instr（b。filter_predicates， e。column_name） > 0

AND （e。num_distinct / f。num_rows） > 0。1

AND c。owner = ’SCOTT‘

AND b。operation = ’TABLE ACCESS‘

AND b。options = ’BY INDEX ROWID‘

AND e。owner = ’SCOTT‘

ORDER BY 4 desc；

95、查詢出可以建立組合索引的SQL（回表只訪問少數字段）

select a。sql_id， a。sql_text，

d。table_name， REGEXP_COUNT（b。projection， ’］‘） || ’/‘ || d。column_cnt column_cnt， c。size_mb， b。FILTER_PREDICATES filter

from v$sql a， v$sql_plan b，

（select owner， segment_name， sum（bytes / 1024 / 1024） size_mb

from dba_segments

group by owner， segment_name） c，

（select owner， table_name， count（*） column_cnt

from dba_tab_cols

group by owner， table_name） d

where a。sql_id = b。sql_id

and a。child_number = b。child_number

and b。object_owner = c。owner

and b。object_name = c。segment_name

and b。object_owner = d。owner

and b。object_name = d。table_name

and c。owner = ’SCOTT‘ and b。operation = ’TABLE ACCESS‘

and b。options = ’BY INDEX ROWID‘

and REGEXP_COUNT（b。projection， ’］‘） / d。column_cnt < 0。25

order by 5 desc；