包含oracle索引如何维护的词条

高手救我，oracle中如何对频繁更新的表索引进行维护

你说的慢一半，是在你插入了50万数据的时候，估计是查询慢了吧。因为按你说的还没到删除的时候呢。查询慢要看看你的查询的语句的执行计划，是否使用了合适的索引。如果索引错了，那么慢是正常的。可以用hint指定索引，从而保证执行计划稳定。

专业领域包括成都网站建设、网站设计、商城网站建设、微信营销、系统平台开发， 与其他网站设计及系统开发公司不同，创新互联的整合解决方案结合了帮做网络品牌建设经验和互联网整合营销的理念，并将策略和执行紧密结合，为客户提供全网互联网整合方案。

对于你这样的周期性维护的大表，可以做31个分区，每天一个，一个月内循环使用。不需要保留的数据可以以天为单位truncate掉一个或多个分区的数据，索引采用分区索引，truncate分区不影响索引的使用。

有一种猜测，是你的索引建的不对。

你的查询语句的查询条件是什么？是单列查询吗？如果pwd指password，至少我觉得不会有按password单列查询的语句吧。

Oracle数据访问和索引的使用

· 通过全表扫描的方式访问数据；

· 通过ROWID访问数据；

· 通过索引的方式访问数据；

· Oracle顺序读取表中所有的行，并逐条匹配WHERE限定条件。

· 采用多块读的方式进行全表扫描，可以有效提高系统的吞吐量，降低I/O次数。

· 即使创建索引，Oracle也会根据CBO的计算结果，决定是否使用索引。

注意事项：

· 只有全表扫描时才可以使用多块读。该方式下，单个数据块仅访问一次。

· 对于数据量较大的表，不建议使用全表扫描进行访问。

· 当访问表中的数据量超过数据总量的5%—10%时，通常Oracle会采用全表扫描的方式进行访问。

· 并行查询可能会导致优化器选择全表扫描的方式。1.2ROWID访问表

· Rowid是数据存放在数据库中的物理地址，能够唯一标识表中的一条数据。

· Rowid指出了一条记录所在的数据文件、块号以及行号的位置，因此通过ROWID定位单行数据是最快的方法。

注意事项：

· Rowid作为一个伪列，其数值并不存储在数据库中，当查询时才进行计算。

· Rowid除了在同一集簇中可能不唯一外，每条记录的Rowid唯一。1.3 INDEX访问表

· 通过索引查找相应数据行的Rowid，再根据Rowid查找表中实际数据的方式称为“索引查找”或者“索引扫描”。

· 一个Rowid对应一条数据行（根据Rowid查找结果，仅需要对Rowid相应数据的数据块进行一次I/O操作），因此该方式属于“单块读”。

· 对于索引，除了存储索引的数据外，还保存有该数据对应的Rowid信息。

· 索引扫描分为两步：1）扫描索引确定相应的Rowid信息。 2）根据Rowid从表中获得对应的数据。

注意事项：

· 对于选择性高的数据行，索引的使用会提升查询的性能。但对于DML操作，尤其是批量数据的操作，可能会导致性能的降低。

· 全表扫描的效率不一定比索引扫描差，关键看数据在数据块上的具体分布。

索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。

(1) 单列索引

单列索引是基于单个列所建立的索引。

(2) 复合索引

复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同。

(1) 重命名索引

(2) 合并索引

（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

(3) 重建索引

方式一：删除原来的索引，重新建立索引

当不需要时可以将索引删除以释放出硬盘空间。命令如下：

例如：

注：当表结构被删除时，有其相关的所有索引也随之被删除。

方式二： Alter index 索引名称 rebuild;

· 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

· 索引可以大大加快数据的检索速度，这是创建索引的最主要的原因。

· 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

· 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

· 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

· 索引的层次不要超过4层。

· 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

· 除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

· 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

· 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。

1) 不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

1) 应该建索引的列

· 在经常需要搜索的列上，可以加快搜索的速度；

· 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

· 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

· 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

· 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

· 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

2) 不应该建索引的列

· 在大表上建立索引才有意义，小表无意义。

· 对于那些在查询中很少使用或者参考的列不应该创建索引。

· 对于那些只有很少数据值的列也不应该增加索引。比如性别，在查询的结果中，结果集的数据行占了表中数据行的很大比例，。增加索引，并不能明显加快检索速度。

· 对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

· 当修改性能远远大于检索性能时，不应该创建索引。

一个表中有几百万条数据，对某个字段加了索引，但是查询时性能并没有什么提高，这主要可能是oracle的索引限制造成的。Oracle的索引有一些索引限制，在这些索引限制发生的情况下，即使已经加了索引，oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的系统开销造成性能更差。

下面的查询即使在djlx列有索引，查询语句仍然执行一次全表扫描。

把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

特别注意：通过把不等于操作符改成OR条件，就可以使用索引，避免全表扫描。

使用IS NULL或IS NOT NULL同样会限制索引的使用。因此在建表时，把需要索引的列设成NOT NULL。如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引）。

如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引）

也是比较难于发现的性能问题之一。比如：bdcs_qlr_xz中的zjh是NVARCHAR2类型,在zjh字段上有索引。如果使用下面的语句将执行全表扫描。

因为Oracle会自动把查询语句改为

特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。

(1) 索引无效

(2) 索引有效

如何加速Oracle大批量数据处理？

一、 提高DML操作的办法：\x0d\x0a简单说来：\x0d\x0a1、暂停索引，更新后恢复.避免在更新的过程中涉及到索引的重建.\x0d\x0a2、批量更新，每更新一些记录后及时进行提交动作.避免大量占用回滚段和或临时表空间.\x0d\x0a3、创建一临时的大的表空间用来应对这些更新动作．\x0d\x0a\x0d\x0a4、批量更新，每更新一些记录后及时进行提交动作.避免大量占用回滚段和或临时表空间.\x0d\x0a\x0d\x0a5、创建一临时的大的表空间用来应对这些更新动作．\x0d\x0a\x0d\x0a6、加大排序缓冲区\x0d\x0a alter session set sort_area_size=100000000;\x0d\x0a insert into tableb select * from tablea;\x0d\x0a commit;\x0d\x0a\x0d\x0a如果UPDATE的是索引字段，就会涉及到索引的重建，暂停索引不会提高多少的速度，反而有可能降低UPDATE速度，\x0d\x0a因为在更新是索引可以提高数据的查询速度，重建索引引起的速度降低影响不大。\x0d\x0a\x0d\x0aORACLE优化修改参数最多也只能把性能提高15%，大部分都是SQL语句的优化！\x0d\x0a\x0d\x0aupdate总体来说比insert要慢 ：\x0d\x0a几点建议： \x0d\x0a 1、如果更新的数据量接近整个表，就不应该使用index而应该采用全表扫描 \x0d\x0a 2、减少不必要的index，因为update表通常需要update index \x0d\x0a 3、如果你的服务器有多个cpu，采用parellel hint，可以大幅度的提高效率\x0d\x0a 另外，建表的参数非常重要，对于更新非常频繁的表，建议加大PCTFREE的值，以保证数据块中有足够的空间用于UPDATE， 从而降低CHAINED_ROWS。 \x0d\x0a\x0d\x0a二、 各种批量DML操作：\x0d\x0a（1）、oracle批量拷贝：\x0d\x0aset arraysize 20\x0d\x0a set copycommit 5000\x0d\x0a copy from username/password@oraclename append table_name1\x0d\x0a using select * from table_name2;\x0d\x0a （2）、常规插入方式：\x0d\x0a insert into t1 select * from t;\x0d\x0a 为了提高速度可以使用下面方法，来减少插入过程中产生的日志:\x0d\x0a alter table t1 nologging;\x0d\x0ainsert into t1 select * from t;\x0d\x0acommit;\x0d\x0a (3)、CTAS方式：\x0d\x0a create table t1\x0d\x0aas\x0d\x0aselect * from t;\x0d\x0a为了提高速度可以使用下面方法，来减少插入过程中产生的日志，并且可以制定并行度:\x0d\x0acreate table t1 nologging parallel(degree 2) as select * from t;\x0d\x0a （4）、Direct-Path插入：\x0d\x0a insert /*+append*/ into t1 select * from t;\x0d\x0a commit;\x0d\x0a 为了提高速度可以使用下面方法，来减少插入过程中产生的日志：\x0d\x0a alter table t1 nologging;\x0d\x0a insert /*+append*/ into t1 select * from t;\x0d\x0a \x0d\x0a Direct-Path插入特点：\x0d\x0a1、 append只在insert ? select ?中起作用，像insert /*+ append */ into t values(?)这类的语句是不起作用的。在update、delete操作中，append也不起作用。\x0d\x0a2、 Direct-Path会使数据库不记录直接路径导入的数据的重做日志，会对恢复带来麻烦。\x0d\x0a3、 Direct-Path直接在表段的高水位线以上的空白数据块中写数据，不会重用高水位线以下的空间，会对空间的使用造成一定的浪费，对查询的性能也会造成一定的影响。而常规插入会优先考虑使用高水位线之下有空闲空间存在的数据块。因此理论上Direct-Path插入会比常规插入速度更快，因为Direct-Path直接使用新数据块，而常规插入要遍历freelist获取可用空闲数据块，如果同 nologging 配合，这种速度优势会更加明显。\x0d\x0a4、 以append方式插入记录后，要执行commit，才能对表进行查询。否则会出现错误：ORA-12838: 无法在并行模式下修改之后读/修改对象。\x0d\x0a5、 用append导入数据后，如果没有提交或者回滚，在其他会话中任何对该表的DML都会被阻塞（不会报错），但对该表的查询可以正常执行。\x0d\x0a6、 在归档模式下，要把表设置为nologging，然后以append方式批量添加记录，才会显著减少redo数量。在非归档模式下，不必设置表的 nologging属性，即可减少redo数量。如果表上有索引，则append方式批量添加记录，不会减少索引上产生的redo数量，索引上的redo 数量可能比表的redo数量还要大。\x0d\x0a7、 数据直接插入数据文件，绕过buffer cache并且忽略了引用完整性约束。\x0d\x0a8、 不管表是否在nologging 下，只要是 direct insert，就不会对数据内容生成undo。\x0d\x0a9、 Oracle在Direct-Path INSERT 操作末尾，对具有索引的表执行索引维护，这样就避免了在drop掉索引后，再rebuild。\x0d\x0a10、 Direct-Path INSERT比常规的插入需要更多的空间。因为它将数据插入在高水位之上。并行插入非分区表需要更多的空间，因为它需要为每一个并行线程创建临时段。\x0d\x0a11、 在插入期间，数据库在表上获得排他锁，用户不能在表上执行并行插入、更新或者删除操作，并行的索引创建和build也不被允许。但却可以并行查询，但查询返回的是插入之前的结果集。\x0d\x0a （5）、并行DML：\x0d\x0a 如果你的服务器有多个cpu，采用parellel hint，可以大幅度的提高效率\x0d\x0a ALTER SESSION ENABLE PARALLEL DML;\x0d\x0a\x0d\x0a INSERT /*+ PARALLEL(tableA, 2) */INTO tableA \x0d\x0a SELECT * FROM tableB;\x0d\x0a\x0d\x0a 为了提高速度可以使用下面方法，来减少插入过程中产生的日志：\x0d\x0a\x0d\x0a INSERT /*+ PARALLEL(tableA, 2) */INTO tableA NOLOGGING\x0d\x0a SELECT * FROM tableB;\x0d\x0a\x0d\x0aoracle默认并不会打开PDML，对DML语句必须手工启用。即需要执行\x0d\x0aalter table enable parallel dml命令。\x0d\x0a \x0d\x0a并行DML特点：\x0d\x0a1、在并行DML模式中，默认的就是DIRECT-PATH插入，为了运行并行DML模式，必须满足以下条件：\x0d\x0aa、必须是Oracle企业版；\x0d\x0ab、必须在session中使并行DML生效，执行以下sql语句：\x0d\x0aALTER SESSION { ENABLE | FORCE } PARALLEL DML;\x0d\x0ac、必须指定table的并行属性，在创建的时候或者其他时候，或者在insert操作时使用“PARALLEL”提示。\x0d\x0ad、为了使Direct-Path Insert模式失效，在INSERT语句中指定“NOAPPEND”提示，覆盖并行DML模式。\x0d\x0a 2、并行Direct-Path INSERT到分区表：\x0d\x0a 类似于serial Direct-Path INSERT，每个并行操作分配给一个或者多个分区，每个并行操作插入数据到各自的分区段的高水位标志之上，commit之后，用户就能看到更新的数据。\x0d\x0a 3、并行Direct-Path INSERT到非分区表：\x0d\x0a 每个并行执行分配一个新的临时段，并插入数据到临时段。当commit运行后，并行执行协调者合并新的临时段到主表段，用户就能看到更新的数据。\x0d\x0a 4、Direct-Path INSERT可以使用Log或者不使用Log。\x0d\x0a 5、另外不得不说的是，并行不是一个可扩展的特性，只有在数据仓库或作为DBA等少数人的工具在批量数据操作时利于充分利用资源，而在OLTP环境下使用并行需要非常谨慎。事实上PDML还是有比较多的限制的，例如不支持触发器，引用约束，高级复制和分布式事务等特性，同时也会带来额外的空间占用，PDDL同 样是如此。

Oracle如何维护索引

对三个字段建立索引：

create index Stuname on student(name);

create index Stusex on student(sex);

create index Stugrade on student(grade);

注意的问题，考虑是不是要建立唯一索引（unique），如果有学号的话，可以考虑建立唯一索引引。

再就是对经常查询，但又相对稳定的可以建立聚簇索引，提高查询效率

在Oracle中，索引有什么优点和缺点？

一：索引是什么：

通俗的来讲，索引在表中的作用，相当于书的目录对书的作用。索引与表一样，也属于段的一种。里面存放了用户的数据，跟表一样需要占用磁盘空间。索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引。

二：优点：

①可以加快数据的检索速度。

②可以加速表和表之间的连接。

③使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间。

三：缺点：

①创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

②索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。

③当对表中的数据进行增加、删除和修改的时候，索引需要动态的维护，这样就降低了数据的维护速度。

网页名称：包含oracle索引如何维护的词条
文章来源：http://6mz.cn/article/heoicp.html