SQL高效與低效

2020 年 4 月 10 日
筆記

SQL高效與低效

SQL 架構

1. SELECT ....  2. FROM Table1大,Table2中,Table3小  3. whereTable1=條件一  4.  andTable2=條件二  5.  and table3=table2  6.  and table2=table1

PS: 如Where 有使用到 Subquery 盡量改寫為虛擬 Table

低效:

1. SELECT ....  2. FROM Table1,Table2,Table3  3. whereTable1=條件一  4.  andTable2=條件二  5.  and table2 =(select.....  6.  fromTable4  7.  where條件四)  8.  and table3=table2  9.  and table2=table1

高效:

1. SELECT ....  2. FROM Table1,Table2,(select.....  3.  fromTable3,Table4  4.  where條件四  5.  andTable3=Table4)Table3  6. whereTable1=條件一  7.  andTable2=條件二  8.  and table3=table2  9.  and table2=table1

(1) 低效:

1. SELECT …  2. FROM DEPT  3. WHERE SAL *12>25000;

高效:

1. SELECT …  2. FROM DEPT  3. WHERE SAL >25000/12;

(2) 高效:

1. SELECT *  2. FROM EMP  3. WHERE DEPTNO >=4

低效:

1. SELECT *  2. FROM EMP  3. WHERE DEPTNO >3

(3) 高效:

1. SELECT LOC_ID , LOC_DESC , REGION  2. FROM LOCATION  3. WHERE LOC_ID =10  4. UNION  5. SELECT LOC_ID , LOC_DESC , REGION  6. FROM LOCATION  7. WHERE REGION ='MELBOURNE'

低效:

1. SELECT LOC_ID , LOC_DESC , REGION  2. FROM LOCATION  3. WHERE LOC_ID =10 OR REGION ='MELBOURNE'

(4) 低效: (索引失效)

1. SELECT …  2. FROM DEPARTMENT  3. WHERE DEPT_CODE IS NOT NULL;

高效: (索引有效)

1. SELECT …  2. FROM DEPARTMENT  3. WHERE DEPT_CODE >=0;

不使用索引:

1. SELECT ACCOUNT_NAME  2. FROM TRANSACTION  3. WHERE AMOUNT !=0;

使用索引:

1. SELECT ACCOUNT_NAME  2. FROM TRANSACTION  3. WHERE AMOUNT >0;

(5) 低效:執行約156.3秒

1. SELECT …  2. FROM EMP E  3. WHERE SAL >50000  4. AND JOB ='MANAGER'  5. AND 25<(SELECT COUNT(*) FROM EMP  6. WHERE MGR=E.EMPNO);

高效:執行約10.6秒

1. SELECT …  2. FROM EMP E  3. WHERE 25<(SELECT COUNT(*) FROM EMP  4. WHERE MGR=E.EMPNO)  5. AND SAL >50000  6. AND JOB ='MANAGER';

(6) 使用DECODE函數來減少處理時間使用DECODE函數可以避免重複描述相同記錄或重複連結相同的表例如:

1. SELECT COUNT(*)，SUM(SAL)  2. FROM EMP  3. WHERE DEPT_NO =0020  4.   AND ENAME LIKE 'SMITH%';  5.  6. SELECT COUNT(*)，SUM(SAL)  7. FROM EMP  8. WHERE DEPT_NO =0030  9.   AND ENAME LIKE 'SMITH%';

你可以用DECODE函數更高效

1. SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT,  2.        COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT,  3.        SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,  4.        SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL  5. FROM EMP  6. WHERE ENAME LIKE 'SMITH%';

· 使用日期

當使用日期時,需要注意如果有超過5位小數加到日期上,這個日期會到下一天例如:

1. SELECT TO_DATE('01-JAN-93'+.99999)  2. FROM DUAL;  3. Returns:  4. '01-JAN-93 23:59:59'  1. SELECT TO_DATE('01-JAN-93'+.999999)  2. FROM DUAL;  3. Returns:  4. '02-JAN-93 00:00:00'

· 以筆數多的放在前面

表 TAB1 16,384 筆，表 TAB2 1 筆選擇TAB2作為基礎表 (最好的方法)

1. select count(*)from tab1,tab2

執行約0.96秒

選擇TAB2作為基礎表 (不佳的方法)

1. select count(*)from tab2,tab1

執行約26.09秒

· 不同表的效能

原表

1. SELECT NAME  2. FROM EMP  3. WHERE EMP_NO =1234;  4. SELECT NAME  5. FROM DPT  6. WHERE DPT_NO =10;  7. SELECT NAME  8. FROM CAT  9. WHERE CAT_TYPE ='RD';

高效

1. SELECT E.NAME,D.NAME,C.NAME  2. FROM CAT C,DPT D,EMP E,DUAL X  3. WHERE NVL('X',X.DUMMY)= NVL('X',E.ROWID(+))  4.   AND NVL('X',X.DUMMY)= NVL('X',D.ROWID(+))  5.   AND NVL('X',X.DUMMY)= NVL('X',C.ROWID(+))  6.   AND E.EMP_NO(+)=1234  7.   AND D.DEPT_NO(+)=10  8.   AND C.CAT_TYPE(+)='RD';

缺點程式可攜性低

· 刪除重覆記錄

1. DELETE FROM EMP E  2. WHERE E.ROWID >(SELECT MIN(X.ROWID)  3. FROM EMP X  4. WHERE X.EMP_NO = E.EMP_NO);

· 減少對錶的查詢

低效

1. SELECT TAB_NAME  2. FROM TABLES  3. WHERE TAB_NAME =  4.  (SELECT TAB_NAME  5.      FROM TAB_COLUMNS  6.      WHERE VERSION =604)  7.   AND DB_VER=  8.  (SELECT DB_VER  9.      FROM TAB_COLUMNS  10.      WHERE VERSION =604)

高效

1. SELECT TAB_NAME  2. FROM TABLES  3. WHERE (TAB_NAME,  4.        DB_VER)=  5.  (SELECT TAB_NAME,  6.             DB_VER)  7.   FROM TAB_COLUMNS WHEREVERSION =604)

· Update 多Column 例子:

低效:

1. UPDATE EMP  2. SET EMP_CAT =  3.  (SELECT MAX(CATEGORY)  4.    FROM EMP_CATEGORIES),  5.     SAL_RANGE =  6.  (SELECT MAX(SAL_RANGE)  7.    FROM EMP_CATEGORIES)  8. WHERE EMP_DEPT =0020;

高效:

1. UPDATE EMP  2. SET (EMP_CAT,  3.      SAL_RANGE)=  4.  (SELECT MAX(CATEGORY),  5.           MAX(SAL_RANGE)  6.    FROM EMP_CATEGORIES)  7. WHERE EMP_DEPT =0020;  · 使用EXISTS(或NOT EXISTS)通常能提高查詢的效率.

低效:

1. SELECT *  2. FROM EMP  3. WHERE EMPNO >0  4.   AND DEPTNO IN  5.  (SELECT DEPTNO  6.      FROM DEPT  7.      WHERE LOC ='MELB')

高效:

1. SELECT *  2. FROM EMP  3. WHERE EMPNO >0  4.   AND EXISTS  5.  (SELECT 'X'  6.      FROM DEPT  7.      WHERE DEPT.DEPTNO = EMP.DEPTNO  8.        AND LOC ='MELB')

· 用NOT EXISTS替代NOT IN,為了避免使用NOT IN ,我們可以把它改成外部連接(Outer Joins)或NOT EXISTS.

例如:

1. SELECT …  2. FROM EMP  3. WHERE DEPT_NO NOT IN  4.  (SELECT DEPT_NO  5.      FROM DEPT  6.      WHERE DEPT_CAT='A');

(方法一: 高效)

1. SELECT ….  2. FROM EMP A,  3.      DEPT B  4. WHERE A.DEPT_NO = B.DEPT(+)  5.   AND B.DEPT_NO IS NULL  6.   AND B.DEPT_CAT(+)='A'

(方法二: 最高效)

1. SELECT ….  2. FROM EMP E  3. WHERE NOT EXISTS  4.  (SELECT 'X'  5.      FROM DEPT D  6.      WHERE D.DEPT_NO = E.DEPT_NO  7.        AND DEPT_CAT ='A');

· 用EXISTS替代DISTINCT

低效:

1. SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPTD,EMP E  2. WHERE D.DEPT_NO = E.DEPT_NO

高效:

1. SELECT DEPT_NO,DEPT_NAME FROM DEPTD  2. WHERE EXISTS ( SELECT 『X』 FROM EMP E  3. WHERE E.DEPT_NO = D.DEPT_NO);

· 計算目前執行SQL的效能狀況,以下為找出最低效的SQL

1. SELECT EXECUTIONS,  2.        DISK_READS,  3.        BUFFER_GETS,  4.        ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,  5.        ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,  6.        SQL_TEXT  7. FROM V$SQLAREA  8. WHERE EXECUTIONS>0  9.   AND BUFFER_GETS >0  10.   AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS <0.8  11. ORDER BY 4 DESC;

· Outer Join 外部連接

1. select d.deptname, e.ename from dept d, emp e where d.empno *= e.enum;

2. select d.deptname,e.ename from dept d, emp e where d.empno = e.enum(+);

· 檢查 table 或 view 是否存在於 Oracle

1. SELECT COUNT (TABLE_NAME)  2. FROM all_tables  3. WHERE UPPER (TABLE_NAME)= UPPER ('PathologyTest')  4.   SELECT COUNT (view_name)  5.   FROM all_views WHEREUPPER (view_name)= UPPER ('v_PathologyTest')

Tags: