Oracle索引技术简介.docx

Oracle索引在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。对于数据库来说，索引是一个必选项，但对于现在的各种大型数据库来说，索引可以大大提高数据库的性能，以至于它变成了数据库不可缺少的一部分。1、索引的概念索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle会自动的引用该索引，先从索引表中查询出符合条件记录的ROWID，由于ROWID是记录的物理地址，因此可以根据ROWID快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。2、建立索引的目的(1)提高对表的查询速度(2)对表有关列的取值进行检查但是，对表进行insert,update,delete处理时，由于要将表的存放位置记录到索引项中而会降低一些速度。注意：一个基表不能建太多的索引；空值不能被索引--创建索引3、什么时候使用索引如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。当从很多行的表中查询少数行时，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时，索引就非常有用。索引的缺点：Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。4、创建索引的一些规则:1、权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。2、把索引与对应的表放在不同的表空间。当读取一个表时，表与索引是同时进行的。如果表与索引在一个表空间里就会产生资源竞争，放在两个表这空就可并行执行。3、最好使用一样大小块。Oracle默认五块，读一次I/O，如果你定义6个块或10个块都需要读取两次I/O。最好是5的整数倍更能提高效率。4、如果一个表很大，建立索引的时间很长，因为建立索引也会产生大量的redo信息，所以在创建索引时可以设置不产生或少产生redo信息。只要表数据存在，索引失败了大不了再建，所以可以不需要产生redo信息。5、建索引的时候应该根据具体的业务SQL来创建，特别是where条件，还有where条件的顺序，尽量将过滤大范围的放在后面（GMSFHM），因为SQL执行是从后往前执行的。5、索引操作5.1创建索引创建索引：CREATE[UNIQUE]INDEXindex_nameONtable_name(column_name[,column_name…])实例:为EMP表的ENAME列创建创建唯一索引，为EMP表的工资列创建普通索引，把JOB列先变为小写再创建索引。CREATEUNIQUEINDEXUQ_ENAME_IDXONEMP(ENAME);CREATEINDEXIDX_SALONEMP(SAL);CREATEINDEXIDX_JOB_LOWERONEMP(LOWER(JOB));5.2修改索引修改索引:主要任务是修改已存在索引的存储参数适应增长的需要或者重新建立索引。简要语法结构如下:ALTER[UNIQUE]INDEX[user.]index[INITRANSn][MAXTRANSn]REBUILD[STORAGEn]其中：REBUILD是根据原来的索引结构重新建立索引，实际是删除原来的索引后再重新建立。例：alterindexIDX_SALrebuildstorage(initial1mnext512k);ALTERINDEXIDX_SALREBUILDREVERSE;Oracle8i的新功能可以对索引的无用空间进行合并：ALTERINDEX...COALESCE;（整合索引碎片）例如：ALTERINDEXIDX_SALCOALESCE;****5.3删除索引删除索引：当不需要时可以将索引删除以释放出硬盘空间。命令如下：DROPINDEX[schema.]indexname例如：sql>dropindexIDX_SAL;注：当表结构被删除时，有其相关的所有索引也随之被删除。5.4查询索引查询索引：显示表的所有索引：selectindex_name,index_typefromuser_indexeswheretable_name='表名';显示索引列：s