1 /*
  2 总结：本质原因：其实就是位图索引存放的是0,1的比特位，占字节数特别少。
  3 */
  4 
  5 --位图索引跟踪前准备
  6 drop table t purge;
  7 set autotrace off
  8 create table t as select * from dba_objects;
  9 insert into t select * from t;
 10 insert into t select * from t;
 11 insert into t select * from t;
 12 insert into t select * from t;
 13 insert into t select * from t;
 14 insert into t select * from t;
 15 update t set object_id=rownum;
 16 commit;
 17 
 18 --观察COUNT(*)全表扫描的代价
 19 set autotrace on
 20 set linesize 1000
 21 select count(*) from t;
 22 
 23 
 24 
 25 ------------------------------------------
 26   COUNT(*)
 27 ----------
 28   4684992
 29 执行计划
 30 ----------------------------------------------------------
 31 Plan hash value: 2966233522
 32 
 33 -------------------------------------------------------------------
 34 | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
 35 -------------------------------------------------------------------
 36 |   0 | SELECT STATEMENT   |      |     1 | 20420  (11)| 00:04:06 |
 37 |   1 |  SORT AGGREGATE    |      |     1 |            |          |
 38 |   2 |   TABLE ACCESS FULL| T    |   294M| 20420  (11)| 00:04:06 |
 39 -------------------------------------------------------------------
 40 统计信息
 41 ----------------------------------------------------------
 42           0  recursive calls
 43           0  db block gets
 44       66731  consistent gets
 45           0  physical reads
 46           0  redo size
 47         426  bytes sent via SQL*Net to client
 48         415  bytes received via SQL*Net from client
 49           2  SQL*Net roundtrips to/from client
 50           0  sorts (memory)
 51           0  sorts (disk)
 52           1  rows processed
 53           
 54           
 55 
 56 
 57 
 58 --观察COUNT(*)用普通索引的代价
 59 create index idx_t_obj on t(object_id);
 60 alter table T modify object_id not null;
 61 set autotrace on
 62 select count(*) from t;
 63 
 64 
 65 
 66 
 67 
 68   COUNT(*)
 69 ----------
 70   4684992
 71 普通索引的执行计划
 72 ---------------------------------------------------------------------------
 73 | Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
 74 ---------------------------------------------------------------------------
 75 |   0 | SELECT STATEMENT      |           |     1 |  3047   (2)| 00:00:37 |
 76 |   1 |  SORT AGGREGATE       |           |     1 |            |          |
 77 |   2 |   INDEX FAST FULL SCAN| IDX_T_OBJ |  4620K|  3047   (2)| 00:00:37 |
 78 ---------------------------------------------------------------------------
 79 普通索引的统计信息
 80 ----------------------------------------------------------
 81           0  recursive calls
 82           0  db block gets
 83       10998  consistent gets
 84           0  physical reads
 85           0  redo size
 86         426  bytes sent via SQL*Net to client
 87         415  bytes received via SQL*Net from client
 88           2  SQL*Net roundtrips to/from client
 89           0  sorts (memory)
 90           0  sorts (disk)
 91           1  rows processed
 92 
 93 
 94 
 95 
 96 --观察COUNT(*)用位图索引的代价（注意，这里我们特意取了status这个重复度很高的列做索引）
 97 create bitmap index idx_bitm_t_status on t(status);
 98 select count(*) from t;
 99 
100 SQL> select count(*) from t;
101 
102 
103 
104 
105 
106 
107   COUNT(*)
108 ----------
109   4684992
110 
111 位图索引的执行计划
112 -------------------------------------------------------------------------------------------
113 | Id  | Operation                     | Name              | Rows  | Cost (%CPU)| Time     |
114 -------------------------------------------------------------------------------------------
115 |   0 | SELECT STATEMENT              |                   |     1 |   115   (0)| 00:00:02 |
116 |   1 |  SORT AGGREGATE               |                   |     1 |            |          |
117 |   2 |   BITMAP CONVERSION COUNT     |                   |  4620K|   115   (0)| 00:00:02 |
118 |   3 |    BITMAP INDEX FAST FULL SCAN| IDX_BITM_T_STATUS |       |            |          |
119 -------------------------------------------------------------------------------------------
120 位图索引的统计信息
121 ----------------------------------------------------------
122           0  recursive calls
123           0  db block gets
124         125  consistent gets
125           0  physical reads
126           0  redo size
127         426  bytes sent via SQL*Net to client
128         415  bytes received via SQL*Net from client
129           2  SQL*Net roundtrips to/from client
130           0  sorts (memory)
131           0  sorts (disk)
132           1  rows processed
133          
134  

 4、

二、反向索引

三、函数索引

四、全文检索