oracle索引基础,Oracle基础教程之索引的详细介绍

索引优缺点

索引：供服务器在表中快速查找一个行的数据库结构，在逻辑上和物理上都独立于基表。Oracle 自动维护索引

优点：

① 快速存取数据。

② 改善数据库性能，实施数据的唯一性和参照完整性。

③ 多表检索数据的过程快。

④ 进行数据检索时，利用索引可以减少排序和分组的时间。

缺点：

① 索引将占用磁盘空间。

② 创建索引需要花费时间。

③ 延长了数据修改的时间，因为在数据修改的同时，还要更新索引

索引的分类

1,按存储方法分类可分为B*树索引与位图索引。

2,按功能和索引对象分类可分为以下几类：

1),唯一索引

2),非唯一索引

3),分区索引

4),正向索引

5),反向索引

6),基于函数的索引

B-树索引示意图：

反向键索引示意图：

位图索引示意图：

B-树与位图索引的比较

一、B-树

适合于高基数的列

键值的更新相对昂贵

对使用或谓词的查询语句无效适用于OLTP

二、位图

适合于低基数的列

键值的更新非常昂贵

对使用或谓词的查询语句有效适用于 DSS

创建标准索引示例：

SQL > CREATE  INDEX emp_ename ON emp(ename) TABLESPACE users ;

删除标准索引示例：

SQL > DROP INDEX emp_ename

唯一索引

唯一索引确保在定义索引的列中没有重复值

Oracle 自动在表的主键列上创建唯一索引

使用CREATE UNIQUE INDEX语句创建唯一索引

示例：

SQL > CREATE UNIQUE INDEX emp_empno ON emp(empno) Tablespace users ;

组合索引

组合索引是在表的多个列上创建的索引

索引中列的顺序是任意的

如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

示例：

SQL > CREATE INDEX comp_index ON  emp(empno,ename);

反向键索引

反向键索引反转索引列键值的每个字节

通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上

创建索引时使用REVERSE关键字

示例：

SQL > CREATE INDEX emp_ename_rev ON emp (ename) tablespace users REVERSE;

SQL > ALTER INDEX emp_ename_rev REBUID NOREVERSE;

位图索引

位图索引适合创建在低基数列上

位图索引不直接存储ROWID，而是存储字节位到ROWID的映射

减少响应时间,节省空间占用

示例：

SQL > CREATE BITMAP INDEX emp_sex  ON emp(sex) tablespace users;

基于函数的索引

基于一个或多个列上的函数或表达式创建的索引

表达式中不能出现聚合函数

不能在LOB类型的列上创建

创建时必须具有 QUERY REWRITE 权限

示例：

SQL > CREATE INDEX emp_ename_fun ON emp(LOWER(ename)) tablespace users;

SQL > SELECT empno FROM emp WHERE LOWER(ename)='smith'

重建索引

重建索引可以达到的目点：

1,移动索引的表空间

2,删除数据提高空间利用率

3,反向键索引与B树索引之间的转换

示例：

SQL > ALTER INDEX emp_ename REBUILD;

索引的管理成本

1、存储索引的磁盘空间

2、执行数据修改操作(INSERT、UPDATE、DELETE)产生的索引维护

3、在数据处理时回需额外的回退空间。

分析SQL的执行计划

1,初试化-建表等，以DBA进入

SQL > connect / as sysdba

SQL > @?/rdbms/admin/utlxplan.sql

SQL > create public synonym plan_table for plan_table;

SQL > grant select,update,insert,delete on plan_table to public;

SQL > @?/ sqlplus/admin/plustrce.sql

SQL > grant plustrace to public

2,理解和使用AutoTrace,对于SQL 调整，使用Autotrace是最简单的方法了，我们只需要做：

SQL >SET AUTOTRACE ON

SQL > SET timing ON

修改索引

修改索引的语法格式：

ALTER INDEX [schema.]index_name

[ PCTFREE integer ]

[ INITRANS integer ]

[ MAXTRANS integer ]

[ STORAGE storage_clause]

[ RENAME TO new_index_name];

获取索引的信息

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

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;

本教程电子下载：Oracle学习情境3：雇员信息录入修改查询o