表的主键、外键必须有索引； 
数据量超过300的表应该有索引； 
经常与其他表进行连接的表,在连接字段上应该建立索引；
经常出现在Where子句中的字段,特别是大表的字段,应该建立索引； 
索引应该建在选择性高的字段上；
索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引； 
复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： 
 
  
A、正确选择复合索引中的主列字段,一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
E、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引；
频繁进行数据操作的表,不要建立太多的索引；
删除无用的索引,避免对执行计划造成负面影响； 

 
 
 
以上是一些普遍的建立索引时的判断依据.一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据.因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销,索引对于插入、删除、更新操作也会增加处理上的开销.另外,过多的复合索引,在有单字段索引的情况下,一般都是没有存在价值的；相反,还会降低数据增加删除时的性能,特别是对频繁更新的表来说,负面影响更大. 
 

一个表哪怕只做查询操作,索引也不宜过多, 因为所以太多会导致查询选择索引出现开销(当然指定了索引可以最低限度的降低开销).
 从我自己的实际工作情况来看, 所以得建立要全局考虑,就是不要仅仅只考虑一张表的索引怎么建,而是要考虑你整个模块应用的索引怎么建,一般在一个表上索引不要超过5个!

---
 
堆组织表就不说了，其索引中记录了记录所在位置的rowid，查找的时候先找索引，然后再根据索引rowid找到块中的行数据
 
 索引组织表，其行数据以索引形式存放，因此找到索引，就等于找到了行数据。
 
--
 
堆组织表的数据是散放的，索引和表的数据是分离的
 
 索引组织表的索引和数据是在一起的
 
--
 
 
 堆组织表的存储速度因为不用考虑排序, 所以存储速度会比较快. 但是要查找符合某个条件的记录, 就必须得读取全部的记录以便筛选.
 而这个时候为了加快查询速度, 索引就出现了, 索引是针对少量特定字段的值拿出来进行排序存储, 并记录在表中的位置, 
 而因为索引是有序的, 所以就会很容易通过索引查询到具体的记录位置, 然后再根据记录位置直接从表中读取该记录.
 同时因为索引的字段较少, 所以索引通常会比其基表小得多.
 
 从上面通过索引访问表记录的方式可以看出, 当要访问的数据量较大时, 通过每一条记录的位置去访问原始记录, 
 每一条符合条件的记录都需要经过索引访问后再访问基表这样一个复杂的过程, 这会花费很多时间,
 同样, 如果不经过索引而直接查询表, 也可能因为表字段太多, 记录较大的情况下把全部的数据读取进来, 这也会花费很多时间.
 
 那怎么办呢?
 这个时候就会想到, 如果表中数据本身就是有序的, 这样查询表的时候就可以快速的找到符合条件的记录位置, 
 而很容易判断符合条件记录的位置, 这样只需要读取一小部分数据出来就可以了, 不需要全表记录都读取出来进行判断.
 索引表就这样产生了.当然索引表中插入,更新资料的时候可能会因为需要排序而将数据重组, 这时候数据插入或更新速度会比堆组织表慢一些.
 如果堆组织表上有索引, 那么对堆组织表的插入也会因为要修改索引而变慢
 
 我们可以看到堆组织表+索引的方式 与 索引表 都能够实现数据的快速查找, 那为什么不全部采用索引表呢, 这样不是很简单吗?
 我能想到的是前者我们可以针对不同的查找条件建立多个索引, 而后者却不行, 后者只能对某一组查询条件有效.
 --
 
堆组织表（heap organized table）
 
 
 Oracle中有很多类型的表，像堆组织表、索引组织表、索引聚簇表等等。首先，我将从最基本、最常用的堆组织表（heap organized table）介绍。
 
 通常我们默认建的表就是堆组织表。语法（详细语法请参见Oracle官方文档）如下：
 
  
 
 
 
 
  

   
 
  
 
 
 
 
 Create table test(  
   Id int,  
   Name varchar2(10)  
 );  
 
 
  
 
 数据会以堆的方式管理，增加数据时，会使用段中找到的第一个能放下此数据的自由空间。当从表中删除数据时，则允许以后的INSERT和UPDATE重用这部分空间。它是以一种有些随即的方式使用。
 
 很多初学者会想当然的以为数据的插入会按顺序进行，第一条肯定排在第一位，接着是第二条，一直到最后。可当SELECT查询的时候，返回的结果往往让人失望。排列的顺序让人大跌眼镜，下面来看一个例子。
 
  
 
 
 
 
  

   
 
  
 
 
 
 
 create table t  
 (   
   a int,  
   b varchar2(4000) default rpad('*', 4000, '*'),  
   c varchar2(3000) default rpad('*', 3000,'*')  
 );  
   
 insert into t(a) values (1);  
 insert into t(a) values (2);  
 insert into t(a) values (3);  
 delete from t where a=2;  
 insert into t(a) values (4);  
   
 SQL> select a from t;  
   
          A  
 ----------  
          1  
          4  
          3  
 
 
  
 
  
 
 全 表扫描时，会按命中的顺序来获取数据，而不是按插入的顺序。这是一个必要要了解的重要的数据库概念。一般来说，数据库表本质上是无序的数据组合。还有一种 情况，如果我插入一个小行，其后是一个非常大的行，而且这个大行与小行是无法放在同一个块上的，然后又插入一个小行，那么观察到的结果很可能是“小行，小 行，大行”。
 
 ----
 
 
 
 
 索引组织表 
 An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well 
 
 
 
索引组织表有一个可变的主B树存储组织。不象普通表(堆组织),数据是无序存储的集合。   

 
  索引组织表是以主键排序的方式的B树组织结构。   
 *** 
 

 heap table 就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定。
 
 而iot 就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通的表要慢一些。
 
 适用于信息检索、空间和OLAP程序。索引组织表的适用情况：
 
 1、 代码查找表。
 
 2、 经常通过主码访问的表。
 
 3、 构建自己的索引结构。
 
 4、 加强数据的共同定位，要数据按特定顺序物理存储。
 
 5、 经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。
 
 经常更新的表当然不适合iot，因为oracle需要不断维护索引，而且由于字段多索引成本就大。
 

  
 

 ****
 

 八楼的说的很对，因为索引组织的表数据所有字段都是根据索引组织的，因此每次插入都需要排序然后重新组织表结构，所以插入很慢，因此不适宜使用频繁改动的表（除非你希望每次插入都表根据索引键排序），但是由于数据全再索引上，因此索引已经将表排序好，所以访问的时候比较快（比访问普通索引快的原因是普通索引找到值后还要指向数据所在的块），并且，由于索引组织的表相同的索引键列中的内容放在一起，对于读取访问的块数也要少不少。
 
 当然索引组织表对全表扫描不排序没什么好处。对不按照索引键访问的表也没什么好处。
 

 *******************************************************
 
 
 
索引
 
 
 
索引是与表关联的可选结构.可以明确地创建索引,以加快对表执行SQL语句的速度.合理使用索引是减少磁盘I/O的主要方法.索引包括以下几种类型:
 1.标准索引.
 语法:
 create index 索引名 on 表名(字段名)
 实例如下:
 create index ind_ename on emp(ename)
 2.唯一索引.主键上会自动创建唯一索引.唯一索引确保在定义索引的列中,表的任意两行的值都不相同.
 语法:
 create unique index 索引名 on 表名(字段名)
 实例:
 create unique index ind_name on emp(ename)
 3.组合索引,组合索引是在表中的多个列上创建的索引.
 基于:select .... from table where ...and ... 此种多条件语句考虑建立组合索引
 实例:
 create index ind_name on emp(ename,sal)
 4.反向键索引.此索引适合由序列组成的字段或者某数据分布密集的字段.
 如:
 原字段 反向键字段
 1001   1001
 1002   2001
 1003   3001
 1004   4001
 是不是观察到哪边检索得快些?对,就是左边的反向键字段.
 实例:
 create index ind_empno on emp(empno) reverse
 5.位图索引.使用位图索引的优点在于,它最适用于低基数列,也就是不同值的数目比表的行数少的列.如果某个列的值重复超过一百次,则可以考虑在该列上创建位图索引.
 实例:
 create bitmap index in_deptno on emp(deptno)
 6.基于函数的索引.
 为了方便操作,oracle提供了一个选项,可以基于一个或多个列上的函数或表达式创建索引.
 如下所示:
 create index ind_sal on emp(lower(ename)) 必须注意的是,要创建基于函数或表达式的索引,必须具有query rewaite 系统权限.
 7.索引组织表.
 索引组织表与普通表不同之处在于,该表的数据存储在与其关联的索引中.对表数据进行的修改,如添加新行,更新新行或删除行,只会导致索引的更新.索引组织表最大的优点,提高读取速度,降低I/O读写.建立索引组织表必须有一列为主键.
 实例如下:
 create table ind_table
 (
 id number primary key,
 name varchar2(20)
 )
 organization index;
 OK,下面再来讲讲索引中的分区.
 与对表进行分区类似,Oracle也允许对索引分区.与表分区一样,索引分区可以存储在不同的表空间中.索引分区有如下三种类型:
 (1)局部分区
 局部分区索引是在分区表上创建的一种索引,在局部分区索引中,Oracle为表的每个分区建立一个独立的索引.
 实例如下:
 先创建一个分区表:
 create table order_mast
 (
 order_id number(4),
 order_name varchar2(20)
 )
 partition by range(order_id)
 (
 partition p1 values less than(1000),
 partition p2 values less than(2000),
 partition p3 values less than(maxvalue)
 );
 然后紧接着创建局部分区索引:
 create index myindex on order_mast(order_id) local
 (2)全局分区
 全局分区是指在分区表或非分区表上创建的索引.全局索引的键可以引用存储在多个分区中的行.合璧索引是自己分区.
 实例如下:
 create index glb_ind on order_mast(order_id) global
 partition by range(order_id)
 (
 partition p1 values less than(1500),
 partition p2 values less than(maxvalue)
 );
 (3)全局非分区,索引中不分区,与普通索引相同.在此不在举例说明.
 
 
 
 
-------
 
1. 堆组织表：
    通常我们默认建的表就是堆组织表。
    语法：Create table test(  
     Id int,  
     Name varchar2(10)  
   );
    此类型的表中，数据会以堆的方式进行管理，增加数据时候，会使用段中找到的第一个能放下
    此数据的自由空间。当从表中删除数据时候，则允许以后的UPDATE和INSERT重用这部分空间，
    它是以一种有些随机的方式使用。
    
    很多初学者会想当然的以为数据的插入会按顺序进行，第一条肯定排在第一位，接着是第二条，一直到最后。
    可当SELECT查询的时候，返回的结果往往让人失望。排列的顺序让人大跌眼镜，下面来看一个例子。
    
    create table t(   
    a int,  
    b varchar2(4000) default rpad('*', 4000, '*'),  
    c varchar2(3000) default rpad('*', 3000,'*')  
  );  
    
  insert into t(a) values (1);  
  insert into t(a) values (2);  
  insert into t(a) values (3);  
  delete from t where a=2;  
  insert into t(a) values (4);  
    
  SQL> select a from t;  
    
           A  
  ----------  
           1  
           4  
           3  
 
  全表扫描时，会按命中的顺序来获取数据，而不是按插入的顺序。
   这是一个必要要了解的重要的数据库概念。一般来说，数据库表本质上是无序的数据组合。 
   
 2. 索引组织表(index organized table, IOT)：就是存储在一个索引结构中的表，数据按主键进行存储和排序。
    适用的场景：
     a.完全由主键组成的表。这样的表如果采用堆组织表，则表本身完全是多余的开销，
       因为所有的数据全部同样也保存在索引里，此时，堆表是没用的。
     b.代码查找表。如果你只会通过一个主键来访问一个表，这个表就非常适合实现为IOT.
  c.如果你想保证数据存储在某个位置上，或者希望数据以某种特定的顺序物理存储，IOT就是一种合适的结构。 IOT提供如下的好处：
   ·提高缓冲区缓存效率，因为给定查询在缓存中需要的块更少。
   ·减少缓冲区缓存访问，这会改善可扩缩性。
   ·获取数据的工作总量更少，因为获取数据更快。
   ·每个查询完成的物理I/O更少。
    如果经常在一个主键或唯一键上使用between查询，也是如此。如果数据有序地物理存储，就能提升这些查询的性能。
     语法：create table indexTable(
   ID varchar2 (10), 
   NAME varchar2 (20), 
   constraint pk_id primary key (ID)
   ) organization index;
 3. 索引聚簇表：
    聚簇是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中；聚簇还表示把相关的数据存储在同一个块上。
    利用聚簇，一个块可能包含多个表 的数据。概念上就是如果两个或多个表经常做链接操作，那么可以把需要的数据预先存储在一起。
    聚簇还可以用于单个表，可以按某个列将数据分组存储。  
    语法：
    索引聚簇表是基于一个索引聚簇（index cluster）创建的。
    里面记录的是各个聚簇键。聚簇键和我们用得做多的索引键不一样，索引键指向的是一行数据，
    聚簇键指向的是一个ORACLE BLOCK。我们可以先通过以下命令创建一个索引簇。
    create cluster emp_dept_cluster
 　　( deptno number(2) )
 　　size 1024
 　　/
 
   size参数：
    向聚簇中放数据之前，需要先对聚簇建立索引。可以现在就在聚簇中创建表，
    但是由于我们想同时创建和填充表，而有数据之前必须有一个聚簇索引，所以我们先来 建立聚簇索引。
    create index emp_dept_cluster_idx
 　　on cluster emp_dept_cluster
 　　/
     
     建表：
     create table dept
 　　( deptno number(2) primary key, 
       dname varchar2(14),
 　　  loc varchar2(13)
 　　)
 　　cluster emp_dept_cluster(deptno)
 　　/
 
    create table emp
 　　(  empno    number primary key, 
  ename    varchar2(10), 
  job      varchar2(9), 
  mgr      number, 
  hiredate date, 
  sal      number, 
  comm     number,
 　　 deptno number(2) constraint emp_fk references dept(deptno)
 　　) cluster emp_dept_cluster(deptno)
     /
 
   什么时候不应该使用聚簇？
    1） 如果预料到聚簇中的表会大量修改：
     必须知道，索引聚簇会对DML的性能产生某种负面影响（特别是INSERT语句）。管理聚簇中的数据需要做更多的工作。
 　 2） 如果需要对聚簇中的表执行全表扫描：不只是必须对你的表中的数据执行全面扫描，
    还必须对（可能的）多个表中的数据进行全面扫描。由于需要扫描更多的数据， 所以全表扫描耗时更久。
 　　3） 如果你认为需要频繁地TRUNCATE和加载表：聚簇中的表不能截除。
  这是显然的，因为聚簇在一个块上存储了多个表，必须删除聚簇表中的行。
 
　　因此，如果数据主要用于读（这并不表示“从来不写”；聚簇表完全可以修改），
  而且要通过索引来读（可以是聚簇键索引，也可以是聚簇表上的其他索引），
  另外 会频繁地把这些信息联结在一起，此时聚簇就很适合。
 ----------- 
 
 
 
表类型的定义
 heap table 就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定
 
 
 
将索引和表数据一起存储在一个称为“索引组织的表”（Index-Organized Table, IOT）的表中。使用IOT可以显著地减少磁盘空间的使用，因为不需要存储索引的列两次（一次存在表中，一次存在索引中）。相反，只需将它们和其他任何非索引的列存储在IOT中一次。IOT适用于基本的访问方法是通过主键进行访问的那些表，但允许在IOT的其他列上创建索引以改善通过这些列的访问性能。 由于IOT中的整个行存储为索引本身，因此没有用于每个行的ROWID。主键用来标识一个IOT中的行。与此不同，Oracle根据主键的值来创建逻辑ROWID，逻辑ROWID用于支持IOT上的二级索引。此外，还可以对IOT进行分区。对于频繁的插入操作的表。iot性能不好，在插入数据时，要寻找插入到那个块，若块的大小不足，需要放在溢出块中，这些操作都需要消耗资源。索引组织表适合于应用中更改不频繁，且访问时大多数时候按某一主键访问的字典。
 
 
而iot 就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通的表要慢一些。
 适用于信息检索、空间和OLAP程序。
 因为索引组织的表数据所有字段都是根据索引组织的，因此每次插入都需要排序然后重新组织表结构，所以插入很慢，因此不适宜使用频繁改动的表（除非你希望每次插入都表根据索引键排序），但是由于数据全再索引上，因此索引已经将表排序好，所以访问的时候比较快（比访问普通索引快的原因是普通索引找到值后还要指向数据所在的块），并且，由于索引组织的表相同的索引键列中的内容放在一起，对于读取访问的块数也要少不少。
 当然索引组织表对全表扫描不排序没什么好处。对不按照索引键访问的表也没什么好处。
 索引组织表的适用情况：
 1、代码查找表。
 2、经常通过主码访问的表。
 3、构建自己的索引结构。
 4、加强数据的共同定位，要数据按特定顺序物理存储。
 5、经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。
 经常更新的表当然不适合iot，因为oracle需要不断维护索引，而且由于字段多索引成本就大。
 
 An   index-organized   table   has   a   storage   organization  that   is   a   variant   of   a   primary   B-tree.   Unlike   an  ordinary   (heap-organized)   table   whose   data   is   stored   as  an   unordered   collection   (heap),   data   for   an  index-organized   table   is   stored   in   a   B-tree   index  structure   in   a   primary   key   sorted   manner.   Besides  storing   the   primary   key   column   values   of   an  index-organized   table   row,   each   index   entry   in   the  B-tree   stores   the   nonkey   column   values   as   well   
     
     
   索引组织表有一个可变的主B树存储组织。不象普通表(堆组织),数据是无序存储的集合。   
   索引组织表是以主键排序的方式的B树组织结构。   
     
 
 
 
 

 
 
 
 

 
 
 
IOT有什么意义呢？使用堆组织表时，我们必须为表和表主键上的索引分别留出空间。而IOT不存在主键的空间开销，因为索引就是数据，数据就是索引，二者已经合二为一。但是，IOT带来的好处并不止于节约了磁盘空间的占用，更重要的是大幅度降低了I/O,减少了访问缓冲区缓存(尽管从缓冲区缓存获取数据比从硬盘读要快得多，但缓冲区缓存并不免费，而且也绝对不是廉价的。每个缓冲区缓存获取都需要缓冲区缓存的多个闩，而闩是串行化设备，会限制应用的扩展能力)
 
 
 

 
 
 
索引组织表属性 
 
 
   1、OVERFLOW子句(行溢出)
 
 
 
 
 
    因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。溢出段的设置有两种格式：
 
 
 
 
 
     PCTTHRESHOLD n：制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段
 
 
     INCLUDING column_name：指定列之前的列都放入索引块，之后的列都放到溢出段
 
 
 
 
 
      ● 当行中某字段的数据量无法确定时使用PCTTHRESHOLD。
 
 
      ● 若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。
 
 
     
 
 
   Create table t88(
 
 
     ID varchar2(10),
 
 
     NAME varchar2(20),
 
 
     Constraint pk_id primary key(ID)
 
 
     )
 
 
   Organization index
 
 
     PCTTHRESHOLD 20
 
 
     Overflow tablespace users
 
 
     INCLUDING name;
 
 
 
 
 
   ● 如上例所示，name及之后的列必然被放入溢出列，而其他列根据PCTTHRESHOLD规则。
 
 
 
 
 
   2、COMPRESS子句(键压缩) 
 
 
 
 
 
    与普通的索引一样，索引组织表也可以使用COMPRESS子句进行键压缩以消除重复值。
 
 
    具体的操作是，在organization index之后加上COMPRESS n子句
 
 
  用于压缩索引列，在块级提取公因子，避免重复值。
 
 
 
 
如：create table iot(
 
 
        owner, object_type, object_name,
 
 
        constraint iot_pk primary key(owner, object_type,object_name)
 
 
        Orgnazation index
 
 
        NOCOMPRESS
 
 
)；
 
 
表示对于每个主键组合都会物理地存储。倘若使用COMPRESS N 则对于重复的列不再物理存储，
 
 
● n的意义在于：指定压缩的列数。默认为无穷大。 
 
 
    例如对于数据(1,2,3)、(1,2,4)、(1,2,5)、(1,3,4)、(1,3,5)时
 
 
    若使用COMPRESS则会将重复出现的(1,2)、(1,3)进行压缩
 
 
    若使用COMPRESS 1时，只对数据(1)进行压缩
 
 
 如NOCOMPRESS:
 
 
 owner , object_type,  object_name
 
 
 Scott    table          emp
 
 
 Scott    table          dept
 
 
 COMPRESS  1
 
 
 owner , object_type,  object_name
 
 
 Scott    table          emp
 
 
          table          dept
 
 
 COMPRESS 2
 
 
 owner , object_type,  object_name
 
 
 Scott    table          emp
 
 
          Dept
 
 
 
 
 
 
 
 
索引组织表的维护
 
 
 
 
 
    索引组织表可以和普通堆表一样进行INSERT、UPDATE、DELETE、SELECT操作。
 
 
    可使用ALTER TABLE ... OVERFLOW语句来更改溢出段的属性。
 
 
 
 
 
   Alter table t88 add overflow;--新增一个overflow
 
 
 
 
 
   ● 要ALTER任何OVERVIEW的属性，都必须先定义overflow，若建表时没有可以新增
 
 
 
 
 
   Alter table t88 pctthreshold 15 including name;--调整overflow的参数
 
 
   Alter table t88 initrans 2 overflow initrans 4;--修改数据块和溢出段的    initrans特性
 
 
 
 下面分别就索引组织表和普通表的一些性能对比做一些试验，创建两张表，一个为普通的表，另外一个为索引组织表：
 C:\>sqlplus
 
 SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 5月 19 11:09:06 2005
 
 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
 请输入用户名:  wwf/wwf
 
 连接到:
 Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
 With the Partitioning, OLAP and Oracle Data Mining options
 JServer Release 9.2.0.1.0 - Production
 
 create table heap_stocks
 ( symbol    varchar2(4),
   ticker_dt date,
   price     number,
   constraint heap_stocks_pk primary key (symbol,ticker_dt)
 );
 
 create table iot_stocks
 ( symbol    varchar2(4),
   ticker_dt date,
   price     number,
   constraint iot_stocks_pk primary key (symbol,ticker_dt)
 )
 organization index compress 1;
 
 上面模仿股票，分别存放股票代码，日期，收盘价格三个字段。下面，我们插入分别对这两个表插入1000种股票200天的数据，看看其插入数据时的性能：
 
 1 插入数据
 SQL> set timing on
 SQL> begin
   2    for i in 1..200 loop
   3       insert into heap_stocks
   4       select to_char(rownum, 'fm0009'), trunc(sysdate)+i, rownum
   5       from all_objects where rownum <= 1000;
   6    end loop;
   7    commit;
   8  end;
   9  /
 
 PL/SQL 过程已成功完成。
 
 已用时间:  00: 00: 18.06
 SQL> set timing on
 SQL> begin
   2    for i in 1..200 loop
   3       insert into iot_stocks
   4       select to_char(rownum, 'fm0009'), trunc(sysdate)+i, rownum
   5       from all_objects where rownum <= 1000;
   6    end loop;
   7    commit;
   8  end
   9  ;
 10  /
 
 PL/SQL 过程已成功完成。
 
 已用时间:  00: 00: 31.07
 
 可以看到，插入20万条数据，普通表用了18秒，而IOT表用了31秒，相差明显。这说明插入数据时，IOT表的速度是相当慢的。
 
 2. 查询
 
 我们重新启动一下数据库：
 SQL> conn
 请输入用户名:  sys / nolog as sysdba
 已连接。
 SQL> shutdown immediate
 数据库已经关闭。
 已经卸载数据库。
 ORACLE 例程已经关闭。
 SQL> startup
 ORACLE 例程已经启动。
 
 Total System Global Area  135338868 bytes
 Fixed Size                   453492 bytes
 Variable Size             109051904 bytes
 Database Buffers           25165824 bytes
 Redo Buffers                 667648 bytes
 数据库装载完毕。
 数据库已经打开。
 SQL> exit
 从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
 With the Partitioning, OLAP and Oracle Data Mining options
 JServer Release 9.2.0.1.0 - Production中断开
 
 然后重新登录：
 SQL> conn
 请输入用户名:  wwf/wwf
 已连接。
 a.  使用autotrace测试
 SQL> set autotrace traceonly
 SQL> set timing on
 SQL> set autotrace traceonly
 SQL> select * from heap_stocks where symbol = '0001';
 
 已选择200行。
 
 已用时间:  00: 00: 00.08
 
 Execution Plan
 ----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'HEAP_STOCKS'
    2    1     INDEX (RANGE SCAN) OF 'HEAP_STOCKS_PK' (UNIQUE)
 
 Statistics
 ----------------------------------------------------------
         239  recursive calls
           0  db block gets
         259  consistent gets
         207  physical reads
           0  redo size
        5706  bytes sent via SQL*Net to client
         646  bytes received via SQL*Net from client
          15  SQL*Net roundtrips to/from client
           4  sorts (memory)
           0  sorts (disk)
         200  rows processed
 
 SQL> select * from iot_stocks where symbol = '0001';
 
 已选择200行。
 
 已用时间:  00: 00: 00.02
 
 Execution Plan
 ----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=2132)
    1    0   INDEX (RANGE SCAN) OF 'IOT_STOCK_PK' (UNIQUE) (Cost=2 Card=82 Bytes=2132)
 
 Statistics
 ----------------------------------------------------------
         299  recursive calls
           0  db block gets
          63  consistent gets
           4  physical reads
           0  redo size
        5706  bytes sent via SQL*Net to client
         646  bytes received via SQL*Net from client
          15  SQL*Net roundtrips to/from client
           6  sorts (memory)
           0  sorts (disk)
         200  rows processed
 
 逻辑读分别为259和63，差别显著！说明，查询时，IOT表性能要远远优越于普通的表！
 
 
 b 使用sql_trace测试:
 SQL> conn
 请输入用户名:  wwf/wwf
 已连接。
 SQL> alter session set sql_trace = true;
 
 会话已更改。
 
 SQL> select avg(price) from heap_stocks where symbol = '0001';
 
 AVG(PRICE)
 ----------
          1
 
 SQL> select avg(price) from iot_stocks where symbol = '0001';
 
 AVG(PRICE)
 ----------
          1
 
 SQL> alter session set sql_trace = false;
 
 会话已更改。
 
 使用tkprof格式化输出文件，得到如下结果：
 
 select avg(price) from heap_stocks where symbol = '0001'
 
 
 call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        1      0.31       0.33          1          1          0           0
 Execute      1      0.00       0.00          0          0          0           0
 Fetch        2      0.00       0.39        203        208          0           1
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total        4      0.31       0.73        204        209          0           1
 
 Misses in library cache during parse: 1
 Optimizer goal: CHOOSE
 Parsing user id: 61  
 
 Rows     Row Source Operation
 -------  ---------------------------------------------------
       1  SORT AGGREGATE 
     200   TABLE ACCESS BY INDEX ROWID HEAP_STOCKS 
     200    INDEX RANGE SCAN HEAP_STOCKS_PK (object id 30391)
 
 
 
 select avg(price) from iot_stocks where symbol = '0001'
 
 
 call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        1      0.02       0.03          0          0          0           0
 Execute      1      0.00       0.01          0          0          0           0
 Fetch        2      0.00       0.07          3          4          0           1
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total        4      0.02       0.11          3          4          0           1
 
 Misses in library cache during parse: 1
 Optimizer goal: CHOOSE
 Parsing user id: 61  
 
 Rows     Row Source Operation
 -------  ---------------------------------------------------
       1  SORT AGGREGATE 
     200   INDEX RANGE SCAN IOT_STOCK_PK (object id 30393)
 
 看看Tom怎么说：
 So, we did 203 physical IO's to process the HEAP table.  What that tells me is 
 that our data for stock symbol 0001 is spread out on 200 blocks (200 days, 200 
 blocks).  In order to cache the results for this query, we need 200 block 
 buffers.  We needed to do that IO to get the answer initially.
 
 Now, looking at the IOT we did 3 physical IO's -- we cached 3 blocks -- and got 
 the same answer!  Not only that but by using index key compression I was able to 
 remove he redudant 0001's from the data -- we can cache this much more 
 efficiently and getting it the first time takes seriously less IO.  Very nice.
 
 
 3 删除数据
 SQL> set autotrace off
 SQL> delete from heap_stocks;
 
 已删除200000行。
 
 已用时间:  00: 00: 26.02
 SQL> delete from iot_stocks;
 
 已删除200000行。
 
 已用时间:  00: 00: 08.08
 
 可以看到，删除数据时，普通表用了26秒，而IOT表用了8秒。差别显著！也许是普通表占的空间大的缘故吧!
 
 
 
 

 
 
 
索引聚簇表clusterindex
 
 Oracle中聚簇是存储一组表的方法，而不是如同SQL Server、Sybase中那样（那是Oracle中的IOT）。概念上是通过聚簇码列将几张表“预连接”，尽可能将聚簇码列相同的几张表的行放入同一个块中。
 不使用聚簇的情况：
 
 1．聚簇可能消极影响DML性能；
 
 2．全扫描表的性能会受到影响——不仅仅扫描一个表，而是对多个表全扫描；
 
 3．聚簇中的表不能TRUNCATE。
 
 
 
 
 
Although a normal index does not storenull key values, cluster indexes store null keys. There is only one entry foreach key value in the cluster index. Therefore, a cluster index is likely to besmaller than a normal index on the same set of key values.
 
 

 Although a normal index does not store null key values, cluster indexes storenull keys. 
 虽然普通的索引不保存空的键值，但cluster index保存空的keys？
 为什么cluster index保存空的keys？什么情况呢，搞不明白
 请指教
 
 
 
 
 
cluster  在存储中，是多个表集合在一起存储的，也就是说多个表的数据存储在同一个 block 中。如果多个表中同时存在null  key，是不是也需要把这些集中起来放在一起？你去观察一下？
 
 想到底是放 null key  呢还是不放 null  key  好呢? 根据 cluster  的特点，结合起来考虑考虑看看。
 
 
 其实，普通 b-tree index 也不是说就不能放 null  keys ，只不过可能oracle认为大多数查询是等值或者范围查询，很少用 is  null 查询，并且如果表中存在大量的 null 的时候不存储 null  有利于减小索引大小提高性能。在各种因素权衡下选择了不存储 null 。
 
 如果你实在觉得没有很明显的理由，大不了就先记下好了。
 
 
Each cluster key value is stored onlyonce for all the rows of the same key value; it therefore uses less storagespace.
 
 
散列聚簇表hash cluster
 
 概念类似索引聚簇表，但用散列函数代替了聚簇码索引。Oracle采用行的码值，使用内部函数或者自定义的函数进行散列运算，从而指定数据的存放位置。这样没有在表中增加传统的索引，因此不能Range Scan散列聚簇中的表，而只能全表扫描（除非单独建立索引）。
 
 
CREATE CLUSTER hash_cluster
 
 (hash_key NUMBER)
 
 HASHKEYS 1000
 
 SIZE 8192;
 
 索引聚簇需要空间时是动态分配，而散列聚簇表在创建时确定了散列码数（HASHKEY）。Oracle采用第一个不小于HASHKEY的质数作为散列码数，将散列码数*SIZE就得到分配的空间（字节），可容纳HASHKEYS/TRUNC(BLOCKSIZE/SIZE)字节的数据。
 
 性能上，散列聚簇表消耗较少I/O，较多CPU，所需执行时间较少，大体取决于CPU时间（当然可能要等待I/O，取决于配置）。
 
 下列情况下使用散列聚簇表较为合适：
 
 1. 在一定程度上精确知道整个过程中表中记录行数或者合理的上限，以确定散列码数；
 
 2.不大量执行DML，尤其是插入。更新不会产生显著的额外开销，除非更新HASHKEY，这样会导致行迁移；
 
 3.总是通过HASHKEY值访问数据。
 
 

 
 

 
 

 
 

 
 

 
 

 

1、索引是干什么的？
 索引用于快速找出在某个列中有一特定值的行。不使用索引，mysql必须从第一条记录开始读完整个表直到找出相关的行。
 表越大，花费的时间越多。如果表中查询的列有一个索引，mysql能快速到达一个位置搜寻到数据文件的中间，没有必要查看所有数据。
 
大多数mysql的索引(primary key、index、unique、fulltext)在B树中存储，只是空间列类型的索引使用R树，并且memory表还支持hash索引。
 
2、索引好复杂，我该怎么理解索引，有没有一个更形象的例子？
 想象一下，你面前有本词典，数据就是书的正文内容，你就是那个cpu，而索引是书的目录。
 
3、索引越多越好？
 大多数情况下，索引能大幅提高查询效率。但是:
 数据变更(增删改)都需要维护索引，因此更多索引意味着更多维护成本；
 也意味着需要更多控件空间(一本书100页，却有50页目录？)；
 过小的表，建索引可能会更慢(读2页的宣传手册，你还需要先去找目录？)
 
4、索引的字段类型问题
 text类型，也可建索引(需要指定长度)；
 mysiam存储引擎长度综合不能超过1000字节；
 用来筛选的值尽量保持和索引列同样的数据类型。
 
5、like能用到索引？
 尽量减少like查询，但是也不是绝对不可用，xxx%是可以用到索引的。
 除了like，以下操作符也可以用到索引：
 
<，<=，=，>，>=，between，in
 
这些用不到索引：
 
<>，not in，!=
 
6、什么样的字段不适合建索引？
 列的值唯一性太小(比如性别，类型)，不适合建索引。
 (什么叫大小？一般来说，同值的数据超过表的15%，那就没有必要建索引了)
 
更新非常频繁的数据不适合建索引。
 
7、一次查询能用多个索引？
 不能
 
8、多列查询该如何建索引？
 一次查询只能用到一个索引， a列建索引还是b列建索引？谁的区分度（同值的少）更高，建谁！
 
当然，联合索引也是个不错的方案。
 
9、联合索引的问题
 where a = 'xxx'可以使用ab联合索引；
 where b = 'xxx'则不能命中ab联合索引。
 
所以大多数情况下，有ab索引了，就不用再去建a索引了。
 
10、哪些常见的情况不能用到索引？
 
like '%xxx'
not in
!=
 
对列进行函数运算，如：
 
where md5(password) = "xxx"
 
存了数值的字符串类型字段(如手机号)，查询是记得不要丢掉值的引号，否则无法命中索引：
 
select * from test where mobile = 13800002222;
 
如果mobile字段是char或者varchar类型，则上面查询无法命中索引。
 
11、NULL的问题
 Null会导致索引形同虚设，所以在设计表结构应避免NULL的存在。
 可用其他方式来表达，比如-1。