oracle索引开发指南

oracle

索引

需积分: 9 74 浏览量更新于2023-06-11 评论收藏 183KB DOCX 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

资源详情

资源评论

资源推荐

一．索引介绍

1.1 索引的创建语法：

CREATE UNIUQE | 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>

相关说明

1） UNIQUE | BITMAP：指定 UNIQUE 为唯一值索引，BITMAP 为位图索引，省略为 B-

Tree 索引。

2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为

expression 时即“基于函数的索引”

3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)

4）STORAGE：可进一步设置表空间的存储参数

5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用 NOLOGGING

来减少占用空间并提高效率)

6）COMPUTE STATISTICS：创建新索引时收集统计信息

7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键

列中出现的重复值)

8）NOSORT | REVERSE：NOSORT 表示与表中相同的顺序创建索引，REVERSE 表示

相反顺序存储索引值

9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

1.2 索引特点：

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时

间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

1.3 索引不足：

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理

空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低

了数据的维护速度。

1.4 应该建索引列的特点：

1）在经常需要搜索的列上，可以加快搜索的速度；

2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是

连续的；

5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，

加快排序查询时间；

6）在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

1.5 不应该建索引列的特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列

很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反

而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很

少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，

即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为 blob 数据类型的列不应该增加索引。这是因为，这些列的数据量

要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性

能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，

会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索

引。

1.6 限制索引

限制索引是一些没有经验的开发人员经常犯的错误之一。在 SQL 中有很多陷阱会使一些索

引无法使用。下面讨论一些常见的问题：

1.6.1 使用不等于操作符（<>、!=）

下面的查询即使在 cust_rating 列有一个索引，查询语句仍然执行一次全表扫描。

select cust_Id,cust_name from customers where cust_rating <> 'aa';

把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优

化器（更智能）时，将会使用索引。

select cust_Id,cust_name from customers where cust_rating < 'aa' or cust_rating > 'aa';

特别注意：通过把不等于操作符改成 OR 条件，就可以使用索引，以避免全表扫描。

1.6.2 使用 IS NULL 或 IS NOT NULL

使用 IS NULL 或 IS NOT NULL 同样会限制索引的使用。因为 NULL 值并没有被定义。在

SQL 语句中使用 NULL 会有很多的麻烦。因此建议开发人员在建表时，把需要索引的列设

成 NOT NULL。如果被索引的列在某些行中存在 NULL 值，就不会使用这个索引（除非索

引是一个位图索引，关于位图索引在稍后在详细讨论）。

1.6.3 使用函数

如果不使用基于函数的索引，那么在 SQL 语句的 WHERE 子句中对存在索引的列使用函数

时，会使优化器忽略掉这些索引。下面的查询不会使用索引（只要它不是基于函数的索

引）

select empno,ename,deptno from emp where trunc(hiredate)='01-MAY-81';

把上面的语句改成下面的语句，这样就可以通过索引进行查找。

select empno,ename,deptno from emp where hiredate<(to_date('01-MAY-81')+0.9999);

1.6.4 比较不匹配的数据类型

也是比较难于发现的性能问题之一。注意下面查询的例子，account_number 是一个

VARCHAR2 类型,在 account_number 字段上有索引。

下面的语句将执行全表扫描：

select bank_name,address,city,state,zip from banks where account_number = 990354;

Oracle 可以自动把 where 子句变成 to_number(account_number)=990354，这样就限制了

索引的使用,改成下面的查询就可以使用索引：

select bank_name,address,city,state,zip from banks where account_number ='990354';

特别注意：不匹配的数据类型之间比较会让 Oracle 自动限制索引的使用,即便对这个

查询执行 Explain Plan 也不能让您明白为什么做了一次“全表扫描”。

1.7 查询索引

查询 DBA_INDEXES 视图可得到表中所有索引的列表，注意只能通过 USER_INDEXES 的

方法来检索模式(schema)的索引。访问 USER_IND_COLUMNS 视图可得到一个给定表中

被索引的特定列。

1.8 组合索引

当某个索引包含有多个已索引的列时，称这个索引为组合（concatented）索引。在

Oracle9i 引入跳跃式扫描的索引访问方法之前，查询只能在有限条件下使用该索引。比如：

表 emp 有一个组合索引键，该索引包含了 empno、 ename 和 deptno。在 Oracle9i 之前除

非在 where 之句中对第一列（empno）指定一个值，否则就不能使用这个索引键进行一次

范围扫描。

特别注意：在 Oracle9i 之前，只有在使用到索引的前导索引时才可以使用组合索引！

1.9 ORACLE ROWID

通过每个行的 ROWID，索引 Oracle 提供了访问单行数据的能力。ROWID 其实就是直接

指向单独行的线路图。如果想检查重复值或是其他对 ROWID 本身的引用，可以在任何表

中使用和指定 rowid 列。

1.10 选择性

使用 USER_INDEXES 视图，该视图中显示了一个 distinct_keys 列。比较一下唯一键的数

量和表中的行数，就可以判断索引的选择性。选择性越高，索引返回的数据就越少。

1.11 群集因子(Clustering Factor)

Clustering Factor 位于 USER_INDEXES 视图中。该列反映了数据相对于已建索引的列是

否显得有序。如果 Clustering Factor 列的值接近于索引中的树叶块(leaf block)的数目，表

中的数据就越有序。如果它的值接近于表中的行数，则表中的数据就不是很有序。

1.12 二元高度(Binary height)

索引的二元高度对把 ROWID 返回给用户进程时所要求的 I/O 量起到关键作用。在对一个

索引进行分析后，可以通过查询 DBA_INDEXES 的 B- level 列查看它的二元高度。二元高

度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被

删除的行，它的二元高度也会增加。更新索引列也类似于删除操作，因为它增加了已删除

键的数目。重建索引可能会降低二元高度。

1.13 快速全局扫描

从 Oracle7.3 后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许 Oracle

剩余11页未读，继续阅读

lytall

粉丝: 1
资源: 3

会员权益专享

oracle索引开发指南

评论0

会员权益专享

最新资源

oracle索引开发指南

评论0

118个真实应用场景的Oracle存储过程案例及开发指南(从入门到熟练使用)

Oracle数据库管理员技术指南

Oracle及SQL语句优化指南

oracle四大宝典之4:oracle性能优化.docx

oracle 19c documentation chm

oracle必知必会 pdf

oracle数据库学习官方文档

oracle11g最新手册教程

toad for oracle 使用教程

oracle database 12c性能优化攻略 pdf

oracle database 11g完全参考手册电子版

oracle11g从入门到精通第三版pdf

oraclexuexizhinan

oracle 19c 官方文档中文版

oracle 应急手册

oracle编程艺术++深入理解数据库体系结构word 版

delphi 10.1 firedac 数据库开发手册

oralce数据库学习

ncc开发环境架设教程

odb++ 标准 csdn

会员权益专享

最新资源