2.1  B树索引 (默认类型)

 B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。B树索引最多可以包括32列。

 在下图的例子中，B树索引位于雇员表的last_name列上。这个索引的二元高度为3；接下来，Oracle会穿过两个树枝块(branch block)，到达包含有ROWID的树叶块。在每个树枝块中，树枝行包含链中下一个块的ID号。

 树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其中列上包含NULL值，这一行就会处于包含NULL值的索引列中，且将被处理为空(视为NULL)。                

 技巧：索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了I/O量。

 B-tree 特点：

   适合与大量的增、删、改（OLTP）

 不能用包含OR操作符的查询；

 适合高基数的列（唯一值多）

 典型的树状结构；

 每个结点都是数据块；

 大多都是物理上一层、两层或三层不定，逻辑上三层；

 叶子块数据是排序的，从左向右递增；

 在分支块和根块中放的是索引的范围；

 

 

 2.2  位图索引

 位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。

 例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。

 Bitmapt 特点：

 适合与决策支持系统；

 做UPDATE代价非常高；

 非常适合OR操作符的查询；

 基数比较少的时候才能建位图索引；

 技巧：对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。

 当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。

 示例

 下面来看一个示例表PARTICIPANT，该表包含了来自个人的调查数据。列Age_Code、Income_Level、Education_Level和Marital_Status都包括了各自的位图索引。下图显示了每个直方图中的数据平衡情况，以及对访问每个位图索引的查询的执行路径。图中的执行路径显示了有多少个位图索引被合并，可以看出性能得到了显著的提高。               

 如上图图所示，优化器依次使用4个单独的位图索引，这些索引的列在WHERE子句中被引用。每个位图记录指针(例如0或1)，用于指示表中的哪些行包含位图中的已知值。有了这些信息后，Oracle就执行BITMAP AND操作以查找将从所有4个位图中返回哪些行。该值然后被转换为ROWID值，并且查询继续完成剩余的处理工作。注意，所有4个列都有非常低的基数，使用索引可以非常快速地返回匹配的行。

 技巧：在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。

 B树索引作为NORMAL列出；而位图索引的类型值为BITMAP。

 技巧：如果要查询位图索引列表，可以在USER _INDEXES视图中查询index_type列。

 建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。B树索引的索引值中包含ROWID，这样Oracle就可以在行级别上锁定索引。位图索引存储为压缩的索引值，其中包含了一定范围的ROWID，因此Oracle必须针对一个给定值锁定所有范围内的ROWID。这种锁定类型可能在某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。

 位图索引的使用限制：

 基于规则的优化器不会考虑位图索引。

 当执行ALTER TABLE语句并修改包含有位图索引的列时，会使位图索引失效。

 位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。

 位图索引不能被声明为唯一索引。

 位图索引的最大长度为30。

 技巧：不要在繁重的OLTP环境中使用位图索引

 

 

 2.3  HASH索引

 使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时，也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上，并且将HASH索引作为WHERE子句中的确切匹配，Oracle就可以通过执行一个HASH函数和I/O来访问数据——而通过使用一个二元高度为4的B树索引来访问数据，则需要在检索数据时使用4个I/O。如下图所示，其中的查询是一个等价查询，用于匹配HASH列和确切的值。Oracle可以快速使用该值，基于HASH函数确定行的物理存储位置。

 HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出，然后造成额外的I/O。如果不同HASH值的数目已经被低估，您就必须在重建这个集群之后改变这个值。

 ALTER CLUSTER命令不能改变HASH键的数目。HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间，HASH集群可能就不是最好的选择。如果应用程序经常在集群表上进行全表扫描，HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量，全表扫描可能非常消耗资源。

 在实现HASH集群之前一定要小心。您需要全面地观察应用程序，保证在实现这个选项之前已经了解关于表和数据的大量信息。通常，HASH对于一些包含有序值的静态数据非常有效。

 技巧：HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。

 

 

 2.4  索引组织表

 索引组织表会把表的存储结构改成B树结构，以表的主键进行排序。这种特殊的表和其他类型的表一样，可以在表上执行所有的DML和DDL语句。由于表的特殊结构，ROWID并没有被关联到表的行上。

 对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。基于主键值的UPDATE和DELETE语句的性能也同样得以提高，这是因为行在物理上有序。由于键列的值在表和索引中都没有重复，存储所需要的空间也随之减少。

 如果不会频繁地根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表。

 技巧：可以在索引组织表上建立二级索引。

 

 

 2.5  反转键索引

 当载入一些有序数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。

 为了解决这个问题，Oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据1234、1235和1236就被存储成4321、5321和6321。结果就是索引会为每次新插入的行更新不同的索引块。

 技巧：如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。

 不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。

 

 

 2.6  基于函数的索引

 可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引：

 select * from emp where UPPER(job) = 'MGR';

 下面的查询使用JOB列上的索引，但是它将不会返回JOB列具有Mgr或mgr值的行：

 select * from emp where job = 'MGR';

 可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式UPPER(job)创建索引，而不是直接在JOB列上建立索引，如：

 create index EMP$UPPER_JOB on emp(UPPER(job));

 尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题：

 能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗

 是否有足够应付额外索引的存储空间？

 在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响？

 基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。

 注意：对于优化器所使用的基于函数的索引来说，必须把初始参数QUERY _REWRITE _ ENABLED设定为TRUE。

 

 

 2.7  分区索引

 分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。B树和位图索引都可以被分区，而HASH索引不可以被分区。可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性

 有两种类型的分区索引：本地分区索引和全局分区索引。每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，就必须是本地索引。把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。

 在使用分区后的表和索引时，Oracle还支持并行查询和并行DML。这样就可以同时执行多个进程，从而加快处理这条语句。

 2.7.1.本地分区索引(通常使用的索引)

 可以使用与表相同的分区键和范围界限来对本地索引分区。每个本地索引的分区只包含了它所关联的表分区的键和ROWID。本地索引可以是B树或位图索引。如果是B树索引，它可以是唯一或不唯一的索引。

 这种类型的索引支持分区独立性，这就意味着对于单独的分区，可以进行增加、截取、删除、分割、脱机等处理，而不用同时删除或重建索引。Oracle自动维护这些本地索引。本地索引分区还可以被单独重建，而其他分区不会受到影响。

 2.7.1.1 有前缀的索引

 有前缀的索引包含了来自分区键的键，并把它们作为索引的前导。例如，让我们再次回顾participant表。在创建该表后，使用survey_id和survey_date这两个列进行范围分区，然后在survey_id列上建立一个有前缀的本地索引，如下图所示。这个索引的所有分区都被等价划分，就是说索引的分区都使用表的相同范围界限来创建。

 技巧：本地的有前缀索引可以让Oracle快速剔除一些不必要的分区。也就是说没有包含WHERE条件子句中任何值的分区将不会被访问，这样也提高了语句的性能。

 2.7.1.2 无前缀的索引

 无前缀的索引并没有把分区键的前导列作为索引的前导列。若使用有同样分区键(survey_id和survey_date)的相同分区表，建立在survey_date列上的索引就是一个本地的无前缀索引，如下图所示。可以在表的任一列上创建本地无前缀索引，但索引的每个分区只包含表的相应分区的键值。

 如果要把无前缀的索引设为唯一索引，这个索引就必须包含分区键的子集。在这个例子中，我们必须把包含survey和(或)survey_id的列进行组合(只要survey_id不是索引的第一列，它就是一个有前缀的索引)。

 技巧：对于一个唯一的无前缀索引，它必须包含分区键的子集。

 

 

 2.7.2. 全局分区索引

 全局分区索引在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。在创建全局分区索引时，必须定义分区键的范围和值。全局索引只能是B树索引。Oracle在默认情况下不会维护全局分区索引。如果一个分区被截取、增加、分割、删除等，就必须重建全局分区索引，除非在修改表时指定ALTER TABLE命令的UPDATE GLOBAL INDEXES子句。

 2.7.2.1 有前缀的索引

 通常，全局有前缀索引在底层表中没有经过对等分区。没有什么因素能限制索引的对等分区，但Oracle在生成查询计划或执行分区维护操作时，并不会充分利用对等分区。如果索引被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区，如下图所示。在该图的3个索引分区中，每个分区都包含指向多个表分区中行的索引条目。

 分区的、全局有前缀索引

 技巧：如果一个全局索引将被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区。

 2.7.2.2 无前缀的索引

 Oracle不支持无前缀的全局索引。

 

 

 2.8  位图连接索引

 位图连接索引是基于两个表的连接的位图索引，在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时，标准方法是连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时，就不需要执行连接，因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的ROWID进一步改进性能，并且减少访问数据所需的I/O数量。

 创建位图连接索引时，指定涉及的两个表。相应的语法应该遵循如下模式：

 create bitmap index FACT_DIM_COL_IDX on FACT(DIM.Descr_Col) from FACT, DIM

 where FACT.JoinCol = DIM.JoinCol;

 位图连接的语法比较特别，其中包含FROM子句和WHERE子句，并且引用两个单独的表。索引列通常是维度表中的描述列——就是说，如果维度是CUSTOMER，并且它的主键是CUSTOMER_ID，则通常索引Customer_Name这样的列。如果事实表名为SALES，可以使用如下的命令创建索引：

 create bitmap index SALES_CUST_NAME_IDX

 on  SALES(CUSTOMER.Customer_Name)  from SALES, CUSTOMER

 where  SALES.Customer_ID=CUSTOMER.Customer_ID;

 如果用户接下来使用指定Customer_Name列值的WHERE子句查询SALES和CUSTOMER表，优化器就可以使用位图连接索引快速返回匹配连接条件和Customer_Name条件的行。

 位图连接索引的使用一般会受到限制：

 1）只可以索引维度表中的列。

 2）用于连接的列必须是维度表中的主键或唯一约束；如果是复合主键，则必须使用连接中的每一列。

 3）不可以对索引组织表创建位图连接索引，并且适用于常规位图索引的限制也适用于位图连接索引

B树索引
 
Oracle默认的索引类型是B树索引。对于高基数的列值，该索引类型是非常有效的。在不指定任何选项的情况下，create index 语句创建B树索引。
 
create index index_name on table_name (col1, col2, ...);
索引组织表
 
索引组织表（IOT）在一个B树索引结构中存出表行的全部内容。使用索引组织表，能缩短具有精确匹配和主键范围搜索的查询时间。
 
create table table_name(col1, col2, ...) organization index;
唯一索引
 
当插入数据到相应的表时，唯一索引将保证插入到表中的非空值都是不同的。
 
create unique index index_name on table_name(col1, col2, ...);
反向键索引
 
反向键索引对于平衡有大量顺序插入的索引的I/O是非常有用的。当插入大量顺序值时，如果使用反向键索引，就可以避免I/O几种在索引内的某个物理磁盘位置。
 
create index index_name on table_name(col1, col2, ...) reverse;
 
不能对位图索引或索引组织表指定 reverse 。另外，反向键索引不能是降序类型
减压缩索引
 
键压缩有助于减少前导列经常重复的组合索引的存出和I/O要求
 
create index index_name on table_name(col1, col2, ...) compress 2;
降序索引
 
默认情况下，Oracle用升序方式存储B树索引。如果在一个列值为数值型数据的列上创建索引，最小的数值将首先出现在索引最左边的叶子节点上，而最大的数值将被存储在最右边的叶子节点上。
 
create index  index_name on table_name (col1 desc);

 
oracle域索引简介(Oracle域索引简介)
 
oracle域索引简介(Oracle域索引简介)
 
Oracle全文检索
 
王力2009-08-06
 
Oracle从8i之前，就有了全文检索功能。到9i之后，基本可以放心使用。
 
建立的Oracle Text索引被称为域索引(域索引)，包括4种索引类型：语境，ctxcat，ctxrule或ctxxpath。
 
在4种索引中，最常用的就是语境索引，使用最通用的包含操作符进行查询。
 
下面通过简单案例介绍一下使用的方法。
 
1。准备工作
 
康恩/数据库
 
创建表空间的数据文件的do_ind /债券/应用程序/ Oracle / oradata /测试/ do_ind1 DBF大小；
 
用户应该使用帐户解锁确定应该使用；
 
连接应该使用/应该使用
 
授予执行ctx_ddl史葛；
 
康恩史葛/老虎
 
——普通表
 
创建表T1
 
(name varchar2(10)，
 
姓名日期，
 
信息VARCHAR2(500))
 
——分区表
 
创建表T2
 
(name varchar2(10)，
 
姓名日期，
 
信息VARCHAR2(500))
 
按范围分区(姓名)
 
(分区p2007值小于(to_date('，'yyyymmdd '))，
 
分区2008的值小于(to_date('，'yyyymmdd '))，
 
分区p2009值小于(to_date('，'yyyymmdd ')))；
 
2。定义域索引参数
 
开始
 
--ctx_ddl.drop_preference('my_lexer”)；
 
--ctx_ddl.drop_preference('mystore”)；
 
——语法分析
 
ctx_ddl.create_preference('my_lexer '，' chinese_lexer”)；
 
——存储参数
 
ctx_ddl.create_preference('mystore '，' basic_storage”)；
 
ctx_ddl.set_attribute('mystore '，' i_table_clause '，'表do_ind”)；
 
ctx_ddl.set_attribute('mystore '，' i_index_clause '，'表do_ind压缩2)；
 
ctx_ddl.set_attribute('mystore '，' k_table_clause '，'表do_ind”)；
 
ctx_ddl.set_attribute('mystore '，' r_table_clause '，'表do_ind”)；
 
ctx_ddl.set_attribute('mystore '，' n_table_clause '，'表do_ind”)；
 
结束；
 
/
 
3。建立索引
 
语法如下：
 
创建索引[架构。]在[模式指标。]表(列)的索引类型是
 
ctxsys.context [在线]
 
本地分区[分区] [([参数('paramstring”)]
 
【[分区] [参数，分区('paramstring ')] ])]
 
([参数] [ paramstring)n ] [使用]平行；
 
创建索引ctx_ind_t1 T1(信息)的索引类型是CTXSYS.CONTEXT
 
参数('lexer my_lexer存储MyStore”)；
 
创建索引ctx_ind_t2 T2(信息)的索引类型是CTXSYS.CONTEXT
 
地方的
 
参数('lexer my_lexer存储MyStore”)；
 
4。简单查询
 
SELECT * FROM T1在包含(信息，“吃了吗”)> 0
 
5。索引的同步和优化
 
背景类型的域索引并不随着DML操作同步索引，需要定期同步索引。同步的方法有三种，使用ctxctl命令、系统包以及SQL语句。使用ctxctl命令已经不再推荐，就介绍包和SQL语句的同步方法。
 
？同步索引
 
对于分区索引，无法整个进行同步
 
1。使用ctx_ddl.sync_index
 
程序sync_index
 
参数名称类型在/输出默认值？
 
------------------------------ ----------------------- ------ --------
 
idx_name VARCHAR2违约
 
在默认的内存空
 
part_name VARCHAR2违约
 
在默认parallel_degree数
 
执行ctx_ddl.sync_index
 
(idx_name = >的ctx_ind_t2 '，part_name = >“2008，parallel_degree = > 2)；
 
或者
 
改变指数ctx_ind_t2重建分区参数(2008 'sync”)；
 
？优化索引
 
同样，分区索引也不能整个优化
 
使用ctx_ddl.optimize_index
 
程序optimize_ind

逻辑上：
 
Single column 单行索引
Concatenated 多行索引
Unique 唯一索引
NonUnique 非唯一索引
Function-based函数索引
Domain 域索引
 
物理上：
 
Partitioned 分区索引
NonPartitioned 非分区索引
B-tree：
Normal 正常型B树
Rever Key 反转型B树 
Bitmap 位图索引
 
索引结构：
 
B-tree：
 
适合与大量的增、删、改（OLTP）； 
 不能用包含OR操作符的查询； 
 适合高基数的列（唯一值多） 
 典型的树状结构； 
 每个结点都是数据块； 
 大多都是物理上一层、两层或三层不定，逻辑上三层； 
 叶子块数据是排序的，从左向右递增； 
 在分支块和根块中放的是索引的范围；
 
Bitmap:
 
适合与决策支持系统； 
 做UPDATE代价非常高； 
 非常适合OR操作符的查询； 
 基数比较少的时候才能建位图索引；
 
树型结构：
 
索引头 
 开始ROWID，结束ROWID（先列出索引的最大范围）
 
BITMAP
 
每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT对应的ROWID有值
 
 
1. b-tree索引
 
Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。CREATE INDEX语句时，默认就是在创建b-tree索引。没有特别规定可用于任何情况。
 
2. 位图索引(bitmap index)
 
位图索引特定于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。
 
3. 基于函数的索引
 
比如经常对某个字段做查询的时候是带函数操作的，那么此时建一个函数索引就有价值了。
 
4. 分区索引和全局索引
 
这2个是用于分区表的时候。前者是分区内索引，后者是全表索引
 
5. 反向索引（REVERSE）
 
这个索引不常见，但是特定情况特别有效，比如一个varchar(5)位字段(员工编号)含值（10001,10002,10033,10005,10016..） 
 这种情况默认索引分布过于密集，不能利用好服务器的并行 
 但是反向之后10001,20001,33001,50001,61001就有了一个很好的分布，能高效的利用好并行运算。
 
6.HASH索引
 
HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。使用HASH索引必须要使用HASH集群。
 
 
oracle索引总结
 
简介
 
1.说明
 
 
 
1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
 
 
2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
 
 
3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
 
 
4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响
 
 
5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
 
 
6）oracle创建主键时会自动在该列上创建索引
 
 
索引原理
 
若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍
若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方
创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引
 
索引使用（创建、修改、删除、查看）
 
1.创建索引语法
 
CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用
 
2.修改索引
 
 
 
1）重命名索引
 
 
alter index index_sno rename to bitmap_index;
 
 
2)合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）
 
 
alter index index_sno coalesce;
 
 
3)重建索引
 
 
　　方式一：删除原来的索引，重新建立索引
 
 
　　方式二：alter index index_sno rebuild;
 
 
3.删除索引
 
drop index index_sno;
 
4.查看索引
 
select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';

 -- eg:    
create index index_sno on student('name');
select * from all_indexes where table_name='student';
 
索引分类
 
1. B树索引（默认索引，保存讲过排序过的索引列和对应的rowid值）
 
1）说明：
 
　　1.oracle中最常用的索引；B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值
 
　　2.所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同
 
　　3.能够适应精确查询、模糊查询和比较查询
 
2）分类：
 
　　 UNIQUE,NON-UNIQUE(默认),REVERSE KEY（数据列中的数据是反向存储的）
 
3）创建例子
 
craete index index_sno on student('sno');
 
4）适合使用场景：
 
　　列基数（列不重复值的个数）大时适合使用B数索引
 
2. 位图索引
 
1）说明：
 
　　1.创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行 使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索 引中的映射函数完成位到行的ROWID的转换
 
2)创建例子
 
create bitmap index index_sno on student(sno);
 
3) 适合场景：
 
对于基数小的列适合简历位图索引（例如性别等）
 
3.单列索引和复合索引（基于多个列创建）
 
1) 注意：
 
　　即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列
 
4. 函数索引
 
1)说明：
 
　　1. 当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度
 
　　2. 函数索引既可以使用B数索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引
 
　　3. 函数索引中可以随你用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等
 
2）例子：
 
create index fbi  on student (upper(name));
select * from student where upper(name) ='WISH';
 
索引建立原则总结
 
　　1. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
 
　　2. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
 
　　3. 小表不要简历索引
 
　　4. 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引
 
　　5. 列中有很多空值，但经常查询该列上非空记录时应该建立索引
 
　　6. 经常进行连接查询的列应该创建索引
 
　　7. 使用create index时要将最常查询的列放在最前面
 
　　8. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引
 
　　9.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）
 
注意事项
 
1. 通配符在搜索词首出现时，oracle不能使用索引，eg：
 
--我们在name上创建索引；

create index index_name on student('name');

--下面的方式oracle不适用name索引

select * from student where name like '%wish%';

--如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：

select * from student where name like 'wish%';
 
2. 不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not会停止使用索引，而采用全表扫描）
 
select * from student where not (score=100);

select * from student where score <> 100;

--替换为

select * from student where score>100 or score <100
 
3. 索引上使用空值比较将停止使用索引， eg：
 
select * from student where score is not null;
 
 
ORACLE索引介绍和使用
 
１．什么是索引？
 
索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；
Oracle存储索引的数据结构是B*树（平衡树），位图索引也是如此，只不过是叶子节点不同B*数索引；
索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。
 
2.使用索引的目的：
 
加快查询速度
减少I/O操作
消除磁盘排序（索引能加快排序速度）
 
3.何时使用索引：
 
查询返回的记录数 排序表<40%，对非排序表<7%
表的碎片较多（频繁增加、删除）
 
4．索引的种类
 
非唯一索引（最常用）
唯一索引
位图索引
局部有前缀分区索引
局部无前缀分区索引
全局有前缀分区索引
散列分区索引
基于函数的索引
 
5．管理索引的准则
 
在表中插入数据后创建索引
在用SQL*Loader或import工具插入或装载数据后，建立索引比较有效；
 
6.索引正确的表和列
 
经常检索排序大表中40%或非排序表7%的行，建议建索引；
为了改善多表关联，索引列用于联结；
列中的值相对比较唯一；
取值范围（大：B*树索引，小：位图索引）；
Date型列一般适合基于函数的索引；
列中有许多空值，不适合建立索引
 
7.为性能而安排索引列
 
经常一起使用多个字段检索记录，组合索引比单索引更有效；
把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)，在where条件中使用groupid或groupid,serv_id，查询将使用索引，若仅用到serv_id字段，则索引无效；
 
合并/拆分不必要的索引。
 
限制每个表索引的数量
 
一个表可以有几百个索引（你会这样做吗？），但是对于频繁插入和更新表，索引越多系统CPU，I/O负担就越重；
建议每张表不超过5个索引。
 
删除不再需要的索引
 
索引无效，集中表现在该使用基于函数的索引或位图索引，而使用了B*树索引；
应用中的查询不使用索引；
重建索引之前必须先删除索引，若用alter index … rebuild重建索引，则不必删除索引。
 
索引数据块空间使用
 
创建索引时指定表空间，特别是在建立主键时，应明确指定表空间；
合理设定pctfress，注意：不能给索引指定pctused；
估计索引的大小和合理地设置存储参数，默认为表空间大小，或initial与next设置成一样大。
 
考虑并行创建索引
 
对大表可以采用并行创建索引，在并行创建索引时，存储参数被每个查询服务器进程分别使用，例如：initial为1M，并行度为8，则创建索引期间至少要消耗8M空间；
 
考虑用nologging创建索引
 
对大表创建索引可以使用nologging来减少重做日志；
节省重做日志文件的空间；
缩短创建索引的时间；
改善了并行创建大索引时的性能。
 
8.怎样建立最佳索引?
 
明确地创建索引
 
create index index_name on table_name(field_name)
　　tablespace tablespace_name
　　pctfree 5
　　initrans 2
　　maxtrans 255
　　storage
　　(
　　minextents 1
　　maxextents 16382
　　pctincrease 0
　　);
 
创建基于函数的索引:
 
常用与UPPER、LOWER、TO_CHAR(date)等函数分类上，例：
 
create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;
 
创建位图索引:
 
对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，例：
 
create bitmap index idx_bitm on class (classno) tablespace tablespace_name;
 
明确地创建唯一索引
 
可以用create unique index语句来创建唯一索引，例：
 
create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;
 
创建与约束相关的索引
 
可以用using index字句，为与unique和primary key约束相关的字段创建索引，例如：
 
alter table table_name
　　add constraint PK_primary_keyname primary key (field_name)
　　using index tablespace tablespace_name；
 
如何创建局部分区索引
 
基础表必须是分区表；
分区数量与基础表相同；
每个索引分区的子分区数量与相应的基础表分区相同；
基础表的子分区中的行的索引项，被存储在该索引的相应的子分区中,例如:
 
　　Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
　　Pctfree 5
　　Tablespace TBS_AK01_IDX
　　Storage (
　　MaxExtents 32768
　　PctIncrease 0
　　FreeLists 1
　　FreeList Groups 1
　　)
　　local
　　/
 
如何创建范围分区的全局索引
 
基础表可以是全局表和分区表。
 
create index idx_start_date on tg_cdr01(start_date)
　　global partition by range(start_date)
　　(partition p01_idx vlaues less than (‘0106’)
　　partition p01_idx vlaues less than (‘0111’)
　　…
　　partition p01_idx vlaues less than (‘0401’ ))
　　/
 
　　重建现存的索引 
 　　重建现存的索引的当前时刻不会影响查询；
 
　　重建索引可以删除额外的数据块； 
 　　提高索引查询效率；
 
alter index idx_name rebuild nologging;
 
　　 
 　　对于分区索引：
 
alter index idx_name rebuild partition partiton_name nologging;
 
要删除索引的原因
 
不再需要的索引；
索引没有针对其相关的表所发布的查询提供所期望的性能改善；
应用没有用该索引来查询数据；
该索引无效，必须在重建之前删除该索引；
该索引已经变的太碎了，必须在重建之前删除该索引；
语句： 
  
 drop index idx_name; 
 drop index idx_name drop partition partition_name; 
 
 
8.建立索引的代价
 
基础表维护时，系统要同时维护索引，不合理的索引将严重影响系统资源，主要表现在CPU和I/O上；
 
插入、更新、删除数据产生大量db file sequential read锁等待；
 
一个表中有几百万条数据，对某个字段加了索引，但是查询时性能并没有什么提高，这主要可能是oracle的索引限制造成的。
 
oracle的索引有一些索引限制，在这些索引限制发生的情况下，即使已经加了索引，oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的系统开销造成性能更差。
 
常见的索引限制问题：
 
9、使用不等于操作符(<>, !=)
 
下面这种情况，即使在列dept_id有一个索引，查询语句仍然执行一次全表扫描
 
select * from dept where staff_num <> 1000;
 
但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？
 
有！
 
通过把用 or 语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了。
 
select * from dept shere staff_num < 1000 or dept_id > 1000;
 
10、使用 is null 或 is not null
 
使用 is null 或is nuo null也会限制索引的使用，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使用这个索引（除非索引是一个位图索引，关于位图索引，会在以后的blog文章里做详细解释）。在sql语句中使用null会造成很多麻烦。
 
解决这个问题的办法就是：建表时把需要索引的列定义为非空(not null)
 
11、使用函数
 
如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询就不会使用索引：
 
select * from staff where trunc(birthdate) = '01-MAY-82';
 
但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。
 
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);
 
12、比较不匹配的数据类型
 
比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。
 
select * from dept where dept_id = 900198;
 
这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引
 
select * from dept where dept_id = '900198';
 
13、使用like子句
 
使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。
 
Like 的字符串中第一个字符如果是‘%’则用不到索引
 
Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’用不到
 
14．使用IN 
 尽管In写法要比exists简单一些，exists一般来说性能要比In要高的多 
 用In还是用Exists的时机 
 当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候，用In要好，否则就要用Exists 
 例：
 
select count(*) from person_info where xb in (select xb_id from dic_sex);

Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);

Select * from person_info where zjhm=3101….;--将会对person_info全表扫描

Select * from person_info where zjhm =‘3101…’;--才能用到索引
 
假定TEST表的dt字段是date类型的并且对dt建了索引。 
 如果要查‘20041010’一天的数据.下面的方法用不到索引
 
Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;
 
而以下将会用到索引。
 
select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1 
 
15．如果能不用到排序，则尽量避免排序。 
 用到排序的情况有 
 集合操作。Union ,minus ,intersect等,注：union all 是不排序的。
 
Order by
Group by
Distinct
In 
 
有时候也会用到排序 
 确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行，有文章说，内存排序的速度是硬盘排序的1万倍。
 
在排序的字段上创建索引，让排序在内存中执行，加快排序速度。 
 
 
16.在基于CBO的优化器（花费）下，表的统计数据过期。也可能导致不使用索引。
 
解决：执行表分析。获取表的最新信息。
 
17.获取的数据量过大，全部扫描效率更高
 
18.索引字段的值分散率太低，值太集中，如类型字段都是1,2， 状态类型Y-有效/N-无效。这类型的字段最好别建索引。
 
尽管在这些字段上建立了索引，但对全表数据区分度不大。最后还是会全表扫描。