40. ORACLE内部操作
當執行查詢時,ORACLE采用了内部的操作. 下表顯示了幾種重要的内部操作.
| ORACLE Clause | 内部操作 |
| ORDER BY | SORT ORDER BY |
| UNION | UNION-ALL |
| MINUS | MINUS |
| INTERSECT | INTERSECT |
| DISTINCT,MINUS,INTERSECT,UNION | SORT UNIQUE |
| MIN,MAX,COUNT | SORT AGGREGATE |
| GROUP BY | SORT GROUP BY |
| ROWNUM | COUNT or COUNT STOPKEY |
| Queries involving Joins | SORT JOIN,MERGE JOIN,NESTED LOOPS |
| CONNECT BY | CONNECT BY |
41. 用UNION-ALL 替換UNION ( 如果有可能的話)
當SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合并, 然後在輸出最終結果前進行排序.
如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會是以得到提高.
舉例:
低效：
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’
高效:
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’
需要注意的是，UNION ALL 将重複輸出兩個結果集合中相同記錄. 是以各位還是
要從業務需求分析使用UNION ALL的可行性.
UNION 将對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊記憶體. 對于這
塊記憶體的優化也是相當重要的. 下面的SQL可以用來查詢排序的消耗量
Select substr(name,1,25) "Sort Area Name",
substr(value,1,15) "Value"
from v$sysstat
where name like 'sort%'
42. 使用提示(Hints)
對于表的通路,可以使用兩種Hints.
FULL 和 ROWID
FULL hint 告訴ORACLE使用全表掃描的方式通路指定表.
例如:
SELECT *
FROM EMP
WHERE EMPNO = 7893;
ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作通路表.
通常, 你需要采用TABLE ACCESS BY ROWID的方式特别是當通路大表的時候, 使用這種方式, 你需要知道ROIWD的值或者使用索引.
如果一個大表沒有被設定為緩存(CACHED)表而你希望它的資料在查詢結束是仍然停留
在SGA中,你就可以使用CACHE hint 來告訴優化器把資料保留在SGA中. 通常CACHE hint 和 FULL hint 一起使用.
例如:
SELECT *
FROM WORK;
索引hint 告訴ORACLE使用基于索引的掃描方式. 你不必說明具體的索引名稱
例如:
SELECT LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
在不使用hint的情況下, 以上的查詢應該也會使用索引,然而,如果該索引的重複值過多而你的優化器是CBO, 優化器就可能忽略索引. 在這種情況下, 你可以用INDEX hint強制ORACLE使用該索引.
ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等.
使用hint , 表示我們對ORACLE優化器預設的執行路徑不滿意,需要手工修改.
這是一個很有技巧性的工作. 我建議隻針對特定的,少數的SQL進行hint的優化.
對ORACLE的優化器還是要有信心(特别是CBO)
43. 用WHERE替代ORDER BY
ORDER BY 子句隻在兩種嚴格的條件下使用索引.
ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序.
ORDER BY中所有的列必須定義為非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.
例如:
表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
非唯一性的索引(DEPT_TYPE)
低效: (索引不被使用)
SELECT DEPT_CODE
FROM DEPT
ORDER BY DEPT_TYPE
EXPLAIN PLAN:
SORT ORDER BY
TABLE ACCESS FULL
高效: (使用索引)
SELECT DEPT_CODE
FROM DEPT
WHERE DEPT_TYPE > 0
EXPLAIN PLAN:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
ORDER BY 也能使用索引! 這的确是個容易被忽視的知識點. 我們來驗證一下:
SQL> select * from emp order by empno;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (FULL SCAN) OF 'EMPNO' (UNIQUE)
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)