Oracle数据库开发——了解索引

关于索引，有一种比喻，表数据是本大书，索引时这本大书的目录。如果书只有几页，你完全可以一眼翻看完，那目录就没太大必要了。
因为看目录的时间，几乎可以让你看完这本书。


索引是关系数据库中的一种基本对象，它是表中数据与相应存储位置的关系。利用索引可以加快数据的检索速度，并实现对完整性的检查。
确切的说，索引是建立在表列上的数据库对象(书的目录是基于书的内容嘛)。

当然，目录对阅读内容的用户会有影响！
表上能否建立索引，建立什么类型的索引，以及建立多少索引，会直接影响到应用的性能。如果没有索引或索引建立的不正确，则会影响数据
查询的速度，因为可能此时不得不进行全表扫描;如果索引，则会影响更新的速度，因为要花更多的时间来维护索引(内容变了，目录也要变)。

建立索引的原则:
A.依据表的大小创建索引。一般来说，小表不必建索引，可以通过全表扫描的性能分析来判断建立索引后是否改善了数据库性能。
B.依据表和列的特征创建索引。
在经常进行查询的列上建立索引，可以提高搜索的速度;
在经常进行连接查询的列上建立索引，可以提高搜索速度;
C.限制表中索引的数量。过多或过少的索引都会影响系统的性能
D.要合理安排符合索引总列的顺序，将频繁使用的列放在其他列的前面

Oracle数据库中提供的索引类型:B-树索引、位图索引、基于函数的索引、反向值索引、域索引。

create table student
(s_num  number(6)
,s_name varchar2(20)
,s_sex  varchar2(1)
)

insert into student values (001,'Tom','F');
insert into student values (002,'Jane','M');
insert into student values (003,'James','F');
insert into student values (004,'Criss','F');
insert into student values (005,'Marry','M');

insert into student values (006,'Rose','M');
insert into student values (007,'Lin','F');
insert into student values (008,'Jack','F');
insert into student values (009,'Scott','F');
insert into student values (010,'Tomas','F');



又称为平衡树索引，是数据库中最常用的一种索引。
这种索引的叶子节点保存索引键值和指向索引行的ROWID。

create unique index student_idx on student(number) tablespace users;

B-树索引可以用unique限定为唯一索引。



与B-树索引不同，位图索引不存储ROWID值，也不存储键值。它是一个索引键条目存储指向多行的指针，即每个索引条目指向多行。
位图索引适合索引基值数少，高度重复，且只读的应用环境使用，所以适合于数据仓库等环境。
create bitmap index student_bitmap_idx on student (s_sex) tablespace users;

位图索引实际上是一个二维数组，数组的列由表记录数决定，行数由索引列基值数决定。

举个例子来说:
student表的sex字段值只有 男，女 两个基值。表中数据假设有10条。3男，7女
那么我们可以想象下索引该怎么保持值以迅速的区分我们需要的数据呢?

M  F  F  F  F  M   M   M   F   F
0  1  1  1  1  0   1   0   1   1

F为男，M为女，我们在过滤条件中加入  sex = 'F' 希望在找到男生的相关数据记录，那么此时数据库只要关注索引值为1的数据记录即可。

SQL> explain plan for select *from student where s_sex = 'F';
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4039275447
--------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     7 |   189 |
|   1 |  TABLE ACCESS BY INDEX ROWID | STUDENT            |     7 |   189 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                    |       |       |
|*  3 |    BITMAP INDEX SINGLE VALUE | STUDENT_BITMAP_IDX |       |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S_SEX"='F')
Note
-----
   - dynamic sampling used for this statement (level=2)
   
   S_SEX='F'是过滤条件，走了STUDENT_BITMAP_IDX的索引，BITMAP INDEX SINGLE VALUE，这个VALUE是不是就是代表 F 的索引值呢?
   
   

基于函数的索引是在B-树索引或位图索引的基础上，将一个函数计算得到的结果，作为索引值而创建的索引。

例如: 加快 select * from student where upper(s_name) like '%JA%' 查询效率


--创建函数索引
SQL> create index student_fun_idx on student(upper(s_name)) tablespace users;
 
Index created


--查看执行计划验证是否走函数索引   
SQL> explain plan for select * from student where upper(s_name) like '%JA%';
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2499371011
--------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    39 |     2
|   1 |  TABLE ACCESS BY INDEX ROWID| STUDENT         |     1 |    39 |     2
|*  2 |   INDEX FULL SCAN           | STUDENT_FUN_IDX |     1 |       |     1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(UPPER("S_NAME") IS NOT NULL AND UPPER("S_NAME") LIKE '%JA%')
Note
-----
   - dynamic sampling used for this statement (level=2)
   
   
注意函数索引的写法，WHERE条件 索引字段使用方式 必须与 函数索引的创建方式 相同
    即:  
                      where upper(s_name) = 'XXX'  走索引，索引全扫描
                      where s_name = upper('XXX')或者 where s_name like upper('%JA%') 不走索引，全表扫描
                      
验证一下:
SQL> explain plan for select * from student where upper(s_name) = 'CRISS';
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2505413714
--------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    39 |     2
|   1 |  TABLE ACCESS BY INDEX ROWID| STUDENT         |     1 |    39 |     2
|*  2 |   INDEX RANGE SCAN          | STUDENT_FUN_IDX |     1 |       |     1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("S_NAME")='CRISS')
Note
-----
   - dynamic sampling used for this statement (level=2)
 
18 rows selected
 
SQL> explain plan for select * from student where s_name = upper('Criss');
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2356778634
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    27 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| STUDENT |     1 |    27 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("S_NAME"='CRISS')
Note
-----
   - dynamic sampling used for this statement (level=2)
 
17 rows selected



反向键值索引可以避免不平衡的索引，使索引条目在索引中分布的更加均匀。
可以直接创建 :
create index student_rev_idx on student(s_num) reverse;
或者用alter index 语句修改
alter index student_rev_idx rebuild noreverse;
alter index student_rev_idx rebuild reverse;

这里有一个疑问:
什么样的表适合创建反向键值索引？或者说，什么情况下，应该将普通的B-tree索引利用alter index idx_name rebuild reverse 子句修改成反向键值索引？
改变后，索引段中存储的键值分布会有什么变化呢？


先了解一下反向键值的存储形式及作用。
反向键值索引的存储形式:
    原值:12345
    索引值:54321
    
反向键值索引的作用:
消除热块、使得B-Tree索引分布更加平衡(防止树的单边增长严重)

举个例子:
索引键原值: 12345
             12346
             12347
  这个三条连续数据将存储在同一个数据块上！
  
反向键值索引值: 54321
                 64321
                 74321
   三条数据将存储在不同的数据块上！
   
由此，可以解决上述问题，B-tree 键值太靠近，使得树单边增长，要不断调整才能达到balance的表适合建立反向键值索引。
                          将原连续键值reverse后，形成多个不同键值分布在不同的数据块上。
                          

===========================================================================================
索引的修改
索引的修改包括 合并索引、重构索引、重命名索引。

在数据库的使用过程中，表数据的不断更新，就会在索引中产生越来越多的存储碎片，这会使得索引失效。
这种情况下可以使用合并所引或重构索引来清除这些存储碎片。

索引合并:
alter index index_name coalesce deallocate unused;

coalesce 表示合并索引，deallocate unused 表示合并后释放多余的存储空间

索引重构:
alter index index_name rebuild;

其实质上是先删除原来的索引，然后再根据原来的索引结构重建。

重命名索引:
alter index index_name rename to new_name;

============================================================================================
索引的监视
开发中，我们可以通过打开索引监视，来了解索引的使用情况

step 1 . 打开我们要监视的索引
SQL> alter index student_rev_idx monitoring usage;
 
Index altered
 
step 2 . 查看动态性能视图 v$object_usage
SQL> select index_name,used,start_monitoring from v$object_usage;
 
INDEX_NAME                     USED START_MONITORING
------------------------------ ---- -------------------
STUDENT_REV_IDX                NO   05/15/2014 10:32:02


当前索引 STUDENT_REV_IDX 的状态为未使用


step 3 . 执行走索引的SQL语句
SQL> select * from student where s_num = '1004';
 
  S_NUM S_NAME               S_SEX
------- -------------------- -----
   1004 Criss                F


step 4 . 再次查看动态性能视图 v$object_usage
SQL> select index_name,used,start_monitoring from v$object_usage;
 
INDEX_NAME                     USED START_MONITORING
------------------------------ ---- -------------------
STUDENT_REV_IDX                YES  05/15/2014 10:32:02


此时，索引STUDENT_REV_IDX状态为使用过


step 4 . 当不需要监视索引时，关闭监视


SQL> alter index student_rev_idx nomonitoring usage;
 
Index altered

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28929558/viewspace-1161733/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28929558/viewspace-1161733/