索引.docx
- 文档编号:9607755
- 上传时间:2023-02-05
- 格式:DOCX
- 页数:19
- 大小:27.47KB
索引.docx
《索引.docx》由会员分享,可在线阅读,更多相关《索引.docx(19页珍藏版)》请在冰豆网上搜索。
索引
XX空间|XX首页|登录顽石个站一个菜鸟的成长代码,和心路里程主页博客相册|个人档案|好友查看文章
oracle中查询时指定索引的方法2008-01-1116:
00五、索引Index的优化设计
1、管理组织索引
索引可以大大加快数据库的查询速度,索引把表中的逻辑值映射到安全的RowID,因此索引能进行快速定位数据的物理地址。
但是有些DBA发现,对一个大型表建立的索引,并不能改善数据查询速度,反而会影响整个数据库的性能。
这主要是和SGA的数据管理方式有关。
ORACLE在进行数据块高速缓存管理时,索引数据比普通数据具有更高的驻留权限,在进行空间竞争时,ORACLE会先移出普通数据。
对一个建有索引的大型表的查询时,索引数据可能会用完所有的数据块缓存空间,ORACLE不得不频繁地进行磁盘读写来获取数据,因此在对一个大型表进行分区之后,可以根据相应的分区建立分区索引。
如果对这样大型表的数据查询比较频繁,或者干脆不建索引。
另外,DBA创建索引时,应尽量保证该索引最可能地被用于where子句中,如果对查询只简单地制定一个索引,并不一定会加快速度,因为索引必须指定一个适合所需的访问路径。
2、聚簇的使用
Oracle提供了另一种方法来提高查询速度,就是聚簇(Cluster)。
所谓聚簇,简单地说就是把几个表放在一起,按一定公共属性混合存放。
聚簇根据共同码值将多个表的数据存储在同一个Oracle块中,这时检索一组Oracle块就同时得到两个表的数据,这样就可以减少需要存储的Oracle块,从而提高应用程序的性能。
3、优化设置的索引,就必须充分利用才能加快数据库访问速度。
ORACLE要使用一个索引,有一些最基本的条件:
1)、where子名中的这个字段,必须是复合索引的第一个字段;2)、where子名中的这个字段,不应该参与任何形式的计算。
Sal*(2*90/100)
六、多CPU和并行查询PQO(ParallelQueryOption)方式的利用
1、尽量利用多个CPU处理器来执行事务处理和查询
CPU的快速发展使得ORACLE越来越重视对多CPU的并行技术的应用,一个数据库的访问工作可以用多个CPU相互配合来完成,加上分布式计算已经相当普遍,只要可能,应该将数据库服务器和应用程序的CPU请求分开,或将CPU请求从一个服务器移到另一个服务器。
对于多CPU系统尽量采用ParallelQueryOption(PQO,并行查询选项)方式进行数据库操作。
2、使用ParallelQueryOption(PQO,并行查询选择)方式进行数据查询
使用PQO方式不仅可以在多个CPU间分配SQL语句的请求处理,当所查询的数据处于不同的磁盘时,一个个独立的进程可以同时进行数据读取。
3、使用SQL*LoaderDirectPath选项进行大量数据装载
使用该方法进行数据装载时,程序创建格式化数据块直接写入数据文件中,不要求数据库内核的其他I/O。
七、实施系统资源管理分配计划
ORACLE提供了DatabaseResourceManager(DRM,数据库资源管理器)来控制用户的资源分配,DBA可以用它分配用户类和作业类的系统资源百分比。
在一个OLDP系统中,可给联机用户分配75%的CPU资源,剩下的25%留给批用户。
另外,还可以进行CPU的多级分配。
除了进行CPU资源分配外,DRM还可以对资源用户组执行并行操作的限制。
八、使用最和SQL优化方优的数据库连接案
1、使用直接的OLEDB数据库连接方式。
通过ADO可以使用两种方式连接数据库,一种是传统的ODBC方式,一种是OLEDB方式。
ADO是建立在OLEDB技术上的,为了支持ODBC,必须建立相应的OLEDB到ODBC的调用转换,而使用直接的OLEDB方式则不需转换,从而提高处理速度。
2、使用ConnectionPool机制
在数据库处理中,资源花销最大的是建立数据库连接,而且用户还会有一个较长的连接等待时间。
解决的办法就是复用现有的Connection,也就是使用ConnectionPool对象机制。
ConnectionPool的原理是:
IIS+ASP体系中维持了一个连接缓冲池,这样,当下一个用户访问时,直接在连接缓冲池中取得一个数据库连接,而不需重新连接数据库,因此可以大大地提高系统的响应速度。
3、高效地进行SQL语句设计
通常情况下,可以采用下面的方法优化SQL对数据操作的表现:
(1)减少对数据库的查询次数,即减少对系统资源的请求,使用快照和显形图等分布式数据库对象可以减少对数据库的查询次数。
(2)尽量使用相同的或非常类似的SQL语句进行查询,这样不仅充分利用SQL共享池中的已经分析的语法树,要查询的数据在SGA中命中的可能性也会大大增加。
(3)限制动态SQL的使用,虽然动态SQL很好用,但是即使在SQL共享池中有一个完全相同的查询值,动态SQL也会重新进行语法分析。
(4)避免不带任何条件的SQL语句的执行。
没有任何条件的SQL语句在执行时,通常要进行FTS,数据库先定位一个数据块,然后按顺序依次查找其它数据,对于大型表这将是一个漫长的过程。
(5)如果对有些表中的数据有约束,最好在建表的SQL语句用描述完整性来实现,而不是用SQL程序中实现。
(6)可以通过取消自动提交模式,将SQL语句汇集一组执行后集中提交,程序还可以通过显式地用COMMIT和ROLLBACL进行提交和回滚该事务。
(7)检索大量数据时费时很长,设置行预取数则能改善系统的工作表现,设置一个最大值,当SQL语句返回行超过该值,数值库暂时停止执行,除非用户发出新的指令,开始组织并显示数据,而不是让用户继续等待。
九、充分利用数据的后台处理方案减少网络流量
1、合理创建临时表或视图
所谓创建临时表或视图,就是根据需要在数据库基础上创建新表或视图,对于多表关联后再查询信息的可建新表,对于单表查询的可创建视图,这样可充分利用数据库的容量大、可扩充性强等特点,所有条件的判断、数值计算统计均可在数据库服务器后台统一处理后追加到临时表中,形成数据结果的过程可用数据库的过程或函数来实现。
2、数据库打包技术的充分利用
利用数据库描述语言编写数据库的过程或函数,然后把过程或函数打成包在数据库后台统一运行包即可。
3、数据复制、快照、视图,远程过程调用技术的运用
数据复制,即将数据一次复制到本地,这样以后的查询就使用本地数据,但是只适合那些变化不大的数据。
使用快照也可以在分布式数据库之间动态复制数据,定义快照的自动刷新时间或手工刷新,以保证数据的引用参照完整性。
调用远程过程也会大大减少因频繁的SQL语句调用而带来的网络拥挤。
总之,对所有的性能问题,没有一个统一的解决方法,但ORACLE提供了丰富的选择环境,可以从ORACLE数据库的体系结构、软件结构、模式对象以及具体的业务和技术实现出发,进行统筹考虑。
提高系统性能需要一种系统的整体的方法,在对数据库进行优化时,应对应用程序、I/O子系统和操作系统(OS)进行相应的优化。
优化是有目的地更改系统的一个或多个组件,使其满足一个或多个目标的过程。
对Oracle来说,优化是进行有目的的调整组件级以改善性能,即增加吞吐量,减少响应时间。
如果DBA能从上述九个方面综合考虑优化方案,相信多数ORACLE应用可以做到按最优的方式来存取数据。
我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为笔者学习、摘录、并汇总部分资料与大家分享!
(1)选择最有效率的表名顺序(只在基于规则的优化器中有效):
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表drivingtable)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。
如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表.把数据少的表放在FROM后面的最后
(2)WHERE子句中的连接顺序.:
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
(3)SELECT子句中避免使用‘*‘:
ORACLE在解析的过程中,会将'*'依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间
(4)减少访问数据库的次数:
ORACLE在内部执行了许多工作:
解析SQL语句,估算索引的利用率,绑定变量,读数据块等;
(5)在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200
(6)使用DECODE函数来减少处理时间:
******************************
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
decode(expression,search_1,result_1)
decode(expression,search_1,result_1,search_2,result_2)
decode(expression,search_1,result_1,search_2,result_2,....,search_n,result_n)
decode(expression,search_1,result_1,default)
decode(expression,search_1,result_1,search_2,result_2,default)
decode(expression,search_1,result_1,search_2,result_2,....,search_n,result_n,default)
decode函数比较表达式和搜索字,如果匹配,返回结果;如果不匹配,返回default值;如果未定义default值,则返回空值。
以下是一个简单测试,用于说明Decode函数的用法:
SQL>createtabletasselectusername,default_tablespace,lock_datefromdba_users;
Tablecreated.
SQL>select*fromt;
USERNAMEDEFAULT_TABLESPACELOCK_DATE
---------------------------------------------------------------------
SYSSYSTEM
SYSTEMSYSTEM
OUTLNSYSTEM
CSMIGSYSTEM
SCOTTSYSTEM
EYGLEUSERS
DBSNMPSYSTEM
WMSYSSYSTEM20-OCT-04
8rowsselected.
SQL>selectusername,decode(lock_date,null,'unlocked','locked')statusfromt;
USERNAMESTATUS
--------------------------------------
SYSunlocked
SYSTEMunlocked
OUTLNunlocked
CSMIGunlocked
SCOTTunlocked
EYGLEunlocked
DBSNMPunlocked
WMSYSlocked
8rowsselected.
SQL>selectusername,decode(lock_date,null,'unlocked')statusfromt;
USERNAMESTATUS
--------------------------------------
SYSunlocked
SYSTEMunlocked
OUTLNunlocked
CSMIGunlocked
SCOTTunlocked
EYGLEunlocked
DBSNMPunlocked
WMSYS
8rowsselected.
(7)整合简单,无关联的数据库访问:
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
(8)删除重复记录:
最高效的删除重复记录方法(因为使用了ROWID)例子:
DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)
FROMEMPXWHEREX.EMP_NO=E.EMP_NO);
(9)用TRUNCATE替代DELETE:
当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.(译者按:
TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
(10)尽量多使用COMMIT:
只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a.回滚段上用于恢复数据的信息.
b.被程序语句获得的锁
c.redologbuffer中的空间
d.ORACLE为管理上述3种资源中的内部花费
(11)用Where子句替换HAVING子句:
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.(非oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进行sum,在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。
在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。
在多表联接查询时,on比where更早起作用。
系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。
由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里
(12)减少对表的查询:
在含有子查询的SQL语句中,要特别注意减少对表的查询.例子:
SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECT
TAB_NAME,DB_VERFROMTAB_COLUMNSWHEREVERSION=604)
(13)通过内部函数提高SQL效率.:
复杂的SQL往往牺牲了执行效率.能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
(14)使用表的别名(Alias):
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
(15)用EXISTS替代IN、用NOTEXISTS替代NOTIN:
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率.在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOTIN,我们可以把它改写成外连接(OuterJoins)或NOTEXISTS.
例子:
(高效)SELECT*FROMEMP(基础表)WHEREEMPNO>0ANDEXISTS(SELECT1FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB')
(低效)SELECT*FROMEMP(基础表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB')
(16)识别'低效执行'的SQL语句:
虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法:
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>0
ANDBUFFER_GETS>0
AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8
ORDERBY4DESC;
(17)用索引提高效率:
索引是表的一个概念部分,用来提高检索数据的效率,ORACLE使用了一个复杂的自平衡B-tree结构.通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率.另一个使用索引的好处是,它提供了主键(primarykey)的唯一性验证.。
那些LONG或LONGRAW数据类型,你可以索引几乎所有的列.通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。
定期的重构索引是有必要的.:
在“系统维护清理”里有个“垃圾文件清理”
ALTERINDEXREBUILD
(18)用EXISTS替换DISTINCT:
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换,EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.例子:
(低效):
SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPE
WHERED.DEPT_NO=E.DEPT_NO
(高效):
SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X'
FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);
(19)sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行
(20)在java代码中尽量少用连接符“+”连接字符串!
(21)避免在索引列上使用NOT通常,
我们要避免在索引列上使用NOT,NOT会产生在和在索引列上使用函数相同的影响.当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
(22)避免在索引列上使用计算.
WHERE子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.
举例:
低效:
SELECT…FROMDEPTWHERESAL*12>25000;
高效:
SELECT…FROMDEPTWHERESAL>25000/12;
(23)用>=替代>
高效:
SELECT*FROMEMPWHEREDEPTNO>=4
低效:
SELECT*FROMEMPWHEREDEPTNO>3
两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
(24)用UNION替换OR(适用于索引列)
通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择OR而降低.在下面的例子中,LOC_ID和REGION上都建有索引.
高效:
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHERELOC_ID=10
UNION
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHEREREGION=“MELBOURNE”
低效:
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHERELOC_ID=10ORREGION=“MELBOURNE”
如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面.
(25)用IN来替换OR
这是一条简单易记的规则,但是实际的执行效果还须检验,在ORACLE8i下,两者的执行路径似乎是相同的.
低效:
SELECT….FROMLOCATIONWHERELOC_ID=10ORLOC_ID=20ORLOC_ID=30
高效
SELECT…FROMLOCATIONWHERELOC_ININ(10,20,30);
(26)避免在索引列上使用ISNULL和ISNOTNULL
避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引.对于单列索引,如果列包含空值,索引中将不存在此记录.对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.举例:
如果唯一性索引建立在表的A列和B列上,并且表中存在一条记录的A,B值为(123,null),ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 索引