oracle的索引定义与实例

⼀、索引

　　索引是⼀种快速访问数据的途径，可提⾼数据库性能。索引使数据库程序⽆须对整个表进⾏扫描，就可以在其中找到所需的数据，就像书的⽬录，可以快速查找所需的信息，⽆须阅读整本书。

　　（⼀）索引的分类

　　逻辑分类：单列索引、组合索引、唯⼀索引、⾮唯⼀索引，函数索引。

　　物理分类：区分索引、⾮分区索引、B树索引、正向索引、反向索引，位图索引。　　

　　（⼆）索引的缺点：

　　　　1、索引占⽤表空间，创建太多索引可能会造成索引冗余。　　　　2、索引会影响INSERT、UPDATE、DELETE语句的性能。　　

　　（三）使⽤索引的原则：　　　　　　　　1、装载数据后再建⽴索引。　　　　2、频繁搜索的列可以作为索引。　　　　3、在联接属性上建⽴索引（主外键）。　　　　4、经常排序分组的列。

　　　　5、删除不经常使⽤的索引。

　　　　6、指定索引块的参数，如果将来会在表上执⾏⼤量的insert操作，建⽴索引时设定较⼤的ptcfree。　　　　7、指定索引所在的表空间，将表和索引放在不同的表空间上可以提⾼性能。　　　　8、对⼤型索引，考试使⽤NOLOGGING⼦句创建⼤型索引。　　　　不要在下⾯的列创建索引：　　　　　　　1、仅包含⼏个不同值得列。　　　　2、表中仅包含⼏条记录。　　　　

⼆、索引的使⽤：　　（⼀）创建索引　　语法：

　　CREATE [UNIQUE] INDEX index_name ON table_name(column_list)　　[TABLESPACE tablespace_name]　　说明：　　

　　unique:指定索引列中值必须是唯⼀的　　index_name:索引名

　　table_name:指定要建⽴索引的表

　　col_name:要建⽴索引的列，可以是多列，那样的索引叫多列索引　　table_space_name:索引存储的表空间

　　实例1：

如果我们经常对emp表进⾏按照empno来查询某个员⼯的信息，SQL> select * from emp where empno=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20我们就应该对empno这⼀列建⼀个索引。create index indx_on_empno on emp(empno) tablespace users;

　　实例2：

如果我们经常查询某个部门⼯资⼤于1000的员⼯信息，那么我们就可以在job和sal列上建⽴所以，这叫组合索引：SQL> select * from emp

2 where job='SALESMAN' and sal>1000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30create index indx_on_job_sal on emp(job,sal)tablespace users;

　　实例3：

我们还可以创建基于函数的索引，不过在此前，我们必须设置⼀下初始化参数：conn system/chage_on_install as sysdba;alter system set query_rewrite_enabled=true;create index indx_lower_jobon emp(lower(job))tablespace users;

　　（⼆）修改索引

就像修改表⼀样，我们在创建索引后可以进⾏修改实例1:分配和释放索引空间alter index indx_on_empnoallocate extent(size 1m);实例2:释放多余的索引空间alter index indx_on_empnodeallocate unused;

实例3:重建索引

alter index indx_on_job_sal rebuild;

实例4:联机重建索引，

使⽤rebuild，若其他⽤户正在表上执⾏dml操作，重建会失败，使⽤如下语句，就可以成功重建

alter index indx_on_job_sal rebuild online;

实例5:合并索引

当相邻索引块存在剩余空间，通过合并索引将其合并到⼀个索引块上alter index indx_on_job_sal coalesce;

实例6:重命名索引

alter index indx_on_job_sal rename to indx_on_jobandsal;

　　（三）删除索引

如果索引不再需要了，留在数据库中将会占⽤资源，我们可以将其删除drop index indx_on_job_sal;

　　（四）查看索引

我们可以从unser_indexes视图中查看有关某表的索引信息，下⾯我先来看看unser_indexes视图的信息:SQL> desc user_indexes;