减少对表的查询

Chinaab

减少对表的查询

在含有子查询的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;

job.haibo

多谢楼主分享

dsp_chenfx

OK{:soso_e128:}

liufuchao

感谢经验分享～～

davidma

呵呵，这个也是有一个优化中的抉择