SELECT子句中避免使用 *
當你想在SELECT子句中列出所有的COLUMN時使用動態SQL列引用 * 是一個方便的方法不幸的是這是一個非常低效的方法 實際上ORACLE在解析的過程中 會將* 依次轉換成所有的列名 這個工作是通過查詢數據字典完成的 這意味著將耗費更多的時間
使用DECODE函數來減少處理時間
使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表 例如:
Sql代碼
SELECT COUNT(*)SUM(SAL) FROM EMP WHERE DEPT_NO = AND ENAME LIKE SMITH%;
SELECT COUNT(*)SUM(SAL) FROM EMP WHERE DEPT_NO = AND ENAME LIKE SMITH%;
你可以用DECODE函數高效地得到相同結果:
Sql代碼
SELECT COUNT(DECODE(DEPT_NOXNULL)) D_COUNT
COUNT(DECODE(DEPT_NOXNULL)) D_COUNT
SUM(DECODE(DEPT_NOSALNULL)) D_SAL
SUM(DECODE(DEPT_NOSALNULL)) D_SAL
FROM EMP WHERE ENAME LIKE SMITH%;
類似的DECODE函數也可以運用於GROUP BY 和ORDER BY子句中
刪除重復記錄
最高效的刪除重復記錄方法 ( 因為使用了ROWID)
Sql代碼
DELETE FROM EMP E WHERE EROWID > (SELECT MIN(XROWID) FROM EMP X WHERE XEMP_NO = EEMP_NO);
用TRUNCATE替代DELETE
當刪除表中的記錄時在通常情況下回滾段(rollback segments ) 用來存放可以被恢復的信息如果你沒有COMMIT事務ORACLE會將數據恢復到刪除之前的狀態(准確地說是恢復到執行刪除命令之前的狀況)而當運用TRUNCATE時 回滾段不再存放任何可被恢復的信息當命令運行後數據不能被恢復因此很少的資源被調用執行時間也會很短
計算記錄條數
和一般的觀點相反 count(*) 比count()稍快 當然如果可以通過索引檢索對索引列的計數仍舊是最快的 例如 COUNT(EMPNO)
用Where子句替換HAVING子句
避免使用HAVING子句HAVING 只會在檢索出所有記錄之後才對結果集進行過濾這個處理需要排序總計等操作如果能通過WHERE子句限制記錄的數目那就能減少這方面的開銷 例如:
Sql代碼
低效
SELECT REGIONAVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != SYDNEY AND REGION != PERTH
高效
SELECT REGIONAVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY ND REGION != PERTH GROUP BY REGION
用EXISTS替代IN
在許多基於基礎表的查詢中為了滿足一個條件往往需要對另一個表進行聯接在這種情況下 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率
Sql代碼
低效
SELECT * FROM EMP WHERE EMPNO > AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB)
高效:
SELECT * FROM EMP WHERE EMPNO > AND EXISTS (SELECT X FROM DEPT WHERE DEPTDEPTNO = EMPDEPTNO AND LOC = MELB)
用NOT EXISTS替代NOT IN
在子查詢中NOT IN子句將執行一個內部的排序和合並 無論在哪種情況下NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷) 為了避免使用NOT IN我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS 例如:
SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A);
Sql代碼
為了提高效率改寫為: (方法一: 高效)
SELECT …FROM EMP ADEPT B WHERE ADEPT_NO = BDEPT(+) AND BDEPT_NO IS NULL AND BDEPT_CAT(+) = A
(方法二: 最高效)
SELECT …FROM EMP E WHERE NOT EXISTS (SELECT X FROM DEPT D WHERE DDEPT_NO = EDEPT_NO AND DEPT_CAT = A);
用EXISTS替換DISTINCT
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時避免在SELECT子句中使用DISTINCT 一般可以考慮用EXIST替換
例如:
Sql代碼
低效:
SELECT DISTINCT DEPT_NODEPT_NAME FROM DEPT DEMP E WHERE DDEPT_NO = EDEPT_NO
高效:
SELECT DEPT_NODEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE EDEPT_NO = DDEPT_NO);
EXISTS 使查詢更為迅速因為RDBMS核心模塊將在子查詢的條件一旦滿足後立刻返回結果
用索引提高效率
索引是表的一個概念部分用來提高檢索數據的效率實際上ORACLE使用了一個復雜的自平衡Btree結構通常通過索引查詢數據比全表掃描要快當ORACLE找出執行查詢和Update語句的最佳路徑時 ORACLE優化器將使用索引 同樣在聯結多個表時使用索引也可以提高效率另一個使用索引的好處是它提供了主鍵(primary key)的唯一性驗證除了那些LONG或LONG RAW數據類型 你可以索引幾乎所有的列 通常 在大型表中使用索引特別有效 當然你也會發現 在掃描小表時使用索引同樣能提高效率雖然使用索引能得到查詢效率的提高但是我們也必須注意到它的代價 索引需要空間來存儲也需要定期維護每當有記錄在表中增減或索引列被修改時索引本身也會被修改這意味著每條記錄的INSERT DELETE UPDATE將為此多付出 次的磁盤I/O 因為索引需要額外的存儲空間和處理那些不必要的索引反而會使查詢反應時間變慢
注定期的重構索引是有必要的
避免在索引列上使用計算
WHERE子句中如果索引列是函數的一部分優化器將不使用索引而使用全表掃描 舉例
Sql代碼
低效
SELECT …FROM DEPT WHERE SAL * > ;
高效:
SELECT … FROM DEPT WHERE SAL > /;
用>=替代>
Sql代碼
如果DEPTNO上有一個索引
高效
SELECT * FROM EMP WHERE DEPTNO >=
低效
SELECT * FROM EMP WHERE DEPTNO >
兩者的區別在於 前者DBMS將直接跳到第一個DEPT等於的記錄而後者將首先定位到DEPTNO=的記錄並且向前掃描到第一個DEPT大於的記錄
通配符% 的使用
不使用索引
select * from emp where name like %A
使用索引
select * from emp where name like A%
From:http://tw.wingwit.com/Article/program/Oracle/201311/18722.html
