你好,欢迎访问我的博客!登录
当前位置:首页 - 教程 - 正文 君子好学,自强不息!

oracle索引有几种,oracle索引的使用、建立、修改、删除教程

2018-06-22 06:12:51教程admin57°c
A+ A-
文章目录
目录

oracle索引

索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。下面给出建立索引的方法和要点。

索引的创建语法

create or replace unique|bitmap index <schema>.<index_name> on <schema>.<table_name>
  (<column_name>|<expression> asc|desc ,
   <column_name>|<expression> asc|desc ...)    tablespace <tablespace_name>    storage <storage_settings>    logging|nologging    compute statistics
    nocompress|compress<nn>
    nosort|reverse
    partition|global partition<partition_setting>
  • unique|bitmap : unique表示唯一值索引,bitmap表示位图索引,为空则默认为B-tree索引

  • column_name|expression asc|desc , ... :可以单列索引,也可以多列进行联合索引,当为

  • tablespace : 制定存放索引的表空间(当表和索引在不同的表空间的时候,效率更高)

  • storage : 可以设置表空间的存储参数

  • logging|nologging : 是否对索引产生redolog(对于大表来说,可以设置为nologging从而来减少空间占用,提高效率)

  • compute statistics : 设置为创建索引时,收集统计信息

  • nocompress|compressnn : 是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)

  • nosort|reverse : nosort表示与表中相同的顺序进行创建索引,reverse表示使用与表中相反的顺序进行创建索引

  • partition|nopartition|global partition : 可以在分区表上和未分区表上对创建的索引进行分区


修改索引

对于较早的Oracle版本,修改索引的主要任务是修改已存在索引的存储参数适应增长的需要或者重新建立索引。而Oracle8I及以后的版本,可以对无用的空间进行合并。这些的工作主要是由管理员来完成。

简要语法结构如下,更详细的语法图见电子文档《Oracle8i Reference 》 中的 Alter index.

ALTER [UNIQUE] INDEX [user.]index

[INITRANS n]

[MAXTRANS n] 

REBUILD 

[STORAGE n]

其中:

REBUILD 是 根据原来的索引结构重新建立索引,实际是删除原来的索引后再重新建立。

提示:DBA经常用 REBUILD 来重建索引可以减少硬盘碎片和提高应用系统的性能。

例:

alter index pk_detno rebuild storage(initial 1m next 512k);

ALTER INDEX emp_ix REBUILD REVERSE;

Oracle8i 的新功能可以对索引的无用空间进行合并,它由下面命令完成:

ALTER INDEX . . . COALESCE;

例如:

ALTER INDEX ename_idx COALESCE;

删除索引

当不需要时可以将索引删除以释放出硬盘空间。命令如下:

DROP INDEX [schema.]indexname

例如:

sql> drop index pk_dept;

注:当表结构被删除时,有其相关的所有索引也随之被删除。

新索引类型

Oracle8i为了性能优化而提供新的创建新类型的索引。这些新索引在下面介绍:

基于函数的索引

基于函数的索引就是存储预先计算好的函数或表达式值的索引。这些表达式可以是算术运算表达式、SQL或PL/SQL函数、C调用等。值得注意的是,一般用户要创建函数索引,必须具有GLOBAL QUERY REWRITE和CREATE ANY INDEX权限。否则不能创建函数索引,看下面例子:

例1:为EMP表的ename 列建立大写转换函数的索引idx :

CREATE INDEX idx ON emp ( UPPER(ename));

这样就可以在查询语句来使用:

SELECT * FROM EMP WHERE UPPER(ename) LIKE ‘JOH%’;

例2:为emp 的工资和奖金之和建立索引:

1) 查看emp 的表结构:

SQL> desc emp

 Name Null? Type

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

 EMPNO NOT NULL NUMBER(4)

 ENAME VARCHAR2(10)

 JOB VARCHAR2(9)

 MGR NUMBER(4)

 HIREDATE DATE

 SAL NUMBER(7,2)

 COMM NUMBER(7,2)

 DEPTNO NUMBER(2)

2)没有授权就创建函数索引的提示:

SQL> create index sal_comm on emp ( (sal+comm)*12, sal,comm)

 2 tablespace users storage(initial 64k next 64k pctincrease 0);

create index sal_comm on emp ( (sal+comm)*12, sal,comm)

                                          *

ERROR at line 1:

ORA-01031: insufficient privileges

3) 连接到DBA帐户并授权:

SQL> connect sys/sys@ora816

Connected.

SQL> grant GLOBAL QUERY REWRITE to scott;

Grant succeeded.

SQL> grant CREATE ANY INDEX to scott;

Grant succeeded.

oracle的索引有几种?各有何用途?

1. b-tree索引

Oracle数据库中最常见的索引类型是b-tree索引,也就是B-树索引,以其同名的计算科学结构命名。CREATE 

INDEX语句时,默认就是在创建b-tree索引。没有特别规定可用于任何情况。

2. 位图索引(bitmap index)

位图索引特定于该列只有几个枚举值的情况,比如性别字段,标示字段比如只有0和1的情况。

3. 基于函数的索引

比如经常对某个字段做查询的时候是带函数操作的,那么此时建一个函数索引就有价值了。

4. 分区索引和全局索引

这2个是用于分区表的时候。前者是分区内索引,后者是全表索引

5. 反向索引(REVERSE)

这个索引不常见,但是特定情况特别有效,比如一个varchar(5)位字段(员工编号)含值

(10001,10002,10033,10005,10016..)

这种情况默认索引分布过于密集,不能利用好服务器的并行

但是反向之后10001,20001,33001,50001,61001就有了一个很好的分布,能高效的利用好并行运算。

6.HASH索引

HASH索引可能是访问数据库中数据的最快方法,但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。使用HASH索引必须要使用HASH集群。

索引的特点

  1. 大大加快检索数据的速度

  2. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性

  3. 加速表与表之间的连接

  4. 查询语句汇总含有分组或者排序的语句时,速度更快

  5. 查询的过程中,使用索引,使用优化隐藏器,从而提高系统的性能

索引的不足

  1. 创建和维护索引,比较耗费时间,随着数据量的增大而增大

  2. 创建索引,占一定的物理空间(聚簇索引,占用空间会更大)

  3. 在对表进行增删改的时候,索引相应的也需要进行动态的更新

比较适合建立索引的列的特点

  1. 经常需要搜索的列上

  2. 主键,一般建立唯一性索引,保持数据的唯一性

  3. 外键,提高表与表之间连接的速度

  4. 需要排序的列上

  5. where子句后边经常出现的字段

  6. 经常需要根据范围进行搜索的列上,比如日期

不适合建立索引的列的特点

  1. 很少进行搜索的列上

  2. 列取值比较少的列上

  3. blob类型的列上

  4. 修改频率比较高的列上

限制索引(建立了索引,但是无法使用)

  1. 使用不等于<> 、 != ,(不等于操作符一定会进行全表扫描)

  2. 使用is null 、 is not null (只要索引中出现一个null,那么这个索引就报废了。所以在建立索引的时候,一定要将准备建立索引的列设置为not null)

  3. 使用函数(where子句中含有trunc()、add_months()之类)的时候,sql优化器会自动忽略掉索引

  4. where子句中,进行了数据类型不匹配的比较,比如(where row_num = '1')的时候,生气了优化器会限制索引的使用

查询索引

  • dba_indexes

  • user_indexes

  • uesr_ind_columns

组合索引

  1. 索引中,包含不止一个列。

  2. 在Oracle9i之前,需要先使用前导索引,才能使用组合索引。

Oracle rowid

  实体表中,每一行都有rowid,通过每一行的rowid,Oracle提供了访问单行数据的能力。

选择性

  user_indexes中distinct_keys,选择性越高,那么索引返回的值就越少。

群集因子

  user_indexes中的clustering factor 越接近 leaf block的值的话,说明表中的数据越有序。

二元高度

  dba_indexes 的 Blevel列查看对应索引的二元高度,二元高度随着表的大小以及被索引的列中,值的范围的狭窄程度而变化。重建索引可以降低二元高度。

快速全表扫描

  允许Oracle执行一个全局索引的扫描操作,快速全表扫描可以快速读取B-tree索引上的所有树叶块。

跳跃式扫描

create index TT_index on TT(teamid,areacode);select /*+ index(tt TT_index )*/ count(areacode) from tt;
  选择打赏方式
微信赞助

打赏

QQ钱包

打赏

支付宝赞助

打赏

  选择分享方式
  移步手机端
oracle索引有几种,oracle索引的使用、建立、修改、删除教程

1、打开你手机的二维码扫描APP
2、扫描左则的二维码
3、点击扫描获得的网址
4、可以在手机端阅读此文章
标签:

发表评论

选填

必填

必填

选填

请拖动滑块解锁
>>


  用户登录