oracle 索引

oracle的索引分为5种:唯一索引，组合索引，反向键索引，位图索引，基于函数的索引

 

创建索引的标准语法:

CREATE INDEX 索引名 ON 表名 (列名)

     TABLESPACE 表空间名;

创建唯一索引:

CREATE unique INDEX 索引名 ON 表名 (列名)

     TABLESPACE 表空间名;

创建组合索引:

CREATE INDEX 索引名 ON 表名 (列名1,列名2)

     TABLESPACE 表空间名;

创建反向键索引:

CREATE INDEX 索引名 ON 表名 (列名) reverse

     TABLESPACE 表空间名;

-----------------------------------------------------------------------------------------------------------------------

索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引。查询

分类可以按逻辑设计和物理实现来分类。

索引逻辑分类

单列索引：基于一列的操作

多列索引：组合索引，最多为32列。组合索引的列不一定与表中列顺序相同。

惟一索引：列的值各不相同。

非惟一索引：列的值允许相同。

基于函数的惟一索引：利用表中一列或多列基于函数表达式所创建的索引。既可以是B-树，也可以是位图索引。

索引物理分类

分区或非分区索引，非分区既可以是B－树，也可以是位图索引。

B-树：包括正常或反转关键字索引，反转关键字在数据库优化中介绍。

位图索引

索引的存储方式

虽然所有索引都使用 B 树结构，但术语“B 树索引”通常与存储每个关键字的行标识列表的索引关联。

B 树索引结构

至上而下，是根结点、分枝结点及叶子结点，叶子结点中有指向表中数据行的索引行。叶子结点被双向链表在一起，以方便按索引关键字升序或降序扫描。

索引的顶部为根，其中包含指向索引中下一级掂，下一级为分枝块，分枝块又指向索引中下一级的块，最低一级为叶节点，其中包含指向表行的索引项。叶块为双重链接，有助于按关键字值的升序和降序扫描索引。

索引项叶节点的格式

索引项由以下三部分组成：

? 项标题（entry header），存储列数和锁定信息

? 关键字列的“长度- 值”(length-value pairs) ，必需成对出现，定义了列长度，紧跟在列长度之后的就是列的值。

? 行的行标识（RowID）， 包含关键字值。

索引项叶结点弟地征

在非分区表上的 B 树索引中：

? 如果多行倪有相同的关键字值，并且索引没有被压缩，则关键字值重复存放。

? 没有索引项与所有关键字列都为 NULL 的行对应，即如果某列值为Null，则不存储相应的索引项。如果Where子句中索引的所在列值为null，Oracle将不使用索引进行全表扫描。

? 因为所有行都属于同一段，所以使用受限行标识指向表中的行，使用RowID可以节省索引存储空间。

DML操作对索引的影响

当在表上执行 DML 操作时，Oracle 服务器将自动维护所有的索引，下面解释 DML命令对索引的影响：

? 插入（Insert）操作导致在适当的块中插入索引项。

? 删除（Delete）行只导致逻辑删除索引项，删除的行所用的空间不能用于新项，直到删除块中的所有项。

? 更新（Update）操作将选删除，再插入，除了在创建时，其它任何时候PCTFREE 设置对索引都没有影响，即使索引块空间少于 PCTFREE 指定的空间，仍可以向索引块添加新项。

 

 

----------------------------------------------------------------------------------------------------------------------------------

1           什么是索引

索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据。具体来说：

1.1          索引类似书的目录结构

1.2          Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度

1.3          索引直接指向包含所查询值的行的位置，减少磁盘I/O

1.4          与所索引的表是相互独立的物理结构

1.5          Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引

1.6          语法：CREATE INDEX index ON table (column[, column]...);

1.7          Oracle存储索引的数据结构是B-树，位图索引也是如此，只不过是叶子节点不同B-数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。

2           什么是ROWID

ROWID存储了row在数据文件中的具体位置：64位编码的数据，A-Z, a-z, 0-9, +, 和 /;　　row在数据块中的存储方式:比如,ROwID=OOOOOOFFFBBBBBBRRR,含义是：　　

OOOOOO：data object number, 对应dba_objects.data_object_id；

FFF：file#, 对应v$datafile.file#；

BBBBBB：block#；

RRR：row#；

3           何时使用索引

3.1          查询返回的记录数　　

3.2          排序表<40%　　

3.3          非排序表 <7%　　

3.4          表的碎片较多（频繁增加、删除）

4           使用索引的目的

4.1          加快查询速度

4.2          减少I/O操作

4.3     消除磁盘排序

5    了解索引的工作原理

表：emp

　　

　　目标：查询Frank的工资salary

　　建立索引：create index emp_name_idx on emp(name);
 

 

　

 

6           索引的分类

6.1          唯一索引

6.1.1     何时创建：当某列任意两行的值都不相同

6.1.2     当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立

6.1.3     语法：CREATE UNIQUE INDEX index ON table (column);

6.2          组合索引

6.2.1     何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引

6.2.2     组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面

6.2.3     演示(组合列，单独列)

6.3          位图索引

6.3.1     何时创建：列中有非常多的重复的值时候。例如某列保存了 “性别”信息。　　Where 条件中包含了很多OR操作符。较少的update操作，因为要相应的跟新所有的bitmap

6.3.2     结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。

6.3.3     优点：位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多

6.3.4     语法：CREATE BITMAP INDEX index ON table (column[, column]...)

6.3.5     create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');

6.4          基于函数的索引

6.4.1     何时创建：在WHERE条件语句中包含函数或者表达式时

6.4.2     函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

6.4.3     语法：CREATE INDEX index ON table (FUNCTION(column));

7           管理索引的准则

7.1          在表中插入数据后创建索引。在用SQL*Loader或import工具插入或装载数据后，建立索引比较有效；

7.2          索引正确的表和列

7.2.1     经常检索排序大表中40%或非排序表7%的行，建议建索引；

7.2.2     为了改善多表关联，索引列用于联结；

7.2.3     列中的值相对比较唯一；

7.2.4     取值范围（大：B*树索引，小：位图索引）；

7.2.5     Date型列一般适合基于函数的索引；

7.2.6     列中有许多空值，不适合建立索引

7.3          为性能而安排索引列

7.3.1     经常一起使用多个字段检索记录，组合索引比单索引更有效；把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)，在where条件中使用groupid或groupid,serv_id，查询将使用索引，若仅用到serv_id字段，则索引无效；合并/拆分不必要的索引。

7.4          限制每个表索引的数量

7.4.1     一个表可以有几百个索引（你会这样做吗？），但是对于频繁插入和更新表，索引越多系统CPU，I/O负担就越重；建议每张表不超过5个索引。

7.5          删除不再需要的索引

7.5.1     索引无效，集中表现在该使用基于函数的索引或位图索引，而使用了B*树索引；

7.5.2     应用中的查询不使用索引；

7.5.3     重建索引之前必须先删除索引，若用alter index … rebuild重建索引，则不必删除索引。

7.6          索引数据块空间使用

创建索引时指定表空间，特别是在建立主键时，应明确指定表空间；合理设定pctfress，注意：不能给索引指定pctused；。估计索引的大小和合理地设置存储参数，默认为表空间大小，或initial与next设置成一样大。

7.7          考虑并行创建索引

对大表可以采用并行创建索引，在并行创建索引时，存储参数被每个查询服务器进程分别使用，例如：initial为1M，并行度为8，则创建索引期间至少要消耗8M空间；

7.8          考虑用nologging创建索引

7.8.1     对大表创建索引可以使用nologging来减少重做日志；

7.8.2     节省重做日志文件的空间；

7.8.3     缩短创建索引的时间；

7.8.4     改善了并行创建大索引时的性能。

8           怎样建立最佳索引

8.1          明确地创建索引

　　create index index_name on table_name(field_name)
　　tablespace tablespace_name
　　pctfree 5
　　initrans 2
　　maxtrans 255
　　storage
　　(
　　minextents 1
　　maxextents 16382
　　pctincrease 0
　　);　　

8.2          创建基于函数的索引

　　         常用与UPPER、LOWER、TO_CHAR(date)等函数分类上，例：create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;

8.3          创建位图索引

对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，例：　　create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

8.4          明确地创建唯一索引

可以用create unique index语句来创建唯一索引，例：create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

8.5          创建与约束相关的索引

可以用using index字句，为与unique和primary key约束相关的索引，例如：　　alter table table_name　add constraint PK_primary_keyname primary key (field_name)　　using index tablespace tablespace_name；据产生大量db file sequential read锁等待；

 

-----------------------------------------------------------------------------

索引分类：

• 唯一索引，作用是数据约束，保证数据唯一，还有就是数据索引，提高查询效率
• 一般索引，只有数据索引的作用。

唯一索引：

create unique index idx_employee_empname on employee(empname);

create unique index idx_employee_empname on employee(empname);

create unique index idx_employee_empname on employee(empname);

一般索引：

create index idx_employee_address on employee(address);
create unique index idx_employee_field1_field2 on employee(field1,field2);

create index idx_employee_address on employee(address);
create unique index idx_employee_field1_field2 on employee(field1,field2);

create index idx_employee_address on employee(address);
create unique index idx_employee_field1_field2 on employee(field1,field2);

函数索引：

create index idx_product_nvl_price on product(nvl(price,0.0));

create index idx_product_nvl_price on product(nvl(price,0.0));

create index idx_product_nvl_price on product(nvl(price,0.0));

删除索引：

drop index idx_employee_empname;

drop index idx_employee_empname;

drop index idx_employee_empname;

指定索引表空间

create index idx_employee_address on employee(address) tablespace indexs;

create index idx_employee_address on employee(address) tablespace indexs;

create index idx_employee_address on employee(address) tablespace indexs;

查看索引

select * from user_indexes where table_name = 'SHOP_PD'

select * from user_indexes where table_name = 'SHOP_PD'