1、建立b-tree索引
（1）相关概念
根块（一个）：索引顶级块，它包含指向下一级节点（分支块或叶块）的信息。
分支块：它包含指向下一级的节点（分支块或叶块）的信息。
叶块：它包含索引入口数据，索引入口包含索引列值或受限ROWID
（2）建立索引
如果在where子句中要经常引用某列或某几列，应该给予这些列值建立B-*树索引
10:23:58 SQL> create index ind_ename on scott.emp(ename) pctfree 30
10:24:32   2  tablespace indexes;
Index created.
（3）使用索引
10:24:41 SQL> set autotrace on explain
10:26:54 SQL> select * from scott.emp where ename='SCOTT';
EMPNO ENAME      JOB       HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ------------------- ---------- ---------- ----------
7788 SCOTT      ANALYST   1987-04-19 00:00:00       3000                    20
Execution Plan
----------------------------------------------------------
Plan hash value: 48385638
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_ENAME |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SCOTT')
2、建立位图索引
位图索引以为位值标识索引数据，它主要用于在DSS(Decision  Support  System)系统中执行数据统计、数据汇总等操作。
B*-树索引建立在重复值很少的列上，而位图索引建立在重复值很多，不同值相对固定的列上。
使用位图索引可以节省大量的磁盘空间，为B*-树索引所用空间1/20~1/10.
建立位图索引时，Oracle会基于每个不同值建立一个位图。
（1）建立位图索引
SQL> create bitmap index test_sex_bitind on test(sex) tablespace indexes;
Index created.
（2）分析索引结构
SQL> analyze index test_sex_bitind validate structure;
Index analyzed.
SQL> select index_name,index_type,tablespace_name,blevel,leaf_blocks,num_rows from user_indexes    where  index_name='TEST_SEX_BITIND';
INDEX_NAME      INDEX_TYPE      TABLESPACE_NAME   BLEVEL LEAF_BLOCKS   NUM_ROWS
--------------- --------------- ---------------- ------- ----------- ----------
TEST_SEX_BITIND BITMAP          INDEXES            0           1          2
——在重复值高的列上适合建立bitmap的索引
（3）使用位图索引
SQL> select /*+ index (test TEST_SEX_BITIND)*/ name,sex from test where sex='F';
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2624764158
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 | 10000 |    97K|    85   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST            | 10000 |    97K|    85   (0)| 00:00:02 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                 |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | TEST_SEX_BITIND |       |       |            |          |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SEX"='F')
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
0  bytes sent via SQL*Net to client
0  bytes received via SQL*Net from client
0  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
10000  rows processed
SQL> drop index TEST_SEX_BITIND;
Index dropped.
3、建立函数索引
函数索引是基于函数或表达式所建立的索引
（1）建立函数索引
SQL> conn scott/tiger
Connected.
SQL> create index emp_ename_funind on emp(lower(ename)) tablespace indexes;
Index created.
（2）使用函数索引
SQL> set autotrace on;
SQL> select * from emp where lower(ename)='scott';
EMPNO ENAME                          JOB                                MGR HIREDATE                   SAL       COMM      DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ----------  ----------
7788 SCOTT                          ANALYST                           7566 1987-04-19 00:00:00       3000                     20
Execution Plan
----------------------------------------------------------
Plan hash value: 519807088
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_ENAME_FUNIND |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("ENAME")='scott')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
0  consistent gets
0  physical reads
0  redo size
0  bytes sent via SQL*Net to client
0  bytes received via SQL*Net from client
0  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed
4、建立反向索引
反向索引时索引列值按相反的顺序存放的索引。
在顺序递增的列上建立普通B*-树索引时，如果表的数据量非常大，将导致索引数据分布布局（偏向某个方向）。为了避免出现这种情况， 应在顺序递增的列上建立反向索引。
用于建立索引的列值是连续的或通过 序列生成时，避免索引存放到集中的leaf block上,避免生成热块。
（1）建立反向索引
03:08:46 SQL> create index r_empno_ind on test(empno) reverse;
Index created.
对于emp表里empno列来说，因为客户ID号顺序递增，所以为了均衡索引数据分布，应在该列上建立反向索引。
（2）重建索引
03:09:46 SQL> alter index r_empno_ind rebuild reverse;
Index altered.
（3）使用反向索引
03:12:41 SQL> set autotrace on explain
03:12:43 SQL> select ename from test
03:13:07   2    where empno=7788;
ENAME
----------
SCOTT
Execution Plan
----------------------------------------------------------
Plan hash value: 514902531
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | R_EMPNO_IND |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
Note
-----
- dynamic sampling used for this statement