高效sql必殺技
原作者：robbinson_ 0612
原文地址：http://blog.csdn.net/robinson_0612/article/details/7406672
No SQL,No cost. SQL語句是造成資料庫開銷最大的部分。而不良SQL寫法直接導致資料庫系統效能下降的情形比比皆是。那麼如何才能稱得
上高效的SQL語句呢？一是查詢優化器為當前的SQL語句生成最佳的執行計劃，保證資料讀寫使用最佳路徑；二是設定合理的物理儲存結構，如表
的型別，欄位的順序，欄位的資料型別等。本文主要描述如何編寫高效的SQL語句並給出示例。下面的描述主要分為三個部分，一是編寫高效SQL
語句，二是使用索引提高查詢效能的部分，三是總結部分。
一、編寫高效SQL語句
1) 選擇最有效的表名順序(僅適用於RBO模式)
ORACLE的解析器總是按照從右到左的順序處理FROM子句中的表名，因此FROM子句中最後的一個表將作為驅動表被優先處理。當FROM子句
存在多個表的時候，應當考慮將表上記錄最少的那個表置於FROM的最右端作為基表。Oracle會首先掃描基表(FROM子句中最後的那個表)並對
記錄進行排序，然後掃描第二個表(FROM子句中最後第二個表)，最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合併。如
果有3個以上的表連線查詢， 那就需要選擇交叉表（intersection table）作為基礎表，交叉表是指那個被其他表所引用的表。
下面的例子使用最常見的scott或hr模式下的表進行演示
表 EMP 有14條記錄
表 DEPT 有4條記錄
SELECT /*+ rule */ COUNT( * ) FROM emp, dept; --高效的寫法
scott@CNMMBO> set autotrace traceonly stat;
scott@CNMMBO> SELECT /*+ rule */ COUNT( * ) FROM emp, dept;
Elapsed: 00:00:00.14
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SELECT /*+ rule */ COUNT( * ) FROM dept, emp; --低效的寫法
scott@CNMMBO> SELECT /*+ rule */ COUNT( * ) FROM dept, emp;
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
105 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2) select 查詢中避免使用'*'
當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 '*' 是一個方便的方法.不幸的是,這是一個非常低效的方法.實際
上，ORACLE在解析的過程中， 會將 '*' 依次轉換成所有的列名， 這個工作是通過查詢資料字典完成的， 這意味著將耗費更多的時間。
注：本文中的例子出於簡化演示而使用了select * ,生產環境應避免使用.
3) 減少訪問資料庫的次數
每當執行一條SQL語句,Oracle 需要完成大量的內部操作,象解析SQL語句,估算索引的利用率,繫結變數, 讀資料塊等等.由此可
見，減少訪問資料庫的次數，實際上是降低了資料庫系統開銷
-->下面通過3種方式來獲得僱員編號為7788與7902的相關資訊
-->方式 1 (最低效):
select ename,job,sal from emp where empno=7788;
select ename,job,sal from emp where empno=7902;
-->方式 2 (次低效):
-->下面使用了引數遊標來完成，每傳遞一次引數則需要對錶emp訪問一次，增加了I/O
DECLARE
CURSOR C1(E_NO NUMBER) IS
SELECT ename, job, sal
FROM emp
WHERE empno = E_NO;
BEGIN
OPEN C1 (7788);
FETCH C1 INTO …, …, …;
..
OPEN C1 (7902);
FETCH C1 INTO …, …, …;
CLOSE C1;
END;
-->方式 3 (最高效)
SELECT a.ename
, a.job
, a.sal
, b.ename
, b.job
, b.sal
FROM emp a, emp b
WHERE a.empno = 7788 OR b.empno = 7902;
注意：在SQL*Plus,SQL*Forms和Pro*C中重新設定ARRAYSIZE引數,可以增加每次資料庫訪問的檢索資料量,建議值為200.
4) 使用DECODE函式來減少處理時間
-->使用decode函式可以避免重複掃描相同的行或重複連線相同的表
select count(*),sum(sal) from emp where deptno=20 and ename like 'SMITH%';
select count(*),sum(sal) from emp where deptno=30 and ename like 'SMITH%';
-->通過使用decode函式一次掃描即可完成所有滿足條件記錄的處理
SELECT COUNT( DECODE( deptno, 20, 'x', NULL ) ) d20_count
, COUNT( DECODE( deptno, 30, 'x', NULL ) ) d30_count
, SUM( DECODE( deptno, 20, sal, NULL ) ) d20_sal
, SUM( DECODE( deptno, 30, sal, NULL ) ) d30_sal
FROM emp
WHERE ename LIKE 'SMITH%';
類似的，DECODE函式也可以運用於GROUP BY 和ORDER BY子句中。
5) 整合簡單，無關聯的資料庫訪問
-->如果你有幾個簡單的資料庫查詢語句，你可以把它們整合到一個查詢中以提高效能(即使它們之間沒有關係)
-->整合前
SELECT name
FROM emp
WHERE empno = 1234;
SELECT name
FROM dept
WHERE deptno = 10;
SELECT name
FROM cat
WHERE cat_type = 'RD';
-->整合後
SELECT e.name, d.name, c.name
FROM cat c
, dpt d
, emp e
, dual x
WHERE NVL( 'X', x.dummy ) = NVL( 'X', e.ROWID(+) )
AND NVL( 'X', x.dummy ) = NVL( 'X', d.ROWID(+) )
AND NVL( 'X', x.dummy ) = NVL( 'X', c.ROWID(+) )
AND e.emp_no(+) = 1234
AND d.dept_no(+) = 10
AND c.cat_type(+) = 'RD';
-->從上面的SQL語句可以看出，儘管三條語句被整合為一條，效能得以提高，然可讀性差，此時應權衡效能與代價
6) 刪除重複記錄
-->通過使用rowid來作為過濾條件，效能高效
DELETE FROM emp e
WHERE e.ROWID > (SELECT MIN( x.ROWID )
FROM emp x
WHERE x.empno = e.empno);
7) 使用truncate 代替 delete
-->通常情況下,任意記錄的刪除需要在回滾段構造刪除前映象以實現回滾(rollback).對於未提交的資料在執行rollback之後,Oracle會生成
-->等價SQL語句去恢復記錄(如delete,則生成對應的insert語句;如insert則生成對應的delete;如update,則是同時生成delete和insert
-->使用truncate命令則是執行DDL命令,不產生任何回滾資訊,直接格式化並釋放高水位線.故該語句效能高效.由於不能rollback,因此慎用.
8) 儘量多使用COMMIT(COMMIT應確保事務的完整性)
-->只要有可能，在程式中儘量多使用COMMIT,這樣程式的效能得到提高,需求也會因為COMMIT所釋放的資源而減少
-->COMMIT所釋放的資源：
-->1.回滾段上用於恢復資料的資訊
-->2.釋放語句處理期間所持有的鎖
-->3.釋放redo log buffer佔用的空間(commit將redo log buffer中的entries 寫入到聯機重做日誌檔案)
-->4.ORACLE為管理上述3種資源中的內部開銷
9) 計算記錄條數
-->一般的情況下,count(*)比count(1)稍快.如果可以通過索引檢索,對索引列的計數是最快的,因為直接掃描索引即可,例如COUNT(EMPNO)
-->實際情況是經測試上述三種情況並無明顯差異.
10) 用Where子句替換HAVING子句
-->儘可能的避免having子句，因為HAVING 子句是對檢索出所有記錄之後再對結果集進行過濾。這個處理需要排序，總計等操作
-->通過WHERE子句則在分組之前即可過濾不必要的記錄數目，從而減少聚合的開銷
-->低效:
SELECT deptno, AVG( sal )
FROM emp
GROUP BY deptno
HAVING deptno = 20;
scott@CNMMBO> SELECT deptno, AVG( sal )
2 FROM emp
3 GROUP BY deptno
4 HAVING deptno= 20;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-->高效:
SELECT deptno, AVG( sal )
FROM emp
WHERE deptno = 20
GROUP BY deptno;
scott@CNMMBO> SELECT deptno, AVG( sal )
2 FROM emp
3 WHERE deptno = 20
4 GROUP BY deptno;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
11) 最小化表查詢次數
-->在含有子查詢的SQL語句中，要特別注意減少對錶的查詢
-->低效:
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'Marketing')
AND manager_id = (SELECT manager_id
FROM departments
WHERE department_name = 'Marketing');
-->高效:
SELECT *
FROM employees
WHERE ( department_id, manager_id ) = (SELECT department_id, manager_id
FROM departments
WHERE department_name = 'Marketing')
-->類似更新多列的情形
-->低效:
UPDATE employees
SET job_id = ( SELECT MAX( job_id ) FROM jobs ), salary = ( SELECT AVG( min_salary ) FROM jobs )
WHERE department_id = 10;
-->高效:
UPDATE employees
SET ( job_id, salary ) = ( SELECT MAX( job_id ), AVG( min_salary ) FROM jobs )
WHERE department_id = 10;
12) 使用表別名
-->在多表查詢時，為所返回列使用表別名作為字首以減少解析時間以及那些相同列歧義引起的語法錯誤
13) 用EXISTS替代IN
在一些基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下,使用EXISTS(或NOT EXISTS)通常
將提高查詢的效率.
-->低效:
SELECT *
FROM emp
WHERE sal > 1000
AND deptno IN (SELECT deptno
FROM dept
WHERE loc = 'DALLAS')
-->高效:
SELECT *
FROM emp
WHERE empno > 1000
AND EXISTS
(SELECT 1
FROM dept
WHERE deptno = emp.deptno AND loc = 'DALLAS')
14) 用NOT EXISTS替代NOT IN
在子查詢中,NOT IN子句引起一個內部的排序與合併.因此,無論何時NOT IN子句都是最低效的,因為它對子查詢中的表執行了一個全表
遍歷.為避免該情形,應當將其改寫成外部連線(OUTTER JOIN)或適用NOT EXISTS
-->低效:
SELECT *
FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE loc = 'DALLAS');
-->高效:
SELECT e.*
FROM emp e
WHERE NOT EXISTS
(SELECT 1
FROM dept
WHERE deptno = e.deptno AND loc = 'DALLAS');
-->最高效(儘管下面的查詢最高效,並不推薦使用,因為列loc使用了不等運算,當表dept資料量較大,且loc列存在索引的話,則此時索引失效)
SELECT e.*
FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.loc <> 'DALLAS'
15) 使用表連線替換EXISTS
一般情況下，使用表連線比EXISTS更高效
-->低效:
SELECT *
FROM employees e
WHERE EXISTS
(SELECT 1
FROM departments
WHERE department_id = e.department_id AND department_name = 'IT');
-->高效:
SELECT * -->經測試此寫法SQLplus下比上面的寫法多一次邏輯讀，而在Toad下兩者結果一致
FROM employees e INNER JOIN departments d ON d.department_id = e.department_id
WHERE d.department_name = 'IT';
16) 用EXISTS替換DISTINCT
對於一對多關係表資訊查詢時(如部門表和僱員表)，應避免在select 子句中使用distinct,而使用exists來替換
-->低效:
SELECT DISTINCT e.department_id, d.department_name
FROM departments d INNER JOIN employees e ON d.department_id = e.department_id;
-->高效:
SELECT d.department_id,department_name
from departments d
WHERE EXISTS
(SELECT 1
FROM employees e
WHERE d.department_id=e.department_id);
EXISTS 使查詢更為迅速，因為RDBMS核心模組將在子查詢的條件一旦滿足後，立刻返回結果
-->經測試此寫法SQLplus下比上面的寫法多一次邏輯讀，而在Toad下兩者結果一致
17) 使用 UNION ALL 替換 UNION(如果有可能的話)
當SQL語句需要UNION兩個查詢結果集時，這兩個結果集合會以UNION-ALL的方式被合併， 然後在輸出最終結果前進行排序。
如果用UNION ALL替代UNION， 這樣排序就不是必要了。 效率就會因此得到提高。
注意：
UNION ALL會輸出所有的結果集,而UNION則過濾掉重複記錄並對其進行排序.因此在使用時應考慮業務邏輯是否允許當前的結果集存在重複現象
尋找低效的SQL語句
-->下面的語句主要適用於從檢視v$sqlarea中獲得當前執行下且耗用buffer_gets較多的SQL語句
SELECT executions
, disk_reads
, buffer_gets
, ROUND( ( buffer_gets
- disk_reads )
/ buffer_gets, 2 )
hit_ratio
, 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.80
ORDER BY 4 DESC;
18) 儘可能避免使用函式，函式會導致更多的 recursive calls
二、合理使用索引以提高效能
索引依賴於表而存在，是真實表的一個縮影，類似於一本書的目錄，通過目錄以更快獲得所需的結果。Oracle使用了一個複雜的自平衡
B資料結構。即任意記錄的DML操作將打破索引的平衡，而定期重構索引使得索引重新獲得平衡。通常，通過索引查詢資料比全表掃描更高效。
任意的DQL或DML操作，SQL優化引擎優先使用索引來計算當前操作的成本以生成最佳的執行計劃。一旦使用索引操出引數optimizer_index_cost_adj
設定的值才使用全表掃描。同樣對於多表連線使用索引也可以提高效率。同時索引也提供主鍵（primary key）的唯一性驗證。
除了那些LONG或LONG RAW資料型別,你可以索引幾乎所有的列.通常,在大型表中使用索引特別有效.當然,你也會發現,在掃描小表時,使用索
引同樣能提高效率。
雖然使用索引能得到查詢效率的提高,但是索引需要空間來儲存,需要定期維護.尤其是在有大量DML操作的表上，任意的DML操作都將引起索
引的變更這意味著每條記錄的INSERT ， DELETE ， UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的儲存空間和處理，
那些不必要的索引反而會使查詢反應時間變慢。
DML操作使用索引上存在碎片而失去高度均衡,因此定期的重構索引是有必要的.
1) 避免基於索引列的計算
where 子句中的謂詞上存在索引，而此時基於該列的計算將使得索引失效
-->低效:
SELECT employee_id, first_name
FROM employees
WHERE employee_id + 10 > 150; -->索引列上使用了計算，因此索引失效，走全表掃描方式
-->高效:
SELECT employee_id, first_name
FROM employees
WHERE employee_id > 160; -->走索引範圍掃描方式
例外情形
上述規則不適用於SQL中的MIN和MAX函式
hr@CNMMBO> SELECT MAX( employee_id ) max_id
2 FROM employees
3 WHERE employee_id
4 + 10 > 150;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1481384439
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | FIRST ROW | | 5 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| EMP_EMP_ID_PK | 5 | 20 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
2) 避免在索引列上使用NOT運算或不等於運算(<>,!=)
通常，我們要避免在索引列上使用NOT或<>,兩者會產生在和在索引列上使用函式相同的影響。 當ORACLE遇到NOT或不等運算時，他就會停止
使用索引轉而執行全表掃描。
-->低效:
SELECT *
FROM emp
WHERE NOT ( deptno = 20 ); -->實際上NOT ( deptno = 20 )等同於deptno <> 20,即deptno <>同樣會限制索引
-->高效:
SELECT *
FROM emp
WHERE deptno > 20 OR deptno < 20;
-->儘管此方式可以替換且實現上述結果，但依然走全表掃描，如果是單純的 > 或 < 運算，則此時為索引範圍掃描
需要注意的是，在某些時候， ORACLE優化器會自動將NOT轉化成相對應的關係操作符
其次如果是下列運算子進行NOT運算,依然有可能選擇走索引, 僅僅除了NOT = 之外,因為 NOT = 等價於 <>
“NOT >” to <=
“NOT >=” to <
“NOT <” to >=
“NOT <=” to >
來看一個實際的例子
hr@CNMMBO> SELECT *
2 FROM employees
3 where not employee_id<100; -->索引列上使用了not，但是該查詢返回了所有的記錄,即107條，因此此時選擇走全表掃描
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 | -->執行計劃中使用了走全表掃描方式
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID">=100) -->檢視這裡的謂詞資訊被自動轉換為 >= 運算子
hr@CNMMBO> SELECT *
2 FROM employees
3 where not employee_id<140; -->此例與上面的語句相同,僅僅是查詢範圍不同返回67條記錄,而此時選擇了索引範圍掃描
67 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 603312277
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68 | 4624 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 68 | 4624 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 68 | | 1 (0)| 00:00:01 | -->索引範圍掃描方式
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID">=140)
3) 用UNION 替換OR(適用於索引列)
通常情況下,使用UNION 替換WHERE子句中的OR將會起到較好的效果.基於索引列使用OR使得優化器傾向於使用全表掃描，而不是掃描索引.
注意,以上規則僅適用於多個索引列有效。 如果有column沒有被索引， 查詢效率可能會因為你沒有選擇OR而降低。
-->低效:
SELECT deptno, dname
FROM dept
WHERE loc = 'DALLAS' OR deptno = 20;
-->高效:
SELECT deptno, dname
FROM dept
WHERE loc = 'DALLAS'
UNION
SELECT deptno, dname
FROM dept
WHERE deptno = 30
-->經測試,由於資料量較少，此時where子句中的謂詞上都存在索引列時，兩者效能相當.
-->假定where子句中存在兩列
scott@CNMMBO> create table t6 as select object_id,owner,object_name from dba_objects where owner='SYS' and rownum<1001;
scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SCOTT' and rownum<6;
scott@CNMMBO> create index i_t6_object_id on t6(object_id);
scott@CNMMBO> create index i_t6_owner on t6(owner);
scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SYSTEM' and rownum<=300;
scott@CNMMBO> commit;
scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T6',cascade=>true);
scott@CNMMBO> select owner,count(*) from t6 group by owner;
OWNER COUNT(*)
-------------------- ----------
SCOTT 5
SYSTEM 300
SYS 1000
scott@CNMMBO> select * from t6 where owner='SCOTT' and rownum<2;
OBJECT_ID OWNER OBJECT_NAME
---------- -------------------- --------------------
69450 SCOTT T_TEST
scott@CNMMBO> select * from t6 where object_id=69450 or owner='SYSTEM';
301 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 238853296
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=69450)
4 - filter(LNNVL("OBJECT_ID"=69450))
5 - access("OWNER"='SYSTEM')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
11383 bytes sent via SQL*Net to client
712 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
301 rows processed
scott@CNMMBO> select * from t6 where owner='SYSTEM' or object_id=69450;
301 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 238853296
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"=69450)
4 - filter(LNNVL("OBJECT_ID"=69450))
5 - access("OWNER"='SYSTEM')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
11383 bytes sent via SQL*Net to client
712 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
301 rows processed
scott@CNMMBO> select * from t6
2 where object_id=69450
3 union
4 select * from t6
5 where owner='SYSTEM';
301 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 370530636
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 301 | 7224 | 7 (72)| 00:00:01 |
| 1 | SORT UNIQUE | | 301 | 7224 | 7 (72)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T6 | 300 | 7200 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OBJECT_ID"=69450)
6 - access("OWNER"='SYSTEM')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
11383 bytes sent via SQL*Net to client
712 bytes received via SQL*Net from client
22 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
301 rows processed
-->從上面的統計資訊可知，consistent gets由46下降為7，故當where子句中謂詞上存在索引時，使用union替換or更高效
-->即使當列object_id與owner上不存在索引時，使用union仍然比or更高效(在Oracle 10g R2與Oracle 11g R2測試)
4) 避免索引列上使用函式
-->下面是一個來自實際生產環境的例子
-->表acc_pos_int_tbl上business_date列存在索引,由於使用了SUBSTR函式，此時索引失效，使用全表掃描
SELECT acc_num
, curr_cd
, DECODE( '20110728'
, ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728', 'YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0
, adj_credit_int_lv1_amt
+ adj_credit_int_lv2_amt
- adj_debit_int_lv1_amt
- adj_debit_int_lv2_amt )
AS interest
FROM acc_pos_int_tbl
WHERE SUBSTR( business_date, 1, 6 ) = SUBSTR( '20110728', 1, 6 ) AND business_date <= '20110728';
-->改進的辦法
SELECT acc_num
, curr_cd
, DECODE( '20110728'
, ( SELECT TO_CHAR( LAST_DAY( TO_DATE( '20110728', 'YYYYMMDD' ) ), 'YYYYMMDD' ) FROM dual ), 0
, adj_credit_int_lv1_amt
+ adj_credit_int_lv2_amt
- adj_debit_int_lv1_amt
- adj_debit_int_lv2_amt )
AS interest
FROM acc_pos_int_tbl acc_pos_int_tbl
WHERE business_date >= TO_CHAR( LAST_DAY( ADD_MONTHS( TO_DATE( '20110728', 'yyyymmdd' ), -1 ) )
+ 1, 'yyyymmdd' )
AND business_date <= '20110728';
-->下面的例子雖然沒有使用函式，但字串連線同樣導致索引失效
-->低效:
SELECT account_name, amount
FROM transaction
WHERE account_name
|| account_type = 'AMEXA';
-->高效:
SELECT account_name, amount
FROM transaction
WHERE account_name = 'AMEX' AND account_type = 'A';
5) 比較不匹配的資料型別
-->下面的查詢中business_date列上存在索引,且為字元型,這種
-->低效:
SELECT *
FROM acc_pos_int_tbl
WHERE business_date = 20090201；
Execution Plan
----------------------------------------------------------
Plan hash value: 2335235465
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37516 | 2857K| 106K (1)| 00:21:17 |
|* 1 | TABLE ACCESS FULL| ACC_POS_INT_TBL | 37516 | 2857K| 106K (1)| 00:21:17 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("BUSINESS_DATE")=20090201) -->這裡可以看到產生了型別轉換
-->高效:
SELECT *
FROM acc_pos_int_tbl
WHERE business_date = '20090201'
6) 索引列上使用 NULL 值
IS NULL和IS NOT NULL會限制索引的使用,因為資料中沒有值等於NULL值,即便是NULL值也不等於NULL值.且NULL值不儲存在於索引之中
因此應儘可能避免在索引類上使用NULL值
SELECT acc_num
, pl_cd
, order_qty
, trade_date
FROM trade_client_tbl
WHERE input_date IS NOT NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 901462645
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TRADE_CLIENT_TBL | 1 | 44 | 15 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
alter table trade_client_tbl modify (input_date not null);
不推薦使用的查詢方式
SELECT * FROM table_name WHERE col IS NOT NULL
SELECT * FROM table_name WHERE col IS NULL
推薦使用的方式
SELECT * FROM table_name WHERE col >= 0 --儘可能的使用 =, >=, <=, like 等運算子
-->Author: Robinson Cheng
-->Blog: http://blog.csdn.net/robinson_0612
三、總結
1、儘可能最小化基表資料以及中間結果集(通過過濾條件避免後續產生不必要的計算與聚合)
2、為where子句中的謂詞資訊提供最佳的訪問路徑(rowid訪問,索引訪問)
3、使用合理的SQL寫法來避免過多的Oracle內部開銷以提高效能
4、合理的使用提示以提高表之間的連線來提高連線效率(如避免迪卡爾集,將不合理的巢狀連線改為hash連線等)
四、更多參考
dbms_xplan之display_cursor函式的使用
相關文章
- 前端面試必殺技：this是個啥？前端面試
- 產品經理課程的必殺技
- 開發的必殺技：Git 的分支管理Git
- OLAP：實現高效BI分析的必備技術
- 面試必殺技，講一講Spring中的迴圈依賴面試Spring
- 使用Python處理複雜字串的必殺技-re.sub()的妙用Python字串
- 必殺技：當報錯資訊看不出原因時，怎麼辦？
- SQL必知必會筆記（上）SQL筆記
- SQL必知必會筆記（下）SQL筆記
- SQL 必知必會 50 題（1 - 5）SQL
- 前端面試必殺技：原型、原型鏈以及繼承（一張圖搞定面試）前端面試原型繼承
- 初學Web前端開發，學會這幾個必殺技，薪資爆表！Web前端
- 乾貨！運維必知的9種高效能技術架構運維架構
- 批次殺執行某條sql的sessionSQLSession
- 常用技術必知必會
- 必知必會——SQL語句基本語法整理SQL
- SQL必知必會-陳暘-極客時間SQL
- 擔心資料洩露嗎？網路安全防護必殺技瞭解一下！
- 制勝下一個十年：重塑未來組織的五大“必殺技”
- 如何寫出高效能SQLSQL
- SQL Server中的IO效能殺手Forwarded recordSQLServerForward
- leetcode我們必知必會的SQL面試題LeetCodeSQL面試題
- 測試人員必會SQL命令SQL
- SQL 必須宣告標量變數SQL變數
- Windows下基礎免殺技術Windows
- 如何將bug殺死在搖籃裡？解讀壓測必經之路JMeter分散式的技術點JMeter分散式
- SQL SERVER--系統隱形殺手—阻塞與等待SQLServer
- sql優化 面試必問【簡答】SQL優化面試
- 學習MyBatis必知必會（7）~註解開發、動態SQLMyBatisSQL
- 街機遊戲中最具創新的波動必殺技，撐起了格鬥遊戲的半壁江山遊戲
- 2020年日本網友評選最酷動漫必殺技排名 天馬流星拳只排第5
- 藉助AI助手如何高效排查SQL問題AISQL
- VSCode 必裝的 10 個高效開發外掛VSCode
- SQL最佳化必懂概念（一）：基數SQL
- 高效的PDF文字提取技術
- 【免殺技術】Tomcat記憶體馬-FilterTomcat記憶體Filter
- [原創]免殺技術有一套（免殺方法大集結）(Anti-AntiVirus)
- Oracle 高效能SQL引擎剖析--SQL優化與調優機制詳解OracleSQL優化
- 高併發下秒殺商品，必須知道的9個細節