0.Oracle SQL 优化入门 (25 页)

25-12020/2/22Oracle9i/10gSQL编程优化赵元杰[e-mail]25-22020/2/22内容提要SQL语句书写方法SQL语句优劣比较FROM子句后表的顺序WHERE子句后的连接顺序索引的使用问题25-32020/2/22SQL语句写法要点不用“*”来代替所有列名SELECT语句中可以用’*’来列出该表的所有的列名Oracle系统会通过查询数据字典来将’*”转换成该表的所有列名(动态问题)用TRUNCATE代替DELETE全表删除可直接用TRUNCATE完整性下多用COMMIT语句:保护数据的信息释放程序语句获得的锁redologbuffer中的空间ORACLE为管理上述3种资源中的内部花费25-42020/2/22SQL的优劣比较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_COLUMNSWHEREVERSION=604)25-52020/2/22SQL的优劣比较NOTEXISTS替代NOTIN下面SQL低效：下面语句高效：SELECT…FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT=’A’);Notin不是好方法SELECT…FROMEMPEWHERENOTEXISTS(SELECT‘X’FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A’);25-62020/2/22SQL的优劣比较NOTIN与MINUS下面SQL可能存在性能问题：下面语句可得到较好的性能：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-72020/2/22SQL的优劣比较EXISTS替代IN下面SQL低效：下面语句高效：SELECT*FROMEMP--基础表WHEREEMPNO0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB’);用in不是好方法SELECT*FROMEMP--基础表WHEREEMPNO0ANDEXISTS(SELECT‘X’FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB’);25-82020/2/22SQL的优劣比较采用表连接的方式比EXISTS更有效率下面SQL低效：下面语句高效：SELECTENAMEFROMEMPEWHEREEXISTS(SELECT‘X’FROMDEPTWHEREDEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A’);用EXISTS不是好方法SELECTENAMEFROMDEPTD,EMPEWHEREE.DEPT_NO=D.DEPT_NOANDDEPT_CAT=‘A’;--带IN的关联子查询是多余的，因为IN子句和子查询中相关的操作一样。25-92020/2/22SQL的优劣比较用EXISTS代替DISTINCT下面SQL低效：下面语句高效：SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO;用DISTINCT不是好方法SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X’FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);25-102020/2/22FROM多表的驱动顺序A是大表而B是小表，则应该用小表驱动大表；取消大表的索引；低效：SELECT*FROMA,BWHEREA.STATE=B.STATE高效：SELECT*FROMA,BWHEREA.STATE=B.STATE||''25-112020/2/22FROM多表的顺序ORACLE的解析器按照从右到左的顺序FROM子句中写在最后的表(基础表drivingtable)将被最先处理；选择记录条数最少的表作为基础表表TAB1有16,384数据行表TAB2有10个数据行方法1(最佳)：选择TAB2作为基础表：selectcount(*)fromtab1,tab2where...方法1(不佳)：选择TAB2作为基础表：selectcount(*)fromtab2,tab1where...25-122020/2/22FROM多表的顺序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.CAT_NOANDE.LOCN=L.LOCNANDE.EMP_NOBETWEEN1000AND2000;25-132020/2/22WHERE子句的连接顺序采用自下而上的顺序解析WHERE子句；表之间连接必须写在其他WHERE条件之前；那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。例1(低效)：SELECT…FROMEMPEWHERESAL50000ANDJOB=‘MANAGER’AND25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);例2(高效)：SELECT…FROMEMPEWHERE25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL50000ANDJOB=‘MANAGER’;25-142020/2/22连接的次序如果A表与B表存在多对一或一对一的关系，则下面的语句有区别：下面SQL低效：下面语句高效：SelectA.*fromA,BwhereA.CITY=B.CITYSelect*fromAwhereA.CITYin(selectB.CityfromB)25-152020/2/22索引的使用-了解访问表索引了解所访问表的索引列查询DBA_INDEXES与DBA_IND_COLUMNS在SELECT语句的WHERE子句中指定索引列Oracle9i可监视索引是否被使用监视索引是否被使用:查询索引使用情况:删除不使用的索引:ALTERINDEXschema.indexnameMONITORINGUSAGE;SELECT*FROMV$OBJECT_USAGE;DROPINDEXschema.indexname;25-162020/2/22索引的使用-复合索引的主列采用多列索引叫复合索引。CREATEINDEXcomp_indONtab1(x,y,z);复合列变为x,xy和xyz几个部分查询语句中带有：where...and来使用复合键SELECT语句：SELECT…FROM…WHEREX=xANDY=yANDZ=z;25-172020/2/22索引的使用-索引列采用多列索引叫复合索引。如果希望使用索引，则不要索引列上加表达式：低效：SELECT*fromAwhereSALARY+1000=:NEWSALARY高效：SELECT*fromAwhereSALARY=:NEWSALARY-100025-182020/2/22索引的使用-查询索引详细信息程序员要了解所访问表对应的索引从USER_TABLES采用GET_DDL来展现SetLONG9999SELECT'Selectdbms_metadata.get_ddl(‘||chr(39)||object_type||chr(39)||','||chr(39)||object_name||chr(39)||')fromdual;'FROMUSER_TABLESWHERETABLE_NAME=‘EMP’;25-192020/2/22索引的使用-不被使用的原因索引不使用主要是加了表达式在索引列上加任何表达式，如：应避免类似下面用法：!=(notequalto)Like'%SA%'SELECT*fromAwheresubstr(name,1,3)='Wil'25-202020/2/22索引的使用-不被使用的原因程序员在WHERE子句加了索引对应列名，但系统可能由于下面原因索引不使用索引在索引列上加了函数，如：在where子句中用了NOT的SQL语句，如：使用ISNULL或ISNOTNULL的SQL语句；对索引列进行内部转换的SQL语句.selectname,addressfrompersonswhereupper(name)=’JOHE’;selectname,address,cityfromperonswherecitynotin(‘BOSTON’,’NEWYORK’);25-212020/2/22索引的使用-有时不用索引索引的使用不是任何时候都需要小表就没有必要采用增加表达式来避免使用索引SELECT*FROMAWHERESALARY+0='10000'ANDDEPT='IT'SELECT*FROMAWHEREEMP_SEX||''=’M'25-222020/2/22索引的使用-索引的NULL不要设置索引列的为NULL如果索引列为数字类型，应设置为0不够良好：SELECT*FROMAWHERENUMBERISNOTNULL良好：(通常反应快些)SELECT*FROMAWHERENUMBER025-232020/2/22索引的使用-索引与排序使用索引连接如果A表在lastname,firstname索引，则：不够良好：SELECT*FROMAWHERElastname='Smith'ORDERBYfirstname良好：(通常反应快些)SELECT*FROMAWHERElastname='Smith'ORDERBYlastname,firstname25-242020/2/22小结：设计是关键：设计考虑大表分区和索引分区；特殊表对应特殊表空间；频繁更新与相对固定表的分开；SQL语句的优化可借助EM来调整：9i的诊断程序；10g的顾问程序；见《9i诊断程序》与《10g顾问程序》