OraclePLSQL开发基础（第四弹：索引）
索引是数据库管理系统提供的一种用来快速访问表中数据的机制。在数据库管理系统系统中，索引的意义非常重大，使用索引可以显著提高对数据库数据的查询效率，减少磁盘的IO操作，提升整个数据库系统的性能。当定义主键或唯一性的约束时，Oracle会自动在相应的字段上创建唯一性索引。
索引简介
plsql配置oracle主目录索引是建立在数据库表中的一列或多列用来加速访问表中互数据的辅助对象。
索引有以下优点：
- 索引可以大大加快检索数据的速度。
- 使用唯一性索引可以保证数据库表中每一行数据的唯一性。
- 通过索引可以加快表与表之间的连接。
-
在使用分组和排序子句进行数据检索时，使用索引可以显著地减少查询中分组和排序的时间。
但是索引需要在表基础上创建，需要占用额外的物理空间，而且对表数据进行修改时，比如增删改的时候，需要动态地进行维护，这会降低数据维护的速度。
索引原理
在Oracle数据表中，每一张表都有一个ROWID伪列，这个ROWID是用来唯一标识一条记录所在物理位置的一个id号，每一行对应的ROWID值是固定且唯一的。一旦数据存入数据库就确定，不会在对数据库表操作的过程中发生改变，只有在表发生移动或表空间变化等操作产生物理位置变化时，才发生改变。
如果我们要为emp表的ename这个列建立一个索引，如：
CREATE INDEX idx_emp_ename ON emp(ename);
∙1
Oracle在创建idx_emp_ename索引时，会对emp表进行一次全表扫描，获取每条记录ename列的数据，并进行升序排序。同时会获取每条记录的ROWID值，连同排序后的ename列一起存储到索引段中，其格式是（索引列值，ROWID），这种组合也称为索引条目。
当检索数据时，比如使用WHERE子句按指定条件检索数据时，Oracle将首先对索引中的列进行快速搜索，由于索引列已经排过序，因此可以使用各种快速的搜索算法，这样就可以避免对全表进行扫描。在到所要检索的数据后，通过ROWID在emp表中读取具体的记录值。
创建索引
索引的创建方式有两种：
- 自动创建：在定义主键约束或唯一约束时，Oracle自动在相应的约束列sang建立唯一索引。Oracle不推荐人为地创建唯一索引。
- 手动创建：用户可以在其他列上创建非唯一索引。
索引根据其组织形式又可以分为多种类型：
-
单列索引：索引基于单个列创建。
- 复合索引：索引基于多个列创建。
- B树索引：这是Oracle默认使用的索引，B树索引可以是单列索引或复合索引、唯一索引或非唯一索引，索引按B树结构组织并存放索引数据。
- 位图索引：为索引列的每个取值创建一个位图，对表中的每行使用1位（bit，取值为0或1）来表示该行是否包含该位图的索引列的取值。
- 函数索引：索引的取值不直接来自列，而是来自包含有列的函数或表达式。
索引的创建语法如下：
CREATE [UNIQUE] | [BITMAP] INDEX index_name
ON table_name ([column1 [ASC|DESC],column2[ASC|DESC],...] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[NOLOGGING]
[NOLINE]
[NOSORT];
∙1
∙2
∙3
∙4
∙5
∙6
∙7
∙8
这些参数的含义如下：
- UNIQUE：表示唯一索引，默认不使用该选项。
- BITMAP：表示位图索引，默认不使用该选项。
- PCTFREE：指定索引在数据块中的空闲空间。对于经常插入数据的表，应该为表中索引指定一个较大的空闲空间。
- NOLOGGING：表示在创建索引的过程中不产生任何重做日志信息。默认不使用该选项。
- ONLINE：表示在创建或重建索引时，允许对表进行DML操作。默认不使用该选项。
- NOSORT：默认不使用该选项。Oracle在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的，则可以使用该选项。
要使用CREATE INDEX创建索引，需要具有如下两种权限：
- CREATE INDEX：当在用户所在的方案中创建索引时需要具备的权限。
- CREATE ANY INDEX：当在其他用户方案中创建索引时需要具备的权限。
在创建索引时，会对表进行全表扫描，对索引列的数据进行排序，为索引分配存储空间，将索引的定义信息保存到数据字典中。
如果在使用CREATE INDEX时，不指定任何索引类型参数，默认创建的就是标准的B树索引。
CREATE INDEX idx_emp_empnoname ON emp_index(ename,empno);  --B树索引
CREATE INDEX idx_emp_job ON emp_index(job);                --B树索引
CREATE BITMAP INDEX idx_emp_job_bitmap ON emp_index(job);  --位图索引
CREATE INDEX idx_emp_name ON emp(UPPER(ename));            --函数索引
∙1
∙2
∙3
∙4
当创建复合索引时，索引列的顺序决定了索引的性能，通常要将最常查询得列放在前面，不常查询的列放在后面。两个具有不同名称的复合索引列，使用了相同的字段但是顺序不同是合法的。
由于索引的创建会带来一定的性能开销，因为必须要注意创建索引的一些基本原则：
- 小表不需要建立索引，比如只有几十条记录的表，不需要建立索引。
- 对于大表而言，如果经常查询的记录数目少于表中总记录数目的15%，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。
- 对于大部分列值不重复的列可建立索引。
- 对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。
- 对于列中有许多空值，但经常查询所有的非空值记录的 列，应该建立索引。
- LONG和LONG RAW列不能创建索引。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。