还剩25页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
1SQL执行顺序
1.1SELECT语句一般select语句处理按以下步骤顺序执行1)建立游标2)分析语句3)定义输出指定位置,类型,结果集的数据类型4)绑定变量如果查询使用变量的话,Oracle就要知道变量的值5)是否能并行运行(如果有多个服务器时)6)执行查询7)检索出数据8)关闭游标
1.2DML语句一般INSERTUPADTEDELETE语句处理执行顺序是1建立游标(Oracle建立一个隐含的游标)2分析语句3绑定变量如果语句用了变量,Oracle要知道变量的值4看语句是否能以并行方式运行(如果有多个服务器时)5执行语句6通知用户,语句已执行完毕7关闭游标
1.3COMMIT语句当事务提交时,Oracle分配一个唯一的顺序号SCNSystemChangeNumber给事务数据库恢复总是基于该SCN号来进行处理SCN号是记录在控制文件、数据文件、块头及重做日志文件中Oracle在下面情况提交事务 发出一个COMMIT语句、 执行DDL语句时、 离开Oracle时Oracle处理COMMIT的顺序1)服务器为每个COMMIT产生一个SCN使改变永久化2)LGWR进程将日志缓冲区数据并带有SCN一起写到重做日志文件3)服务器释放表级和行级锁4)用户被提示COMMIT完成5)服务器使事务已完成
1.4ROLLBACK语句当下面情况发生时,数据库执行回滚 发出ROLLBACK命令、 服务器进程放弃地终止、 会话被DBA终止ORACLE处理ROLLBACK的顺序1)服务器进程不做任何的改变2)服务器释放表级和行级锁3)服务器使事务已完成2访问表中记录
2.1访问表中记录的方式ORACLE只有两种访问表中记录的方式a. 全表扫描 全表扫描就是顺序地访问表中每条记录ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描b. 通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率,ROWID包含了表中记录的物理位置信息,ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高
2.2SQL对表与索引的可能操作情况ORACLE的执行一个SQL对表与索引的可能操作情况有三种A.只读表该情况只出现在全表扫描的情况B.只读索引该情况只出现在全索引扫描的情况Oracle对全索引扫描限制为SQL请求的全部列(column)必须驻留在索引树中;也就是说,SELECT和WHERE字句中的所有数据列必须存在于索引中C.读取索引和表最普遍的查询方式,数据库先根据索引查询到满足要求的rowid再根据rowid查询表
2.3全表扫描与高水位线做全表扫描的时候,影响性能最大的不是表内记录数的多少,也不是该表所分配的空间,而是这个表的高水位线下的空间的大小比如说表A在创建的时候分配了100M空间,然后在表A内插入数据的时候,表A的高水位线下的空间会逐渐加大;而删除表内的数据的时候,表的高水位线并不会变化,也就是说高水位线下的空间不会减少随着高水位线的不断上移,以上查询的效率就会越来越低
2.
3.1解除高水位线过高的处理移动表所在的表空间,然后再移回原来的表空间Altertabletable_namemovetablespacetablespace_name;缺点需要足够的空间,并且需要另外一个足够空间的表空间,移动表后需重建该表的索引,需要放在系统空闲时做,会影响业务对该表的使用Exp/imp将该表export后再import缺点import前需要将该表drop,需要中断业务,有该表参与的过程,函数等需要进行重编译将原表改名后重新建立新表再用insertintoselect方式转移数据缺点需要中断业务,需要足够的空间,比较移动表空间的方式可以在同一表空间上进行处理;改名时还需要对索引,约束,触发器等进行处理(影响建立新表)通过createtableaasselect*fromb建立表a保存数据,将表btruncate后将保存的数据用insertinto..select恢复缺点需要中断业务,需要足够的空间,存在外键时需要先drop外键
2.4表与索引相关的系统对象dba_tables;dba_tab_cols;dba_tab_columns;dba_tab_comments;dba_indexes;dba_ind_columns;dba_segments;user_tables;user_tab_cols;user_tab_columns;user_tab_comments;user_indexes;user_ind_columns;user_segments;3索引的组织结构
3.1基本的多叉树索引第一层为根节点,第二层为分叉节点,第三层为叶节点
3.2索引的结构的变动
3.
2.1INSERT引起的索引结构变动在INSERT数据时,索引的结构增加,索引占用的空间规模、扫描需要花费的时间规模、插入操作需要花费的时间规模增加问题随着数据的增加,插入数据时对索引的变动花费的时间增加
3.
2.2DELETE引起的索引结构变动在delete数据时,在索引中仅置一标志,表明该数据被删除而不影响索引的基本结构
3.
2.3重建索引ALTERINDEXINDEXNAMEREBUILDTABLESPACENAME;4ORACLE优化器与执行计划
4.1Oracle优化器 ORACLE的优化器共有3种RULE(基于规则)COST(基于成本)CHOOSE(选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你当然也在SQL句级或是会话(session)级对其进行覆盖 为了使用基于成本的优化器(CBO,Cost-BasedOptimizer),你必须经常运行analyze命令,以增加数据库中的对象统计信息(objectstatistics)的准确性 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关如果table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器在缺省情况下,ORACLE采用CHOOSE优化器,为了避免那些不必要的全表扫描(fulltablescan),你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器
4.2获取执行计划
4.
2.1Plsql中获取执行计划在sql窗口中选中sql,按F5键即可获取该sql的执行计划
4.
2.2Sqlplus中获取执行计划D:\oracle\ora92sqlplus/nologSQL*Plus:Release
9.
2.
0.
1.0-Productionon星期二6月315:16:032003Copyrightc19822002OracleCorporation.Allrightsreserved.SQLconnectsysassysdba请输入口令:已连接SQL@\rdbms\admin\utlxplan表已创建SQLcreatepublicsynonymplan_tableforplan_table;同义词已创建SQLgrantallonplan_tabletopublic;授权成功SQL@\sqlplus\admin\plustrceSQLSQLdroproleplustrace;droproleplustrace*ERROR位于第1行:ORA-01919:角色PLUSTRACE不存在SQLcreateroleplustrace;角色已创建SQLSQLgrantselectonv_$sesstattoplustrace;授权成功SQLgrantselectonv_$statnametoplustrace;授权成功SQLgrantselectonv_$sessiontoplustrace;授权成功SQLgrantplustracetodbawithadminoption;授权成功SQLSQLsetechooff DBA用户首先被授予了plustrace角色然后我们可以把plustrace授予public这样所有用户都将拥有plustrace角色的权限. SQLgrantplustracetopublic;授权成功SQLsetautotraceon;SQLselectcount*fromt_cell;COUNT*----------5384ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10SORTAGGREGATE21TABLEACCESSFULLOFT_CELLStatistics----------------------------------------------------------0recursivecalls0dbblockgets27consistentgets0physicalreads0redosize380bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sortsmemory0sortsdisk1rowsprocessedSQL
4.3分析表和索引以选择正确的执行计划analyzetabletablenamecomputestatistics;analyzetableabccomputestatisticssample20percent;analyzeindex|clusterindexnameestimatestatistics;analyzetabletablenamecomputestatisticsfortableforall[local]indexesforall[indexed]columns;analyzetabletablenamedeletestatistics;analyzetabletablenamevalidaterefupdate;analyzetabletablenamevalidatestructure;
4.4系统忙紧急处理方式1.通过top类命令获取cpu使用率高的进程ID(pid)2.根据PID查询该进程在执行的sql语句selectsql_textfromv$sqlwhereaddress=selectsql_addressfromv$sessionwherepaddr=selectaddrfromv$processwherespid=;3.分析该sql语句的执行计划4.优化该sql语句的执行计划
4.5通过statspack获取性能差的sql
4.
5.1系统参数为了能够顺利安装和运行Statspack你可能需要设置以下系统参数
1.job_queue_processes为了能够建立自动任务,执行数据收集,该参数需要大于0你可以在初试化参数文件中修改该参数
2.timed_statistics收集操作系统的计时信息,这些信息可被用来显示时间等统计信息、优化数据库和SQL语句要防止因从操作系统请求时间而引起的开销,请将该值设置为False使用statspack收集统计信息时建议将该值设置为TRUE,否则收集的统计信息大约只能起到10%的作用,将timed_statistics设置为True所带来的性能影响与好处相比是微不足道的该参数使收集的时间信息存储在在V$SESSTATS和V$SYSSTATS动态性能视图中Timed_statistics参数可以在实例级进行更改SQLaltersystemsettimed_statistics=true;SystemalteredSQL
4.
5.
2.安装Statspack安装Statspack需要用internal身份登陆,或者拥有SYSDBAconnect/assysdba权限的用户登陆需要在本地安装或者通过telnet登陆到服务器在Oracle
8.
1.6版本中运行statscre.sql;在Oracle
8.
1.7及以上版本中运行spcreate.sql首先登陆到数据库,最好转到$ORACLE_HOME/RDBMS/ADMIN目录,这样我们执行脚本就可以方便些Sql@spcreate.sql
4.
5.3测试安装好的Statspack运行statspack.snap可以产生系统快照,运行两次,然后执行spreport.sql就可以生成一个基于两个时间点的报告如果一切正常,说明安装成功SQLexecutestatspack.snapPL/SQLproceduresuccessfullycompleted.SQLexecutestatspack.snapPL/SQLproceduresuccessfullycompleted.SQL@spreport.sql
4.
5.4生成分析报告调用spreport.sql可以生成分析报告SQL@spreport
4.
5.5删除历史数据删除stats$snapshot数据表中的相应数据,其他表中的数据会相应的级连删除SQLselectmaxsnap_idfromstats$snapshot;MAXSNAP_ID------------166SQLdeletefromstats$snapshotwheresnap_id=166;143rowsdeleted
4.
5.6调整STATSPACK的收集门限Statspack有两种类型的收集选项级别(level)控制收集数据的类型门限(threshold)设置收集的数据的阈值
4.
5.
6.1级别(level)Statspack共有三种快照级别,默认值是5a.level0:一般性能统计包括等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池统计等等b.level5:增加SQL语句除了包括level0的所有内容,还包括SQL语句的收集,收集结果记录在stats$sql_summary中c.level10:增加子锁存统计包括level5的所有内容并且还会将附加的子锁存存入stats$lathc_children中在使用这个级别时需要慎重,建议在Oraclesupport的指导下进行可以通过statspack包修改缺省的级别设置SQLexecutestatspack.snapi_snap_level=0i_modify_parameter=’true’;通过这样的设置,以后的收集级别都将是0级如果你只是想本次改变收集级别,可以忽略i_modify_parameter参数SQLexecutestatspack.snapi_snap_level=10;
4.
5.
6.2快照门限快照门限只应用于stats$sql_summary表中获取的SQL语句因为每一个快照都会收集很多数据,每一行都代表获取快照时数据库中的一个SQL语句,所以stats$sql_summary很快就会成为Statspack中最大的表门限存储在stats$statspack_parameter表中让我们了结一下各种门限a.executions_th这是SQL语句执行的数量默认值是100b.disk_reads_tn这是SQL语句执行的磁盘读入数量(默认值是1000)c.parse_calls_th这是SQL语句执行的解析调用的数量(默认值是1000)d.buffer_gets_th这是SQL语句执行的缓冲区获取的数量(默认值是10000)任何一个门限值超过以上参数就会产生一条记录通过调用statspack.modify_statspack_parameter函数我们可以改变门限的默认值例如SQLexecutestatspack.modify_statspack_parameteri_buffer_gets_th=100000i_disk_reads_th=100000;
4.6简明低性能sql获取select*fromselectsql_textdisk_readsexecutionsfromv$sqlorderbydisk_readsdescwhererownum=10;5常用优化规则
5.1使用索引使用索引来减少全表扫描,但是并非所有的情况下索引扫描都优于全表扫描,以下就是全表扫描优于索引扫描的情况A.小表B.索引的过滤度低C.需要做全表扫描时使用索引不能过度,在对表插入数据时,由于索引的存在,会降低插入的速度一般来说,一个表上的索引不应该超过5个
5.2共享SQL语句,使用绑定变量 为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路径ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用 当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等) 共享的语句必须满足三个条件A.字符级的比较 当前被执行的语句和共享池中的语句必须完全相同 例如 SELECT*FROMEMP; 和下列每一个都不同 SELECT*fromEMP; Select*FromEmp; SELECT * FROMEMP; B. 两个语句所指的对象必须完全相同 C. 两个SQL语句中必须使用相同的名字的绑定变量(bindvariables) 例如第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值) a. selectpin,namefrompeoplewherepin=blk
1.pin; selectpin,namefrompeoplewherepin=blk
1.pin; b. selectpin,namefrompeoplewherepin=blk
1.ot_ind; selectpin,namefrompeoplewherepin=blk
1.ov_ind;
5.3尽量多使用COMMIT 只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少COMMIT所释放的资源回滚段上用于恢复数据的信息被程序语句获得的锁 redologbuffer中的空间 ORACLE为管理上述3种资源中的内部花费 注意在使用COMMIT时必须要注意到事务的完整性,必须先保证事务的完整性
5.4SELECT子句中避免使用‘*’ 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法不幸的是,这是一个非常低效的方法实际上,ORACLE在解析的过程中,会将‘*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间
5.5使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表 例如 SELECTCOUNT*,SUMSAL FROM EMP WHEREDEPT_NO=0020 ANDENAMELIKE ‘SMITH%’; SELECTCOUNT*,SUMSAL FROM EMP WHEREDEPT_NO=0030 ANDENAMELIKE ‘SMITH%’; 你可以用DECODE函数高效地得到相同结果SELECTCOUNTDECODEDEPT_NO0020’X’NULLD0020_COUNT COUNTDECODEDEPT_NO0030’X’NULLD0030_COUNT SUMDECODEDEPT_NO0020SALNULLD0020_SAL SUMDECODEDEPT_NO0030SALNULLD0030_SALFROMEMPWHEREENAMELIKE‘SMITH%’; 类似的,DECODE函数也可以运用于GROUPBY和ORDERBY子句中
5.6避免改变索引列的类型 当比较不同数据类型的数据时,ORACLE自动对列进行简单的类型转换 假设EMPNO是一个数值类型的索引列SELECT…FROMEMPWHEREEMPNO=‘123’ 实际上,经过ORACLE类型转换,语句转化为 SELECT…FROMEMPWHEREEMPNO=TO_NUMBER(‘123’) 幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变 现在,假设EMP_TYPE是一个字符类型的索引列SELECT… FROMEMPWHEREEMP_TYPE=123 这个语句被ORACLE转换为 SELECT…FROMEMPWHERETO_NUMBER(EMP_TYPE)=
1235.7删除重复记录 最高效的删除重复记录方法(因为使用了ROWID)DELETEFROMEMPEWHEREE.ROWIDSELECTMINX.ROWID FROMEMPX WHEREX.EMP_NO=E.EMP_NO;
5.8用TRUNCATE替代DELETE 当删除表中的全部记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息当命令运行后,数据不能被恢复因此很少的资源被调用,执行时间也会很短 注意TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML
5.9用Where子句替换HAVING子句 避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤这个处理需要排序,总计等操作如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销 例如 低效 SELECTREGION,AVGLOG_SIZE FROMLOCATION GROUPBYREGION HAVINGREGIONREGION!=‘SYDNEY’ ANDREGION!=‘PERTH’ 高效 SELECTREGION,AVGLOG_SIZE FROMLOCATION WHEREREGIONREGION!=‘SYDNEY’ ANDREGION!=‘PERTH’ GROUPBYREGION注意HAVING中的条件一般用于对一些集合函数的比较,如COUNT()等等除此而外,一般的条件应该写在WHERE子句中
5.10减少对表的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询 例如 低效 SELECTTAB_NAME FROMTABLES WHERETAB_NAME=SELECTTAB_NAME FROMTAB_COLUMNS WHEREVERSION=604 AND DB_VER=SELECTDB_VER FROMTAB_COLUMNS WHEREVERSION=604 高效 SELECTTAB_NAME FROMTABLES WHERE TAB_NAMEDB_VER =SELECTTAB_NAMEDB_VER FROMTAB_COLUMNS WHEREVERSION=604 Update多个Column例子 低效 UPDATEEMP SETEMP_CAT=SELECTMAXCATEGORYFROMEMP_CATEGORIES SAL_RANGE=SELECTMAXSAL_RANGEFROMEMP_CATEGORIES WHEREEMP_DEPT=0020; 高效 UPDATEEMPSETEMP_CATSAL_RANGE =SELECTMAXCATEGORYMAXSAL_RANGE FROMEMP_CATEGORIES WHEREEMP_DEPT=0020;
5.11利用with减少表的查询insertintomct_balance_bill_cpcompany_idcompany_nameprovincenamebalance_MONTHbusi_codebusi_countbusi_feebusi_percentwithaasselectaaa.mobilenobbb.movieprovideraaa.filmidcount*filmcountfrommct_film_operaaamct_moviebbbwhereaaa.filmid=bbb.filmidandaaa.servicecode=-YDDMandaaa.opertime=add_monthstruncsysdatemm-1andaaa.opertimetruncsysdatemmgroupbyaaa.mobilenobbb.movieprovideraaa.filmidselect0ab.movieproviderb.provincenamev_month-YDDMnullsumfeenullfromselecta.mobileno10/count*feefromagroupbya.mobilenoaaaabmct_phone_codebwhereaa.mobileno=ab.mobilenoandsubstraa.mobileno17=b.msisdngroupbyab.movieproviderb.provincename;
5.12使用表的别名(Alias) 当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误 注意Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属
5.13用EXISTS替代IN 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率 低效SELECT*FROMEMP基础表WHEREEMPNO0ANDDEPTNOINSELECTDEPTNOFROMDEPTWHERELOC=‘MELB’ 高效SELECT*FROMEMP基础表WHEREEMPNO0ANDEXISTSSELECT‘X’FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB’
5.14用NOTEXISTS替代NOTIN 在子查询中,NOTIN子句将执行一个内部的排序和合并无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历) 为了避免使用NOTIN,我们可以把它改写成外连接(OuterJoins)或NOTEXISTS. 例如SELECT…FROMEMPWHEREDEPT_NONOTINSELECTDEPT_NO FROMDEPT WHEREDEPT_CAT=’A’; 为了提高效率改写为 (方法一高效)SELECT….FROMEMPADEPTBWHEREA.DEPT_NO=B.DEPT+ANDB.DEPT_NOISNULLANDB.DEPT_CAT+=‘A’ (方法二最高效)SELECT….FROMEMPEWHERENOTEXISTSSELECT‘X’ FROMDEPTD WHERED.DEPT_NO=E.DEPT_NO ANDDEPT_CAT=‘A’;
5.15用表连接替换EXISTS 通常来说,采用表连接的方式比EXISTS更有效率 SELECTENAME FROMEMPE WHEREEXISTSSELECT‘X’ FROMDEPT WHEREDEPT_NO=E.DEPT_NO ANDDEPT_CAT=‘A’; (更高效) SELECTENAME FROMDEPTDEMPE WHEREE.DEPT_NO=D.DEPT_NO ANDDEPT_CAT=‘A’;
5.16强制索引失效 在特定的时候,我们可以通过强制索引失效让数据库选择正确的索引或不使用索引来优化sql 举例SELECTENAME FROMEMP WHEREEMPNO=7935 ANDDEPTNO+0=10 /*DEPTNO上的索引将失效*/ ANDEMP_TYPE||‘’=‘A’ /*EMP_TYPE上的索引将失效*/ 这是一种相当直接的提高查询效率的办法但是你必须谨慎考虑这种策略,一般来说,只有在你希望单独优化几个SQL时才能采用它 这里有一个例子关于何时采用这种策略, 假设在EMP表的EMP_TYPE列上有一个非唯一性的索引而EMP_CLASS上没有索引SELECTENAME FROMEMP WHEREEMP_TYPE=‘A’ ANDEMP_CLASS=‘X’; 优化器会注意到EMP_TYPE上的索引并使用它这是目前唯一的选择如果,一段时间以后,另一个非唯一性建立在EMP_CLASS上,优化器必须对两个索引进行选择,在通常情况下,优化器将使用两个索引并在他们的结果集合上执行排序及合并然而,如果其中一个索引(EMP_TYPE)接近于唯一性而另一个索引(EMP_CLASS)上有几千个重复的值排序及合并就会成为一种不必要的负担在这种情况下,你希望使优化器屏蔽掉EMP_CLASS索引 用下面的方案就可以解决问题SELECTENAME FROMEMP WHEREEMP_TYPE=‘A’ ANDEMP_CLASS||‘’=‘X’;
5.17避免在索引列上使用计算 WHERE子句中,如果索引列是函数的一部分优化器将不使用索引而使用全表扫描 举例 低效 SELECT… FROMDEPTWHERESAL*1225000; 高效 SELECT… FROMDEPT WHERESAL25000/12;
5.18用=替代 如果DEPTNO上有一个索引, 高效SELECT… FROMEMP WHEREDEPTNO=4 低效SELECT… FROMEMP WHEREDEPTNO3 两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录
5.19避免在索引列上使用ISNULL和ISNOTNULL 避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引对于单列索引,如果列包含空值,索引中将不存在此记录对于复合索引,如果每个列都为空,索引中同样不存在此记录 如果至少有一个列不为空,则记录存在于索引中 举例 如果唯一性索引建立在表的A列和B列上,并且表中存在一条记录的A,B值为(123,null),ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入)然而如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空因此你可以插入1000条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引 举例 低效(索引失效)SELECT…FROMDEPARTMENTWHEREDEPT_CODEISNOTNULL; 高效(索引有效)SELECT…FROMDEPARTMENTWHEREDEPT_CODE=0;
5.20总是使用索引的第一个列 如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引
5.21用UNION-ALL替换UNION(如果有可能的话) 当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序 如果用UNIONALL替代UNION,这样排序就不是必要了效率就会因此得到提高 举例 低效 SELECTACCT_NUM,BALANCE_AMT FROMDEBIT_TRANSACTIONS WHERETRAN_DATE=‘31-DEC-95’ UNION SELECTACCT_NUM,BALANCE_AMT FROMDEBIT_TRANSACTIONS WHERETRAN_DATE=‘31-DEC-95’ 高效 SELECTACCT_NUM,BALANCE_AMT FROMDEBIT_TRANSACTIONS WHERETRAN_DATE=‘31-DEC-95’ UNIONALL SELECTACCT_NUM,BALANCE_AMT FROMDEBIT_TRANSACTIONS WHERETRAN_DATE=‘31-DEC-95’ 注意:UNIONALL将重复输出两个结果集合中相同记录因此还是要从业务需求分析使用UNIONALL的可行性
5.22需要当心的WHERE子句 某些SELECT语句中的WHERE子句不使用索引注意以下的例子
5.
22.1 ‘!=’将不使用索引 不使用索引 SELECTACCOUNT_NAME FROMTRANSACTION WHEREAMOUNT!=0; 使用索引 SELECTACCOUNT_NAME FROMTRANSACTION WHEREAMOUNT0;
5.
22.2 ‘||’将不使用索引 不使用索引 SELECTACCOUNT_NAME,AMOUNT FROMTRANSACTION WHEREACCOUNT_NAME||ACCOUNT_TYPE=‘AMEXA’; 使用索引 SELECTACCOUNT_NAME,AMOUNT FROMTRANSACTION WHEREACCOUNT_NAME=‘AMEX’ANDACCOUNT_TYPE=‘A’;
5.
22.3‘+’将不使用索引 不使用索引 SELECTACCOUNT_NAME,AMOUNT FROMTRANSACTION WHEREAMOUNT+30005000; 使用索引 SELECTACCOUNT_NAME,AMOUNT FROMTRANSACTION WHEREAMOUNT2000;
5.
22.4 相同的索引列将不使用索引 不使用索引 SELECTACCOUNT_NAME,AMOUNT FROMTRANSACTION WHEREACCOUNT_NAME=NVL(ACC_NAME,ACCOUNT_NAME); 使用索引 SELECTACCOUNT_NAME,AMOUNT FROMTRANSACTION WHEREACCOUNT_NAMELIKENVL(ACC_NAME,‘%’); 注意如果一定要对使用函数的列启用索引,建立基于函数的索引(Function-BasedIndex) CREATEINDEXEMP_IONEMP(UPPER(ename));/*建立基于函数的索引*/ SELECT*FROMempWHEREUPPER(ename)=‘BLACKSNAIL’;/*将使用索引*/
5.23CBO下使用更具选择性的索引 基于成本的优化器(CBO,Cost-BasedOptimizer)对索引的选择性进行判断来决定索引的使用是否能提高效率 如果索引有很高的选择性,那就是说对于每个不重复的索引键值,只对应数量很少的记录 比如,表中共有100条记录而其中有80个不重复的索引键值这个索引的选择性就是80/100=
0.
8.选择性越高,通过索引键值检索出的记录就越少 如果索引的选择性很低,检索数据就需要大量的索引范围查询操作和ROWID访问表的操作也许会比全表扫描的效率更低
5.24分离表和索引 总是将你的表和索引建立在不同的表空间内(TABLESPACES)决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里同时,确保数据表空间和索引表空间置于不同硬盘控制卡控制的硬盘上6Hint干预执行计划
6.1指定存取路径FULL/*+FULLtable*/指定该表使用全表扫描ROWID/*+ROWIDtable*/指定对该表使用rowid存取方法,该提示用的较少INDEX/*+INDEXtable[index]*/使用该表上指定的索引对表进行索引扫描INDEX_FFS/*+INDEX_FFStable[index]*/使用快速全表扫描NO_INDEX/*+NO_INDEXtable[index]*/不使用该表上指定的索引进行存取,仍然可以使用其它的索引进行索引扫描
6.2指定连接顺序ORDERED/*+ORDERED*/按from字句中表的顺序从左到右的连接STAR/*+STAR*/指示优化器使用星型查询
6.3指定连接类型USE_NL/*+USE_NLtable[table...]*/使用嵌套连接USE_MERGE/*+USE_MERGEtable[table...]*/使用排序--合并连接USE_HASH/*+USE_HASHtable[table...]*/使用HASH连接注意如果表有alias别名,则上面的table指的是表的别名,而不是真实的表名7中间件比较存储过程1.javac#这样的语言,实现业务逻辑时,还是需要大量的使用sql,这样中间层和db的通讯太多,而且每次传过去的相当于动态sql语句,oracle都要解析,中间层的程序自身做的逻辑其实很少,比如sql里就有排序,分组,分析函数等等很强的功能,所以中间层也做不了多少逻辑;反而大量增加了数据库服务器的通讯功能,也没有给数据库服务器带来那里业务上的优化;另外,一些事务控制,如果在存储过程内部实现是最好的,但是如果在中间层做,就有些隔靴搔痒的感觉;至于javac#里丰富的面向对象的功能,入继承,封装,接口,重构等,好像在以和数据库为核心的系统里,作用也不是特别明显;
2.就算中间层放在多个服务器上,但是由于其大量的通讯和数据获取都需要DB服务器,所以数据库服务器的压力没有减少,反而增加;
3.过程太多,可以用包封装,管理也不至于特别麻烦;至于过多的存储过程,是否会带来db服务器的效率瓶颈;
4.很多不同的数据库都有其真身的“技巧”,放着这些“技巧”不用,不是可惜;
5.把业务的实现过多的依赖pl/sql上,对开发语言是否用C#还是java对以后无论是b/s,还是c/s,程序无论运行在pc上还是类似于智能手机的设备上,操作系统无论是windows还是其他之类,都不受影响;好像感觉有点以不变应万变的做法;而且调试和优化都很方便NULL-B1MOVE-B2Null-L1Bolan-L2Floyd-L3Move-L4Queen-L5Ziggy-L6ABBArowidAcdcrowidBolanrowidCedrowidDfgrowidFloydrowidGedrowidHfgrowidMoverowidOrgrowidPxcrowidQueenrowidRacrowidRdgrowidZiggyrowidZxcrowid。