Oracle优化器模式与Oracle索引优化规则课件

Oracle优化器介绍.Oracle索引介绍.SQL优化规则介绍.索引优化规则介绍.Oracle优化器介绍.1Oracle优化器模式:Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的.Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具.Oracle的优化器有两种优化方式：基于规则的优化方式(Rule-BasedOptimization,简称为RBO)和基于代价的优化方式(Cost-BasedOptimization,简称为CBO).RBO方式︰优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引.CBO方式︰CBO是看语句的代价,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的.在Oracle8及以后的版本,Oracle列推荐用CBO的方式。Oracle优化器模式:2优化器的优化模式(OptermizerMode):包括Rule,Choose,Firstrows,Allrows这四种方式.Rule:不用多说,即走基于规则的方式。rboChoose:指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。FirstRows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。对于排序分页页显示这种查询尤其适用.AllRows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式.查看缺省的Oracle优化器:SQL>showparametersoptimizer_mode;可以在init文件中对整个instance的所有会话设置.也可以单独对某个会话设置：SQL>ALTERSESSIONSEToptimizer_mode

=FIRST_ROWS;

优化器的优化模式(OptermizerMode):包括R3查看统计信息：1.selecttt.table_name,tt.num_rows,tt.blocks,tt.empty_blocks,tt.avg_row_lenfromdba_tablesttwherett.owner='SCOTT';2.selectttt.index_name,ttt.num_rows,ttt.distinct_keys,ttt.avg_leaf_blocks_per_key,ttt.clustering_factorfromdba_indexestttwherettt.owner='SCOTT';人工进行统计：对某一个用户下的所有表和索引执行统计分析:executedbms_stats.gather_schema_stats(ownname=>‘SCOTT',cascade=>true);对单个表执行统计分析:EXECUTEdbms_stats.gather_table_stats(ownname=>‘SCOTT',tabname=>‘EMP',estimate_percent=>50,cascade=>true).查看统计信息：4从OracleDatabase10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息,调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息.默认情况下在工作日晚上10：00-6：00和周末全天开启.一起运行的还有另外一个Job:AUTO_SPACE_ADVISOR_JOB.可以通过以下查询这个JOB的运行情况：

select*fromDba_Scheduler_JobswhereJOB_NAME='GATHER_STATS_JOB'.关闭自动统计功能：SQL>execBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');从OracleDatabase10g开始，Ora5Oracle索引介绍Oracle索引介绍6Oracle索引简介索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据(加快查询)；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。Oracle索引简介索引是建立在表的一列或多个列上的辅助对象7索引分类逻辑上:单列索引,多列索引,唯一索引,非惟一索引。物理上:B*-Tree索引，反向索引，位图索引。索引分类逻辑上:8单列索引和复合索引单列索引是基于单个列所建立的索引。多列索引是基于两列或多列所建立的索引。单列索引

Createindexemp_ind1onemp(ename);复合索引

Createindexemp_ind2onemp(ename,job);单列索引和复合索引单列索引是基于单个列所建立的索引。多列索引9惟一索引和非惟一索引惟一索引是索引列值不能重复的索引。非惟一索引是索引列值可以重复的索引。无论是惟一索引还是非惟一索引，索引列都允许NULL。惟一索引和非惟一索引惟一索引是索引列值不能重复的索引。10B*-tree索引B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branchblock）和叶块（leafblock）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址.创建索引：

1:createindexSTUDENT_IND_nameonSTUDENT(NAME)tablespaceUSERS;2:createindexSTUDENT_IND_nameonSTUDENT(NAME,AGE)tablespaceUSERS;B*-tree索引B*Tree索引是最常见的索引结构，默认建11B*-tree索引B*-tree索引12B*-tree索引假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75－100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如wherecolumn>20andcolumn<80，那么会先定位到第一个包含20的叶块，然后横向查找其他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。B*-tree索引假设我们要找索引中值为80的行，从索引树的13反向索引反向索引是B*Tree索引的一个分支，它的设计是为了运用在某些特定的环境下的。Oracle推出它的主要目的就是为了降低在并行服务器（OracleParallelServer）环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节。建立反向索引:createindexreversed_ind_emponemp(Order_ID)reverse;反向索引反向索引是B*Tree索引的一个分支，它的设计是为14反向索引如果有一个名称为order_id的列值在某行上是12345，oracle就将它颠倒为54321。这样连续的几个数不会在同一个页块里。不过反向索引又一个缺点就是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用，例如，wherecolumn>value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。

注意：只有索引中的值是逆向的，表中的值保持不变。反向索引如果有一个名称为order_id的列值在某行上是1215位图索引位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。

创建位图索引:createbitmapindexinx_bitmap_emponemp(sex);位图索引位图索引主要用于决策支持系统或静态数据，不支持行级锁16位图索引的格式行值

1

2

3

4

5

6

7

8

9

10

Male

1

0

0

0

0

0

0

0

1

1

Female

0

1

1

1

0

0

1

1

0

0

Null

0

0

0

0

1

1

0

0

0

0

位图索引的格式行值

1

2

3

4

5

6

7

817位图索引如果搜索where

gender=’Male’,要统计性别是”Male”的列行数的话，Oracle很快就能从位图中找到共3行即第1，9，10行是符合条件的；如果要搜索where

gender=’Male’

or

gender=’Female’的列的行数的话，也很容易从位图中找到共8行即1，2，3，4，7，8，9，10行是符合条件的。如果要搜索表的值的话，那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。

位图索引如果搜索where

gender=’Male’,要统18聚簇聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数据的目的。聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。一个表最多只能有一个聚簇索引。

聚簇聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数19使用Oracle聚簇索引在Oracle当中，聚簇不是索引的组织形式，而是表的组织形式。多用于表之间的连接字段。例:dept(deptno,

dnma,e

loc)

和表

emp(empno,

ename,

job,

mgr,

...

deptno),

常在deptno上进行连接,可以针对deptno字段建立聚簇，然后建立基于该聚簇的索引，并让两个表都使用上该聚簇。使用Oracle聚簇索引在Oracle当中，聚簇不是索20Oracle聚簇索引CREATECLUSTERpersonnel(department_numberNUMBER(2))SIZE512STORAGE(INITIAL100KNEXT50K);CREATETABLEemp(empnoNUMBERPRIMARYKEY,enameVARCHAR2(10)NOTNULLCHECK(ename=UPPER(ename)),jobVARCHAR2(9),mgrNUMBER,commNUMBER(9,0)DEFAULTNULL,deptnoNUMBER(2)NOTNULL)CLUSTERpersonnel(deptno);

CREATETABLEdept(deptnoNUMBER(2),dnameVARCHAR2(9),locVARCHAR2(9))CLUSTERpersonnel(deptno);

CREATEINDEXidx_personnelONCLUSTERpersonnel;Oracle聚簇索引CREATECLUSTER21Oracle聚簇索引这样可以让两个表同时用上聚簇索引。保证两个表的记录按照depno值尽量存放到同一个物理块当中。Oracle聚簇索引这样可以让两个表同时用上聚簇索引。保证22使用索引的一些规则1.索引对大表最有用，不要在小表上加索引.2.为每个表中的主码指定一个唯一索引。3.索引对于那些频繁出现在SQL命令中的where子句中的列最有用，不管这些列在选择中用来限定行还是为了表连接。4.当一个属性中存在很多不同的值时，可以使用索引。Oracle建议当一个属性中有少于30个不同值时，索引不是很有用，当属性中有100或更多不同值时索引就很明显地有用了。相似地，只有当使用索引进行查询的结果不超过文件中所有记录总数的20%时，使用索引才有帮助。5.检查你的DBMS对索引的限制，即便要在每个表允许的索引个数上。许多系统不超过16个索引而且限制每个索引键值的大小。对一个表创建的索引数一般不超过5个.使用索引的一些规则1.索引对大表最有用，不要在小表上加索引.237.对于包含空值的属性建立索引时要小心，在很多DBMS里有空值的行不能在索引中作为参照。8.时常需要做删除、更新、插入操作的表不要创建索引.9.将表和索引建立在不同的表空间内(TABLESPACES).不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里.同时,确保数据表空间和索引表空间置于不同的硬盘上.减少I/O竞争.7.对于包含空值的属性建立索引时要小心，在很多DBMS里有24SQL优化的一些规则:(有些规则只有在基于规则的优化器里有效).SQL优化的一些规则:251>选择最有效率的表名顺序：1.把记录少的表放在from子句的最后面一个表.2.如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表.原因：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

1>选择最有效率的表名顺序：262>WHERE子句中的连接顺序：

ORACLE采用自右向左的顺序解析WHERE子句,,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

3.>SELECT子句中避免使用‘*‘

ORACLE在解析的过程中,需要通过查询数据字典将'*'依次转换成所有的列名.4.>使用表的别名(Alias)当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)

5.>减少访问数据库的次数:当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.2>WHERE子句中的连接顺序：276.>(可能的话)用TRUNCATE替代DELETE.当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到执行删除命令之前的状况.而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

(TRUNCATE只在删除全表里的记录时适用,TRUNCATE是DDL不是DML)

7.>(可能的话)使用COMMIT

只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:

COMMIT所释放的资源:

a.回滚段上用于恢复数据的信息.

b.被程序语句获得的锁

c.redologbuffer中的空间

d.ORACLE为管理上述3种资源中的内部花费6.>(可能的话)用TRUNCATE替代DELETE.288.>(可能的话)用Where子句替换HAVING子句尽量少使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

9.>(某些情况下)可以用EXISTS替代IN.NOTEXISTS替代NOTIN性能比较:1.Select*fromt1wherexin(selectyfromt2)2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)当t1记录比较少,t2比较大时适合用exists(exists大部分情况会利用到index),当子查询记录集很小时用in比较合适.8.>(可能的话)用Where子句替换HAVING子句29原因分析：

1.Select*fromT1wherexin(selectyfromT2)

执行的过程相当于:

select*

fromt1,(selectdistinctyfromt2)t3wheret1.x=t3.y;

2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)

执行的过程相当于:

forxin(select*fromt1)loop

if(exists(select1fromt2wheret2.y=t1.x)

then

OUTPUTTHERECORD

endif

endloop

这样表

T1要被完全扫描一遍.所以可以得出结论:当t1记录比较少,t2比较大时适合用exists(exists大部分情况会利用到index),当子查询记录集很小时用in比较合适.原因分析：3010.>用表连接替换EXISTS

改进第9打优化规则的例子.11.>用EXISTS替换DISTINCTEXISTS使查询更为迅速,因为RDBMS核心模块在子查询的条件一旦满足后立刻返回结果.DISTINCT会先进行排序,然后会根据排序后的顺序去除相同的行.12.>使用显式的游标(CURSOR)使用隐式的游标,将会执行两次操作.第一次检索记录,第二次检查TOOMANYROWS这个exception.而显式游标不执行第二次操作.10.>用表连接替换EXISTS

改进第9打优化规则的例子.3111-例：1.(低效)SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPE

WHERED.DEPT_NO=E.DEPT_NO;2.高效:

SELECTDEPT_NO,DEPT_NAMEFROMDEPTD

WHEREEXISTS(SELECT*

FROMEMPE

WHEREE.DEPT_NO=D.DEPT_NO);

11-例：3213.用索引提高效率通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.

除了那些LONG或LONGRAW数据类型,你可以索引几乎所有的列.在大型表中使用索引特别有效.

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.定期的重构索引是有必要的.ALTERINDEX<INDEXNAME>REBUILD<TABLESPACENAME>

13.用索引提高效率33Oracle索引优化规则Oracle索引优化规则34索引优化规则：1.like件中不要以通配符(WILDCARD)开始,否则索引将不被采用.

例:SELECTLODGINGFROMLODGINGWHEREMANAGERLIKE‘％HANMAN';2.避免在索引列上使用计算或改变索引列的类型或使用‘!=’及<>例:SELECT…FROMDEPTWHERESAL*12>25000;SELECT…FROMEMPWHEREEMP_TYPE=to_char(123);

select….WhereACCOUNT_NAME||ACCOUNT_TYPE='AMEXA';select…whereempno!=8888;3.避免在索引列上使用NOT.4.用>=替代>.高效:SELECT*FROMEMPWHEREDEPTNO>=4

低效:SELECT*FROMEMPWHEREDEPTNO>3

两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.索引优化规则：355.用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,那就需要返回记录最少的索引列写在最前面注意：WHEREKEY1=10（返回最少记录）

ORKEY2=20（返回最多记录）

ORACLE内部将以上转换为

WHEREKEY1=10AND（（NOTKEY1=10）ANDKEY2=20）5.用UNION替换OR(适用于索引列)36

6.避免在索引列上使用ISNULL和ISNOTNULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录.对于复合索引，如果每个列都为空，索引中同样不存在此记录.如果至少有一个列不为空，则记录存在于索引中．

(建议：可以给null值的字段设置一个默认值))

7. 如果索引是建立在多个列上,索引时段需要放在where条件的第一个条件(Oracle8i之前),Oracle8i之后允许跳跃式索引.8.(可能的话)用UNION-ALL替换UNION.

UNION-ALL就是做简单的合并，不会进行排序，UNION先做简单的合并，然后做进行排序，最后去除重复的记录。6.避免在索引列上使用ISNULL和ISNOTNU379.避免使用耗费资源的操作带有DISTINCT,UNION,MINUS,INTERSECT,ORDERBY的SQL语句会启动SQL引擎.

执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.

例如,一个UNION查询,其中每个查询都带有GROUPBY子句,GROUPBY会触发嵌入排序(NESTEDSORT);这样,每个查询需要执行一次排序,然后在执行UNION时,又一个唯一排序(SORTUNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行.嵌入的排序的深度会大大影响查询的效率.

通常,带有UNION,MINUS,INTERSECT的SQL语句都可以用其他方式重写.

9.避免使用耗费资源的操作38

theend!Oracle优化器模式与Oracle索引优化规则课件39Oracle优化器介绍.Oracle索引介绍.SQL优化规则介绍.索引优化规则介绍.Oracle优化器介绍.40Oracle优化器模式:Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的.Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具.Oracle的优化器有两种优化方式：基于规则的优化方式(Rule-BasedOptimization,简称为RBO)和基于代价的优化方式(Cost-BasedOptimization,简称为CBO).RBO方式︰优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引.CBO方式︰CBO是看语句的代价,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的.在Oracle8及以后的版本,Oracle列推荐用CBO的方式。Oracle优化器模式:41优化器的优化模式(OptermizerMode):包括Rule,Choose,Firstrows,Allrows这四种方式.Rule:不用多说,即走基于规则的方式。rboChoose:指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。FirstRows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。对于排序分页页显示这种查询尤其适用.AllRows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式.查看缺省的Oracle优化器:SQL>showparametersoptimizer_mode;可以在init文件中对整个instance的所有会话设置.也可以单独对某个会话设置：SQL>ALTERSESSIONSEToptimizer_mode

=FIRST_ROWS;

优化器的优化模式(OptermizerMode):包括R42查看统计信息：1.selecttt.table_name,tt.num_rows,tt.blocks,tt.empty_blocks,tt.avg_row_lenfromdba_tablesttwherett.owner='SCOTT';2.selectttt.index_name,ttt.num_rows,ttt.distinct_keys,ttt.avg_leaf_blocks_per_key,ttt.clustering_factorfromdba_indexestttwherettt.owner='SCOTT';人工进行统计：对某一个用户下的所有表和索引执行统计分析:executedbms_stats.gather_schema_stats(ownname=>‘SCOTT',cascade=>true);对单个表执行统计分析:EXECUTEdbms_stats.gather_table_stats(ownname=>‘SCOTT',tabname=>‘EMP',estimate_percent=>50,cascade=>true).查看统计信息：43从OracleDatabase10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息,调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息.默认情况下在工作日晚上10：00-6：00和周末全天开启.一起运行的还有另外一个Job:AUTO_SPACE_ADVISOR_JOB.可以通过以下查询这个JOB的运行情况：

select*fromDba_Scheduler_JobswhereJOB_NAME='GATHER_STATS_JOB'.关闭自动统计功能：SQL>execBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');从OracleDatabase10g开始，Ora44Oracle索引介绍Oracle索引介绍45Oracle索引简介索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据(加快查询)；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。查询DBA_INDEXES视图可得到表中所有索引的列表，注意只能通过USER_INDEXES的方法来检索模式(schema)的索引。访问USER_IND_COLUMNS视图可得到一个给定表中被索引的特定列。通过每个行的ROWID，索引Oracle提供了访问单行数据的能力。ROWID其实就是直接指向单独行的线路图。Oracle索引简介索引是建立在表的一列或多个列上的辅助对象46索引分类逻辑上:单列索引,多列索引,唯一索引,非惟一索引。物理上:B*-Tree索引，反向索引，位图索引。索引分类逻辑上:47单列索引和复合索引单列索引是基于单个列所建立的索引。多列索引是基于两列或多列所建立的索引。单列索引

Createindexemp_ind1onemp(ename);复合索引

Createindexemp_ind2onemp(ename,job);单列索引和复合索引单列索引是基于单个列所建立的索引。多列索引48惟一索引和非惟一索引惟一索引是索引列值不能重复的索引。非惟一索引是索引列值可以重复的索引。无论是惟一索引还是非惟一索引，索引列都允许NULL。惟一索引和非惟一索引惟一索引是索引列值不能重复的索引。49B*-tree索引B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branchblock）和叶块（leafblock）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址.创建索引：

1:createindexSTUDENT_IND_nameonSTUDENT(NAME)tablespaceUSERS;2:createindexSTUDENT_IND_nameonSTUDENT(NAME,AGE)tablespaceUSERS;B*-tree索引B*Tree索引是最常见的索引结构，默认建50B*-tree索引B*-tree索引51B*-tree索引假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75－100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如wherecolumn>20andcolumn<80，那么会先定位到第一个包含20的叶块，然后横向查找其他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。B*-tree索引假设我们要找索引中值为80的行，从索引树的52反向索引反向索引是B*Tree索引的一个分支，它的设计是为了运用在某些特定的环境下的。Oracle推出它的主要目的就是为了降低在并行服务器（OracleParallelServer）环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节。建立反向索引:createindexreversed_ind_emponemp(Order_ID)reverse;反向索引反向索引是B*Tree索引的一个分支，它的设计是为53反向索引如果有一个名称为order_id的列值在某行上是12345，oracle就将它颠倒为54321。这样连续的几个数不会在同一个页块里。不过反向索引又一个缺点就是不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用，例如，wherecolumn>value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。

注意：只有索引中的值是逆向的，表中的值保持不变。反向索引如果有一个名称为order_id的列值在某行上是1254位图索引位图索引主要用于决策支持系统或静态数据，不支持行级锁定。位图索引最好用于低cardinality列（即列的唯一值除以行数为一个很小的值，接近零），例如又一个“性别”列，列值有“Male”，“Female”，“Null”等3种，但一共有300万条记录，那么3/3000000约等于0，这种情况下最适合用位图索引。

创建位图索引:createbitmapindexinx_bitmap_emponemp(sex);位图索引位图索引主要用于决策支持系统或静态数据，不支持行级锁55位图索引的格式行值

1

2

3

4

5

6

7

8

9

10

Male

1

0

0

0

0

0

0

0

1

1

Female

0

1

1

1

0

0

1

1

0

0

Null

0

0

0

0

1

1

0

0

0

0

位图索引的格式行值

1

2

3

4

5

6

7

856位图索引如果搜索where

gender=’Male’,要统计性别是”Male”的列行数的话，Oracle很快就能从位图中找到共3行即第1，9，10行是符合条件的；如果要搜索where

gender=’Male’

or

gender=’Female’的列的行数的话，也很容易从位图中找到共8行即1，2，3，4，7，8，9，10行是符合条件的。如果要搜索表的值的话，那么Oracle会用内部的转换函数将位图中的相关信息转换成rowid来访问数据块。

位图索引如果搜索where

gender=’Male’,要统57聚簇聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数据的目的。聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。一个表最多只能有一个聚簇索引。

聚簇聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数58使用Oracle聚簇索引在Oracle当中，聚簇不是索引的组织形式，而是表的组织形式。多用于表之间的连接字段。例:dept(deptno,

dnma,e

loc)

和表

emp(empno,

ename,

job,

mgr,

...

deptno),

常在deptno上进行连接,可以针对deptno字段建立聚簇，然后建立基于该聚簇的索引，并让两个表都使用上该聚簇。使用Oracle聚簇索引在Oracle当中，聚簇不是索59Oracle聚簇索引CREATECLUSTERpersonnel(department_numberNUMBER(2))SIZE512STORAGE(INITIAL100KNEXT50K);CREATETABLEemp(empnoNUMBERPRIMARYKEY,enameVARCHAR2(10)NOTNULLCHECK(ename=UPPER(ename)),jobVARCHAR2(9),mgrNUMBER,commNUMBER(9,0)DEFAULTNULL,deptnoNUMBER(2)NOTNULL)CLUSTERpersonnel(deptno);

CREATETABLEdept(deptnoNUMBER(2),dnameVARCHAR2(9),locVARCHAR2(9))CLUSTERpersonnel(deptno);

CREATEINDEXidx_personnelONCLUSTERpersonnel;Oracle聚簇索引CREATECLUSTER60Oracle聚簇索引这样可以让两个表同时用上聚簇索引。保证两个表的记录按照depno值尽量存放到同一个物理块当中。Oracle聚簇索引这样可以让两个表同时用上聚簇索引。保证61使用索引的一些规则1.索引对大表最有用，不要在小表上加索引.2.为每个表中的主码指定一个唯一索引。3.索引对于那些频繁出现在SQL命令中的where子句中的列最有用，不管这些列在选择中用来限定行还是为了表连接。4.当一个属性中存在很多不同的值时，可以使用索引。Oracle建议当一个属性中有少于30个不同值时，索引不是很有用，当属性中有100或更多不同值时索引就很明显地有用了。相似地，只有当使用索引进行查询的结果不超过文件中所有记录总数的20%时，使用索引才有帮助。5.检查你的DBMS对索引的限制，即便要在每个表允许的索引个数上。许多系统不超过16个索引而且限制每个索引键值的大小。对一个表创建的索引数一般不超过5个.使用索引的一些规则1.索引对大表最有用，不要在小表上加索引.627.对于包含空值的属性建立索引时要小心，在很多DBMS里有空值的行不能在索引中作为参照。8.时常需要做删除、更新、插入操作的表不要创建索引.9.将表和索引建立在不同的表空间内(TABLESPACES).不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里.同时,确保数据表空间和索引表空间置于不同的硬盘上.减少I/O竞争.7.对于包含空值的属性建立索引时要小心，在很多DBMS里有63SQL优化的一些规则:(有些规则只有在基于规则的优化器里有效).SQL优化的一些规则:641>选择最有效率的表名顺序：1.把记录少的表放在from子句的最后面一个表.2.如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表.原因：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

1>选择最有效率的表名顺序：652>WHERE子句中的连接顺序：

ORACLE采用自右向左的顺序解析WHERE子句,,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

3.>SELECT子句中避免使用‘*‘

ORACLE在解析的过程中,需要通过查询数据字典将'*'依次转换成所有的列名.4.>使用表的别名(Alias)当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)

5.>减少访问数据库的次数:当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.2>WHERE子句中的连接顺序：666.>(可能的话)用TRUNCATE替代DELETE.当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到执行删除命令之前的状况.而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

(TRUNCATE只在删除全表里的记录时适用,TRUNCATE是DDL不是DML)

7.>(可能的话)使用COMMIT

只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:

COMMIT所释放的资源:

a.回滚段上用于恢复数据的信息.

b.被程序语句获得的锁

c.redologbuffer中的空间

d.ORACLE为管理上述3种资源中的内部花费6.>(可能的话)用TRUNCATE替代DELETE.678.>(可能的话)用Where子句替换HAVING子句尽量少使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

9.>(某些情况下)可以用EXISTS替代IN.NOTEXISTS替代NOTIN性能比较:1.Select*fromt1wherexin(selectyfromt2)2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)当t1记录比较少,t2比较大时适合用exists(exists大部分情况会利用到index),当子查询记录集很小时用in比较合适.8.>(可能的话)用Where子句替换HAVING子句68原因分析：

1.Select*fromT1wherexin(selectyfromT2)

执行的过程相当于:

select*

fromt1,(selectdistinctyfromt2)t3wheret1.x=t3.y;

2.select*fromt1whereexists(select1fromt2wheret2.y=t1.x)

执行的过程相当于:

forxin(select*fromt1)loop

if(exists(select1fromt2wheret2.y=t1.x)

then

OUTPUTTHERECORD

endif

endloop

这样表

T1要被完全扫描一遍.所以可以得出结论:当t1记录比较少,t2比较大时适合用exists(exists大部分情况会利用到index),当子查询记录集很小时用in比较合适.原因分析：6910.>用表连接替换EXISTS

改进第9打优化规则的例子.11.>用EXISTS替换DISTINCTEXISTS使查询更为迅速,因为RDBMS核心模块在子查询的条件一旦满足后立刻返回结果.DISTINCT会先进行排序,然后会根据排序后的顺序去除相同的行.12.>使用显式的游标(CURSOR)使用隐式的游标,将会执行两次操作.第一次检索记录,第二次检查TOOMANYROWS这个exception.而显式游标不执行第二次操作.10.>用表连接替换EXISTS

改进第9打优化规则的例子.7011-例：1.(低效)SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPE

WHERED.DEPT_NO=E.DEPT_NO;2.高效:

SELECTDEPT_NO,DEPT_NAMEFROMDEPTD

WHEREEXISTS(SELECT*

FROMEMPE

WHEREE.DEPT_NO=D.DEPT_NO);

11-例：7113.用索引提高效率通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.

除了那些LONG或LONGRAW数据类型,你可以索引几乎所有的列.在大型表中使用索引特别有效.

虽然使