SQL 优化入门 （25 页）

1、25-1,2020/7/4,Oracle9i/10gSQL编程优化,赵元杰2006.04.14Z,25-2,2020/7/4,内容提要,SQL语句书写方法SQL语句优劣比较FROM子句后表的顺序WHERE子句后的连接顺序索引的使用问题,25-3,2020/7/4,SQL语句写法要点,不用“*”来代替所有列名SELECT语句中可以用*来列出该表的所有的列名Oracle系统会通过查询数据字典来将*”转换成该表的所有列名(动态问题)用TRUNCATE代替DELETE全表删除可直接用TRUNCATE完整性下多用COMMIT语句:保护数据的信息释放程序语句获得的锁redologbuffer中的空间ORA

2、CLE为管理上述3种资源中的内部花费,25-4,2020/7/4,SQL的优劣比较,SQL语句高效与低效下面SQL低效：下面语句高效：,SELECTTAB_NAMEFROMTABLESWHERETAB_NAME=(SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION=604)ANDDB_VER=(SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604),两个语句都访问同一表,SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECTTAB_NAME,DB_VER)FROMTAB_CO

3、LUMNSWHEREVERSION=604),25-5,2020/7/4,SQL的优劣比较,NOTEXISTS替代NOTIN下面SQL低效：下面语句高效：,SELECTFROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT=A);,Notin不是好方法,SELECTFROMEMPEWHERENOTEXISTS(SELECTXFROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=A);,25-6,2020/7/4,SQL的优劣比较,NOTIN与MINUS下面SQL可能存在性能问题：下面语句可得到较

4、好的性能：,select*fromAwhere(A.key1,A.key2)notin(selectB.key1,B.key2fromB),select*fromAwhere(A.key1,A.key2)in(selectA.key1,A.key2fromAminusselectB.key1,B.key2fromB),25-7,2020/7/4,SQL的优劣比较,EXISTS替代IN下面SQL低效：下面语句高效：,SELECT*FROMEMP-基础表WHEREEMPNO0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=MELB);,用in不是好方法,SELEC

5、T*FROMEMP-基础表WHEREEMPNO0ANDEXISTS(SELECTXFROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=MELB);,25-8,2020/7/4,SQL的优劣比较,采用表连接的方式比EXISTS更有效率下面SQL低效：下面语句高效：,SELECTENAMEFROMEMPEWHEREEXISTS(SELECTXFROMDEPTWHEREDEPT_NO=E.DEPT_NOANDDEPT_CAT=A);,用EXISTS不是好方法,SELECTENAMEFROMDEPTD,EMPEWHEREE.DEPT_NO=D.DEPT_NOANDDEP

6、T_CAT=A;-带IN的关联子查询是多余的，因为IN子句和子查询中相关的操作一样。,25-9,2020/7/4,SQL的优劣比较,用EXISTS代替DISTINCT下面SQL低效：下面语句高效：,SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO;,用DISTINCT不是好方法,SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECTXFROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);,25-10,2020/7/4,FROM多表的驱动顺序,A是

7、大表而B是小表，则应该用小表驱动大表；取消大表的索引；,低效：SELECT*FROMA,BWHEREA.STATE=B.STATE高效：SELECT*FROMA,BWHEREA.STATE=B.STATE|,25-11,2020/7/4,FROM多表的顺序,ORACLE的解析器按照从右到左的顺序FROM子句中写在最后的表(基础表drivingtable)将被最先处理；选择记录条数最少的表作为基础表,表TAB1有16,384数据行表TAB2有10个数据行方法1(最佳)：选择TAB2作为基础表：selectcount(*)fromtab1,tab2where.方法1(不佳)：选择TAB2作为基础表

8、：selectcount(*)fromtab2,tab1where.,25-12,2020/7/4,FROM多表的顺序,FROM子句后三个表的情况：,例如：EMP表描述了LOCATION表和CATEGORY表的交集。则EMP的顺序是关键：(这里EMP是交叉表)例1(效率高)：SELECT*FROMLOCATIONL,CATEGORYC,EMPEWHEREE.EMP_NOBETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN;例2(效率低)：SELECT*FROMEMPE,LOCATIONL,CATEGORYCWHEREE.CAT_NO=C.

9、CAT_NOANDE.LOCN=L.LOCNANDE.EMP_NOBETWEEN1000AND2000;,25-13,2020/7/4,WHERE子句的连接顺序,采用自下而上的顺序解析WHERE子句；表之间连接必须写在其他WHERE条件之前；那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。,例1(低效)：SELECTFROMEMPEWHERESAL50000ANDJOB=MANAGERAND2550000ANDJOB=MANAGER;,25-14,2020/7/4,连接的次序,如果A表与B表存在多对一或一对一的关系，则下面的语句有区别：下面SQL低效：下面语句高效：,Select

10、A.*fromA,BwhereA.CITY=B.CITY,Select*fromAwhereA.CITYin(selectB.CityfromB),25-15,2020/7/4,索引的使用-了解访问表索引,了解所访问表的索引列查询DBA_INDEXES与DBA_IND_COLUMNS在SELECT语句的WHERE子句中指定索引列Oracle9i可监视索引是否被使用监视索引是否被使用:查询索引使用情况:删除不使用的索引:,ALTERINDEX.MONITORINGUSAGE;,SELECT*FROMV$OBJECT_USAGE;,DROPINDEX.;,25-16,2020/7/4,索引的使用-

11、复合索引的主列,采用多列索引叫复合索引。CREATEINDEXcomp_indONtab1(x,y,z);复合列变为x,xy和xyz几个部分查询语句中带有：where.and来使用复合键,SELECT语句：SELECTFROMWHEREX=xANDY=yANDZ=z;,25-17,2020/7/4,索引的使用-索引列,采用多列索引叫复合索引。如果希望使用索引，则不要索引列上加表达式：,低效：SELECT*fromAwhereSALARY+1000=:NEWSALARY高效：SELECT*fromAwhereSALARY=:NEWSALARY-1000,25-18,2020/7/4,索引的使用-

12、查询索引详细信息,程序员要了解所访问表对应的索引从USER_TABLES采用GET_DDL来展现,SetLONG9999SELECTSelectdbms_metadata.get_ddl(|chr(39)|object_type|chr(39)|,|chr(39)|object_name|chr(39)|)fromdual;FROMUSER_TABLESWHERETABLE_NAME=EMP;,25-19,2020/7/4,索引的使用-不被使用的原因,索引不使用主要是加了表达式在索引列上加任何表达式，如：应避免类似下面用法：!=(notequalto)Like%SA%,SELECT*fromA

13、wheresubstr(name,1,3)=Wil,25-20,2020/7/4,索引的使用-不被使用的原因,程序员在WHERE子句加了索引对应列名，但系统可能由于下面原因索引不使用索引在索引列上加了函数，如：在where子句中用了NOT的SQL语句，如：使用ISNULL或ISNOTNULL的SQL语句；对索引列进行内部转换的SQL语句.,selectname,addressfrompersonswhereupper(name)=JOHE;,selectname,address,cityfromperonswherecitynotin(BOSTON,NEWYORK);,25-21,2020/7

14、/4,索引的使用-有时不用索引,索引的使用不是任何时候都需要小表就没有必要采用增加表达式来避免使用索引,SELECT*FROMAWHERESALARY+0=10000ANDDEPT=ITSELECT*FROMAWHEREEMP_SEX|=M,25-22,2020/7/4,索引的使用-索引的NULL,不要设置索引列的为NULL如果索引列为数字类型，应设置为0,不够良好：SELECT*FROMAWHERENUMBERISNOTNULL良好：(通常反应快些)SELECT*FROMAWHERENUMBER0,25-23,2020/7/4,索引的使用-索引与排序,使用索引连接如果A表在lastname,firstname索引，则：,不够良好：SELECT*