Oracle SQL优化简介.ppt

SQL TUNING AUTHOR : JOHNSON.QIAO 　 2008/09/25 TRAINING　FOR　MIS　PROGRAMMER Agenda SUMMARY OPTIMIZER OPTIMIZER MODE EXPLAIN PLAN TABLE JOIN INDEX HINT OPERATOR FUNCTION OTHERS Summary Summary Summary Summary 設計 Optimizer Optimizer mode Optimizer Optimizer mode Optimizer Optimizer mode Optimizer Optimizer mode Explain plan Explain plan Table join Table join Table join Table join Table join Table join Table join Table join Table join Table join Table join Table join Table join Table join Table join Table join Table join Table join Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Index Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Hint Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Operator Function Function Function Others Others Others Others Others Others The end 1﹒ SELECT JOB,AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB=SALESMAN‘ 2﹒ SELECT JOB,AVG(SAL) FROM EMP WHERE JOB=SALESMAN GROUP BY JOB 把使用聚集函數統計資料時﹐查詢條件應盡可能的寫在WHERE子句中﹐避免使用HAVING﹐HAVING 中的條件一般用於對一些集合函數的比較﹐這樣執行效率會更高 如上例﹐SQL2將优于SQL1 ORDER　BY操作符 1﹒ORDER　BY后面可以接任何欄位﹐包括函數﹐但是衹要是要求排序﹐一般查詢速度都會明顯減慢﹐所以沒有必要一般不要使用ORDER　BY 2﹒ORDER　BY引用索引的條件非常嚴格﹐只在下列兩种情況下才會引用索引﹕第一﹐ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序﹔第二﹐ORDER BY中所有的列必須定義為非空 3﹒ORDER　BY后的欄位如果有建INDEX﹐但是此欄位可以為空﹐則可以用WHERE替換ORDER　BY來优化 1﹒ SELECT * FROM PS_EMPLOYEE ORDER BY GRADE 2﹒ SELECT * FROM PS_EMPLOYEE WHERE GRADE ‘ 在此實例中SQL2明顯好于SQL1﹐SQL1將會調用全表掃描﹐而 SQL2可以引用GRADE上的INDEX﹐使用WHERE替換ORDER　BY需要小心﹐具体情況具体分析 DISTINCT操作符 DISTINCT操作也是相當耗費資源的﹐當提交一個包含一對多表資訊(比如部門表和雇員表)的查詢時﹐避免在SELECT子句中使用DISTINCT.﹐一般可以考慮用EXIST替換 1﹒SELECT DISTINCT PD.DEPTNO,PD.SHORT_NAME FROM PS_EMPLOYEE PE,PS_DEPARTMENT PD