第14部分 索引

一、目标：

了解oracle11g的索引的分类
理解oracle11g的索引查找的原理
能够根据情况建立合适的索引

二、索引1：

1、索引对于表，相当于一本书的目录对于书的结构

2、索引是建在列上的

索引是与表相关的一个可选结构
用以提高 SQL 语句执行的性能
减少磁盘I/O
使用 CREATE INDEX 语句创建索引
在逻辑上和物理上都独立于表的数据——索引和表可以不在一个表空间中，也可以和数据不在一个磁盘中
Oracle 自动维护索引——数据进行增、删、改操作时，oracle会自动维护索引，不需要用户对索引做手动改动。

三、索引2：

索引分为：B树索引（平衡树索引）、位图索引（一般在数据仓库中使用）。
B树索引分为：唯一索引、组合索引、反向键索引、基于函数的索引

四、索引的图例

1、根节点、分支节点块、叶子节点块是索引的内部结构；

0 B1：索引列的值在0-500时，就放在B1块中

L1: 0<=X<200

R1：索引对应的值位置为0

R2：索引对应的值位置为29

R3：索引对应的值位置为190

R1、R2、R3是三个条目

L2: 200<=X<400

L3: 400<=X5400

0 B2：索引列的值在=500-100时，就放在B2块中

L1: 0<=X<200

L2: 200<=X<400

L3: 400<=X5400

0 B3：索引列的值>=1000-时，就放在B3块中

2、表的数据行，才是真正指向的数据

3、表的数据行，是代表的建立索引的列（字段）的值，有多少行，就有多少值

四、创建索引

1、创建标准索引

create table student(sno number,sname varchar2(10),sage int,male char(2));

insert into student values(1,'Tom',21,'M');
insert into student values(2,'tom',22,'M');
insert into student values(3,'John',21,'F');

SQL> create index ind1 on student(sno);

SQL>select * from user_indexes;

如下：

索引名：IND1;

索引类型：普通或标准

索引表空间：USERS;

表拥有着：SCOTT

索引关联表名：Student

2、查询索引对应的列信息

SQL>select * from user_ind_columns u where u.index_name='IND1'

五、分析索引

语法：analyze index <index_name> validate structure;

查看index_stats表中的pct_used列的值，如果pct_used的值过低，说明在索引中存在碎片，可以重建索引，来提高pct_used的值，减少索引中的碎片。

因为在对表的删除操作时，索引也会随之被删除，在这个过程中，会产生一些索引碎片。碎片越多，对oracle的查找性能影响越大。所以需要通过分析索引，并对索引进行重建。

举例：

1、创建表，

SQL> create table t(c1 char(8));

2、插入130000条数据

begin
       for i in 1..5000000 loop
   insert into t values(ltrim(to_char(i,'00000009')));
   if mod(i,100)=0 then
   commit;
   end if;
       end loop;
   end;
   /

3、建立索引

create index ind_t on t(c1) 【tablespace 表空间名】

4、创建索引后，查看碎片

SQL> select name,pct_used from index_stats where name='IND_T';

5、分析索引

SQL> analyze index ind_t validate structure;

SQL> select name,pct_used from index_stats where name='IND_T';

查看后PCT_USED使用率是90%，没有碎片。PCT_USED最大利用率是90%，所以没有碎片。

6、手动删除数据，模拟产生碎片

（1）创建5000000行数据

（2）创建索引

SQL>create index ind_t on t(c1)

（3）分析索引

SQL> analyze index ind_t validate structure;

SQL> select name,pct_used from index_stats where name='IND_T';

（3）删除将近400万行，没有出现PCT_USED降低的情况

六、唯一索引

唯一索引确保在定义索引的列中没有重复值
Oracle 自动在表的主键列上创建唯一索引
使用CREATE UNIQUE INDEX语句创建唯一索引

例子1：创唯一索引，可以使表的此列没有重复值

1、创建唯一索引，并给此列插入重复数据，报错，违反唯一性约束

SQL> create table T3(sno number,sname varchar2(10));

SQL> create unique index ind_sno on t3(sno);

2、插入NULL是否可以？测试一下，同时插入NULL值，且插入2个都可以，所以NULL值是不违反unique约束

例子:2：创建表带主键，自动创建索引

1、创建表t2,带主键

SQL> create table t2(ID number primary key,name varchar(2));

2、查看索引，索引名增加了SYS——C0012517索引，table_name是T2,所以有主键的表，自动创建了索引。

SQL>select * from user_indexes;

7、组合索引

组合索引是在表的多个列上创建的索引
索引中列的顺序是任意的
如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度。适合用于多列作为条件时，较好

SQL> CREATE INDEX comp_index ON t3(sno, same);

8、反向键索引

反向键索引反转索引列键值的每个字节
通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上
创建索引时使用REVERSE关键字

oracle 为了实现索引的值能够平衡放置到各个节点块中。

实际数据 sno:1001 1002 1003 1004 1005 1006 1007 1008

反向键索引：1001 2001 3001 4001 5001 6001 7001 8001——注意，不改变数据表中的值，只是索引按照值的反向进行索引

SQL> CREATE INDEX rev_index ON itemfile (itemcode) REVERSE;

SQL> ALTER INDEX rev_index REBUID NOREVERSE;

9、位图索引——与B树索引完全不一样

位图索引适合创建在低基数列上（低基数列，行数虽然多，但是取值确非常少，比如性别一栏——男或女），接收索引的空间，01，00等
位图索引不直接存储ROWID，而是存储字节位到ROWID的映射
节省空间占用
如果索引列被经常更新的话，不适合建立位图索引
总体来说，位图索引适合于数据仓库中，不适合OLTP中

SQL> CREATE BITMAP INDEX bit_index ON t3(smale);

10、基于函数的索引

基于一个或多个列上的函数或表达式创建的索引
表达式中不能出现聚合函数
不能在LOB类型的列上创建
创建时必须具有 QUERY REWRITE 权限

11、删除索引

SQL> DROP INDEX item_index;

12、重建索引

ALTER INDEX index_name REBUILD [ONLINE] [NOLOGGING] [COMPUTE STATISTICS];

其中：ONLINE使得在重建索引过程中，用户可用对原来的索引进行修改；

NOLOGGING表示在重建过程中产生最少的重做条目redo Entry；

COMPUTE STATISTICS表示在重建过程中就生成了oracle优化器所需的统计信息，避免了索引重建之后再进行analyze或dbms_stats来收集统计信息。

13、索引的分区

可以将索引存储在不同的分区中，与分区有关的索引有三种类型：
局部分区索引－在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致
全局分区索引－在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关
全局非分区索引－在分区表上创建的全局普通索引，索引没有被分区

例子：

1、创建分区表：

create table employee1(code number,name varchar2(10))
partition by range(code)
(
    partition p1 values less than(1000),
    partition p2 values less than(2000),
    partition p3 values less than(maxvalue)
);

2、创建局部索引,并查看是否是分区索引

SQL> create index ind_employeel on employee1(code) local;

SQL> select index_name,table_name,partitioned from user_indexes;：

SQL>select * fro user_ind_partitions;

SQL> select * from user_tab_partitions where table_name='EMPLOYEE1';

表分区信息和索引分区信息一致

3、全局分区索引

（1）创建分区表

create table employee2(code number,name varchar2(10))
partition by range(code)
(
    partition p1 values less than(1000),
    partition p2 values less than(2000),
    partition p3 values less than(maxvalue)
);

（2）创建全局分区索引——需要单独制定索引分区信息，与分区表信息无关

create index ind_employee2 on employee2(code) global partition by range(code)
(
    partition a1 values less than(1000),
    partition a2 values less than(1500),
    partition a3 values less than(maxvalue)
);

（3）查看索引分区和表分区信息

SQL> select * from user_ind_partitions where index_name='IND_EMPLOYEE2';

SQL> select * from user_tab_partitions where table_name='EMPLOYEE2';

4、全局非分区索引——

（1）索引不分区，所以在分区索引中是查不到的

（2）查不到分区索引的信息

SQL> select * from user_ind_partitions where index_name='IND_EMPLOYEE3';

SQL> select * from user_tab_partitions where table_name='EMPLOYEE3';

14、获取索引的信息

与索引有关的数据字典视图有：

USER_INDEXES －用户创建的索引的信息
USER_IND_PARTITIONS －用户创建的分区索引的信息
USER_IND_COLUMNS －与索引相关的表列的信息

SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS ORDER BY INDEX_NAME, COLUMN_POSITION;

本作品采用知识共享署名 4.0 国际许可协议进行许可

第14部分索引

文章评论

第14部分 索引

文章评论

第14部分索引