索引具有两个功能:一是强制实施主键约束和唯一约束,二是提高性能

但是会减低DML操作性的性能。

 

1.1 索引的必要性

       一、索引是约束机制的一部分,通过索引,可以立即(或近似立即)访问键值。如果定义主键,而主键上尚未建立索引,Oracle将自动创建一个索引。唯一约束也需要建立索引,为了提高性能,始终应在字表的外键上建立索引。索引对性能而言至关重要。

       二、可以使用索引的情况是排序。

       三、索引有助于提高性能的第三种情况是联接查询。

 

1.2 索引的类型

OLTP系统中,尽量减少索引的数量,而在OLAP系统中(数据仓库),创建足够的索引会起到帮助作用。

1B*树索引

索引树的叶节点按顺序存储行键,每个键有一个指针,用来确定行的物理位置。行的指针是rowidrowidOracle专用的虚拟列,每个表的每一行都有此虚拟列。每一个rowid在全局上是唯一的。

 

Select  count(*) from employees where last_namebetween 'A%' and 'Z%';

 

注:如果在where 子句中指定的值是null,则会是一个例外情况。null不会参与到B*树索引中,导致始终扫描全表。

Select *from employees where last_name is null;

 

使用B*树的情况如下:

  • 列的基数(不同值得个数)很大

  • 表的行数多

  • 列用在where 子句或join条件中。

 

 

2)位图索引

位图索引使用在数据仓库中,可以从几个维度进行分析。可以用到位图索引的情况如下:

  • 列的基数(不同值的个数)小

  • 表中的行数多。

  • 列用于布尔代数运算。

 

3)索引类型选项

  • 唯一或非唯一  Unique or non_unique

  • 反向键 (Reverse Key)

  • 压缩  (Compressed)

  • 复合 (Composite)

  • 基于函数 (Function based)

  • 升序或降序 (Ascending or     Desending)

以上6个选项均可用于B* 树,只用复合,基于函数,升序或降序应用于位图索引。

 

1.3 创建和使用索引

索引定义在主键约束和唯一约束。

语法:

CREATE[UNIQUE | BITMAP] INDEX [schema.]indexname

ON[schema.]tablename (column[,column…]);

注:默认创建的索引类型是非唯一,非压缩,非反向键B*树。

Eg.

SQL>create table dept(deptno number(8),dname varchar2(20));

SQL>createtable emp (empno number(8),ename varchar2(20),deptno number(8));

SQL>createunique index dept_i on dept(deptno);

SQL>createunique index emp_i on emp(empno);

SQL>createbitmap index emp_i2 on emp(deptno);

SQL>altertable dept add constraint dept_pk primary key(deptno);

SQL>altertable emp add constraint emp_pk primary key(empno);

SQL>altertable emp add constraint emp_fk foreign key(deptno) references dept(deptno);

 

 

1.4 修改和删除索引

alter index 命令属于数据库管理范畴,在1Z0-055中进行解释。

SQL>drop index emp_i2;

 

 

【创建索引案例】

SQL>commwebstore/admin123

SQL>createindex cust_name_i on customers(customer_name,customer_status);

 

在一些低基数列上创建位图索引

SQL>createbitmap index creditrating_i on customers(creditrating);

 

运行下列查询确定刚刚创建的索引的名称及其他一些特性。

SQL>selectindex_name,column_name,index_type,uniqueness from user_indexes natural joinuser_ind_columns where table_name = 'CUSTOMERS';