SQL优化常用方法15

1. 减少对表的查询
   在含有子查询的SQL语句中,要特别注意减少对表的查询.

例如: 低效 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)
Update 多个Column 例子:
低效: 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;