資料庫SQL效能最佳化（一），資料庫sql效能最佳化

資料庫SQL效能最佳化（一），資料庫sql效能最佳化

substr(hbs_bh,1,4)=’5400’，最佳化處理：hbs_bh like ‘5400%’trunc(sk_rq)=trunc(sysdate)， 最佳化處理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)

進行了顯式或隱式的運算的欄位不能進行索引，如：

ss_df+20>50，最佳化處理：ss_df>30 ‘X’ || hbs_bh>’X5400021452’，最佳化處理：hbs_bh>’5400021542’ sk_rq+5=sysdate，最佳化處理：sk_rq=sysdate-5 hbs_bh=5401002554，最佳化處理：hbs_bh=’5401002554’，註：此條件對hbs_bh 進行隱式的to_number轉換，因為hbs_bh欄位是字元型。

條件內包括了多個本表的欄位運算時不能進行索引，如：ys_df>cx_df，無法進行最佳化 qc_bh || kh_bh=’5400250000’，最佳化處理：qc_bh=’5400’ and kh_bh=’250000’

DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID) FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO)

SELECT  TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM  TAB_COLUMNS  WHERE  VERSION = 604)

（高效）SELECT * FROM  EMP (基礎資料表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB') 

 （低效）SELECT  * FROM  EMP (基礎資料表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')

SELECT  EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 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.8 ORDER BY  4 DESC;

(低效): 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);

低效： SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 高效: SELECT … FROM DEPT WHERE SAL > 25000/12;

高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”  

低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

低效: 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'

提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結果但第二個明顯就快了許多.

低效: SELECT JOB , AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' 

高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB

公眾號推薦：

公眾號：VOA英語每日一聽

號: voahk01

可長按掃碼關注，謝謝