Oracle常用SQL技巧收藏.doc

Go to start of metadata 1.SELECT子句中避免使用“*”當(dāng)你想在SELECT子句中列出所有的COLUMN時,使用動態(tài)SQL列引用是一個方便的方法.不幸的是,這是一個非常低效的方法.實際上,ORACLE在解析的過程中,會將“”依次轉(zhuǎn)換成所有的列名,這個工作是通過查詢數(shù)據(jù)字典完成的,這意味著將耗費更多的時間.2.使用DECODE函數(shù)來減少處理時間使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.例如:Sql代碼SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE SMITH%; SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE SMITH%; 你可以用DECODE函數(shù)高效地得到相同結(jié)果:Sql代碼 SELECT COUNT(decode(dept_no, 0020, x, NULL) d0020_count, COUNT(decode(dept_no, 0030, x, NULL) d0030_count, SUM(decode(dept_no, 0020, sal, NULL) d0020_sal, SUM(decode(dept_no, 0030, sal, NULL) d0030_sal FROM emp WHERE ename LIKE smith%;類似的,DECODE函數(shù)也可以運用于GROUPBY和ORDERBY子句中.3.刪除重復(fù)記錄最高效的刪除重復(fù)記錄方法(因為使用了ROWID)DELETE FROM emp e WHERE e.rowid (SELECT MIN(x.rowid)FROM emp x WHERE x.emp_no = e.emp_no);4.用TRUNCATE替代DELETE當(dāng)刪除表中的記錄時，在通常情況下，回滾段(rollbacksegments)用來存放可以被恢復(fù)的信息，如果你沒有COMMIT事務(wù)，ORACLE會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)，而當(dāng)運用TRUNCATE時,回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時間也會很短.5.計算記錄條數(shù)和一般的觀點相反,count(*)比count(1)稍快，當(dāng)然如果可以通過索引檢索，對索引列的計數(shù)仍舊是最快的.例如COUNT(EMPNO)6.用Where子句替換HAVING子句避免使用HAVING子句，HAVING只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾，這個處理需要排序、總計等操作，如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷,例如:Sql代碼-低效SELECT region，avg(log_size) FROM location GROUP BY regionHAVING region sydney AND region perth-高效SELECT region，avg(log_size) FROM location WHERE region sydney AND region perth GROUP BY region7.用EXISTS替代IN在許多基于基礎(chǔ)表的查詢中,為了滿足一個條件,往往需要對另一個表進(jìn)行聯(lián)接.在這種情況下,使用EXISTS(或NOTEXISTS)通常將提高查詢的效率.-低效SELECT * FROM emp WHERE empno 0 AND deptno IN (SELECT deptno FROM dept WHERE loc = melb);-高效:SELECT * FROM emp WHERE empno 0 AND EXISTS (SELECT x FROM dept WHERE dept.deptno = emp.deptno AND loc = melb);8.用NOTEXISTS替代NOTIN在子查詢中，NOTIN子句將執(zhí)行一個內(nèi)部的排序和合并.無論在哪種情況下，NOTIN都是最低效的(因為它對子查詢中的表執(zhí)行了一個全表遍歷).為了避免使用NOTIN，我們可以把它改寫成外連接(OuterJoins)或NOTEXISTS.例如:SELECTFROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT=A);Sql代碼-為了提高效率改寫為: (方法一: 高效)SELECT FROM emp a, dept b WHERE a.dept_no = b.dept AND b.dept_no IS NULL AND b.dept_cat = a;- (方法二: 最高效)SELECT FROM emp e WHERE NOT EXISTS (SELECT x FROM dept d WHERE d.dept_no = e.dept_no AND dept_cat = a);9.用EXISTS替換DISTINCT當(dāng)提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT.一般可以考慮用EXIST替換例如:-低效:SELECT DISTINCT dept_no, dept_name FROM dept d, emp e WHERE d.dept_no = e.dept_no;-高效:SELECT dept_no, dept_name FROM dept d WHERE EXISTS (SELECT x FROM emp e WHERE e.dept_no = d.dept_no);-EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果.10.用索引提高效率索引是表的一個概念部分，用來提高檢索數(shù)據(jù)的效率，實際上ORACLE使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu)，通常通過索引查詢數(shù)據(jù)比全表掃描要快，當(dāng)ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時，ORACLE優(yōu)化器將使用索引，同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率，另一個使用索引的好處是，它提供了主鍵(primarykey)的唯一性驗證，除了那些LONG或LONGRAW數(shù)據(jù)類型,你可以索引幾乎所有的列.通常,在大型表中使用索引特別有效.當(dāng)然,你也會發(fā)現(xiàn),在掃描小表時,使用索引同樣能提高效率，雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價.索引需要空間來存儲，也需要定期維護(hù)，每當(dāng)有記錄在表中增減或索引列被修改時，索引本身也會被修改，這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5次的磁盤I/O，因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應(yīng)時間變慢注：定期的重構(gòu)索引是有必要的.11.避免在索引列上使用計算WHERE子句中，如果索引列是函數(shù)的一部分，優(yōu)化器將不使用索引而使用全表掃描.舉例：-低效：SELECT FROM dept WHERE sal * 12 25000;-高效:SELECT FROM dept WHERE sal 25000 / 12;12.用=替代-如果D