高效sql

sql执行顺序是自下而上，基于这条规则，如果是多表查询，表内记录多的靠近from，记录少的出现在from后所有表的后面。where后面的条件限制，表之间的连接必须写在其他WHERE条件之前,从下到上，限制越来越严（判断标准可以根据查询表的次数和查询表的记录数决定），where最后面的限制语句应该是最大限度缩小查询范围的语句

1、查询

低效

          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)

    高效

          SELECT TAB_NAME

          FROM TABLES

          WHERE (TAB_NAME,DB_VER)

 = ( SELECT TAB_NAME,DB_VER)

                   FROM TAB_COLUMNS

                   WHERE VERSION = 604)

 

2、修改

　低效：

           UPDATE EMP

           SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),

              SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)

           WHERE EMP_DEPT = 0020;

　高效：

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

 

 

识别“低效执行”的sql:

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;