ORACLE 查詢SQL優化

1 使用EXPLAIN PLAN

????????使用EXPLAIN PLAN查看查詢的執行計劃，這可以幫助你理解查詢是如何被Oracle執行的。基于執行計劃，你可以確定是否存在索引缺失、不必要的全表掃描等問題。

????????以下是幾種使用EXPLAIN PLAN的方法：

? ? ? ? ?使用`EXPLAIN PLAN FOR`:

????????你可以在SQL*Plus、SQL Developer或其他Oracle工具中運行以下命令：

EXPLAIN PLAN FOR SELECT * FROM your_table WHERE your_column = 'some_value';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

????????第一條命令為指定的SQL查詢生成執行計劃，但它不會直接顯示結果。第二條命令使用DBMS_XPLAN.DISPLAY來格式化并顯示執行計劃。

????????格式化執行計劃輸出:

????????使用DBMS_XPLAN.DISPLAY時，你可以使用各種選項來格式化輸出。例如，要顯示詳細的執行計劃并包括統計信息，你可以這樣做：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ALLSTATS PREDICATE LAST COST'));

????????這將顯示執行計劃的每個步驟，包括它們的成本、估計的行數、使用的索引等。

FORMAT還有一些附加的選項可用于定制化輸出行為，使用中可以通過逗號和空格分隔來聲明多個關鍵字，同時可以使用”+”和”-”符號來包含或排除相應的顯示元素，這些附加的選項在官方文檔中可以查到：

1.ROWS – 顯示被優化器估算的記錄的行號
2.BYTES – 顯示優化器估算的字節數
3.COST – 顯示優化器計算的成本信息
4.PARTITION – 顯示分區的分割信息
5.PARALLEL – 顯示并行執行信息
6.PREDICATE – 顯示謂語
7.PROJECTION – 顯示列投影部分(每一行的那些列被傳遞給其父列已經這些列的大小)
8.ALIAS – 顯示查詢塊名稱已經對象別名
9.REMOTE – 顯示分布式查詢信息
10.NOTE – 顯示注釋
11.IOSTATS – 顯示游標執行的IO統計信息
12.MEMSTATS – 為內存密集運算如散列聯結，排序，或一些類型的位圖運算顯示內存管理統計信息
13.ALLSTATS – 與'IOSTATS MEMSTATS'等價
14.LAST – 顯示最后執行的執行計劃統計信息，默認顯示為ALL類型，并且可以累積。

????????查看特定SQL ID的執行計劃:

????????如果你知道SQL語句的SQL ID（通常可以在AWR報告或V$SQL視圖中找到），你可以使用DBMS_XPLAN.DISPLAY_CURSOR來查看其執行計劃：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', 'ALLSTATS'));

????????請注意替換'your_sql_id'為你想要查看的SQL語句的ID。

????????清除之前的執行計劃:

????????如果你之前為多個查詢生成了執行計劃，并希望清除它們以便為新的查詢生成執行計劃，你可以使用以下命令：

EXECUTE DBMS_XPLAN.CLEAR_PLAN_TABLE();

????????這將清除PLAN_TABLE$（存儲執行計劃的地方）中的內容。注意，這可能會影響到其他會話中正在查看的執行計劃。

????????在SQL*Plus中使用AUTOTRACE:

????????在SQL*Plus中，你可以使用SET AUTOTRACE ON來自動顯示查詢的執行計劃：

SET AUTOTRACE ON

EXPLAIN SELECT * FROM your_table WHERE your_column = 'some_value';

????????這將執行查詢并顯示其執行計劃。

????????使用SQL Developer等圖形化執行計劃

????????如果你使用Oracle SQL Developer，你可以直接點擊查詢結果上方的“執行計劃”選項卡來查看圖形化的執行計劃（或者快捷鍵，如PL/SQL快捷鍵為“F5”）。SQL Developer會自動為你生成并顯示執行計劃。????????

2 索引優化：

確保在經常用于搜索、排序和連接的列上創建了索引。
避免在索引列上使用函數或表達式，這可能導致索引失效。
定期審查和維護索引，刪除不必要的索引，因為索引也會占用存儲空間并影響插入、更新和刪除操作的性能。
多表查詢關聯時，關聯條件字段數據類型不一致時，可能會導致索引失效，盡量保證關聯的索引字段數據類型保持一致。

3 SQL語句優化

盡量減少SELECT語句中選擇的列數，只選擇需要的列。
使用IN代替多個OR條件。
使用連接（JOIN）代替子查詢，當可能時。
避免在WHERE子句中使用非SARGable（Search ARGumentable）表達式，這些表達式不能有效地利用索引。
避免SELECT *，使用SELECT*?會返回表中的所有列，這可能會消耗更多的I/O資源。只選擇需要的列。
通過創建適當的索引和優化查詢來減少全表掃描。
對于大型查詢，考慮使用Oracle的并行查詢功能來加速查詢的執行。這可以在多個CPU或磁盤上并行處理查詢。

當用到子查詢時，子查詢中的查詢條件最好使用主查詢中結果集最少的表，避免大結果集匹配子查詢；當可能時，可在條件中加入 rownum=1，如：

--使用
select (select itable.col0 from itable where itable.col1 = table1.col1) as resultfrom table1, table2where table1.col2 = 'xx'and table1.col1 = table2.col1;--不使用
select (select itable.col0 from itable where itable.col1 = table2.col1) as resultfrom table1, table2where table1.col2 = 'xx'and table1.col1 = table2.col1;--使用
select (select itable.col0from itablewhere itable.col1 = table1.col1and rownum = 1) as resultfrom table1, table2where table1.col2 = 'xx'and table1.col1 = table2.col1;--不使用
select (select itable.col0 from itable where itable.col1 = table1.col1) as resultfrom table1, table2where table1.col2 = 'xx'and table1.col1 = table2.col1;

4 分區

????????對于非常大的表，考慮使用分區來提高查詢性能。分區允許你將數據分成更小的、更易于管理的片段，每個片段可以獨立存儲、備份和索引。

Oracle提供了多種分區方法，包括范圍分區、列表分區、散列分區和復合分區等。以下是各種分區方法的簡要說明：

范圍分區：根據表中某個列的值的范圍將數據劃分為不同的分區。例如，可以根據日期范圍將銷售數據劃分為不同的季度或年份分區。
- 優點：適用于可以按自然范圍進行分區的表，如時間序列數據。
- 示例：PARTITION BY RANGE (sales_date) (PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD')))
列表分區：明確指定要包含在特定分區中的值的列表。與范圍分區不同，列表分區支持基于非連續或不規則的值進行分區。
- 優點：適用于需要明確控制行如何映射到分區的場景。
- 示例：PARTITION BY LIST (region) (PARTITION reg1 VALUES ('North', 'South'), PARTITION reg2 VALUES ('East', 'West'))
散列分區：根據指定的散列函數將數據均勻地分布到不同的分區中。這通常用于確保數據的均勻分布和負載平衡。
- 優點：自動將數據均勻分布到不同的分區中，無需手動指定范圍或值。
- 示例：PARTITION BY HASH (employee_id) (PARTITIONS 4)
復合分區：結合使用范圍分區和列表分區或散列分區的方法。通常首先使用范圍分區將數據劃分為較大的邏輯部分，然后在每個范圍內使用列表或散列分區進行更細粒度的劃分。
- 優點：結合了范圍分區和列表/散列分區的優點，提供了更靈活的數據劃分方式。