登陆

关系数据库性能问题

15. 减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.
  例如: 
     Slow:
          SELECT TAB_NAME
          FROM TABLES
          WHERE TAB_NAME = ( SELECT TAB_NAME 
                                FROM TAB_COLUMNS
                                WHERE VERSION = 604)
          AND DB_VER= ( SELECT DB_VER 
                           FROM TAB_COLUMNS
                           WHERE VERSION = 604)

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

     Update 多个Column 例子:
     Slow
           UPDATE EMP
           SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
              SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
           WHERE EMP_DEPT = 0020;

     Fast
           UPDATE EMP
           SET (EMP_CAT, SAL_RANGE)
 = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
 FROM EMP_CATEGORIES)
           WHERE EMP_DEPT = 0020;

 

8. 使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
例如:
   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函数高效地得到相同结果
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函数也可以运用于GROUP BY 和ORDER BY子句中.

« 上一篇 | 下一篇 »