Oracle 19c索引技术 - 墨天轮文档

Oracle 19c索引技术

codedba

443

36页

2次

2022-07-09

5墨值下载

索引

索引基本概念

当访问表中数据时，Oracle 提供了两种方式：从表中读取所有行（即全表扫描），或者

通过 Rowid 一次读取一行。当访问大数据量表中的少量行时，你可能想使用索引。例如如果

只访问大数据量表中的 5%的行，并且可以使用索引标识需要读取的数据库，这样花费的 I/O

较少。如果没有使用索引，就要读取表中所有的数据块。

索引对性能改进的程度取决于数据的选择性以及数据在标的数据块中的分布方式。如果

数据选择性很好，那么表中将只有很少的行匹配一个索引值（例如护照号码）。Oracle 将能

够快速查询索引，找到匹配的 Rowid，从而快速查到表中相应的少量数据块。如果数据选择

性不好（例如国家名），那么对一个值索引为可能返回多个 Rowid，导致从表中查询许多不

连续的数据块。

如果数据库选择性很好，但是相关的行在表中的物理存储位置并不相互靠近，这种情况

下，索引带来的好处就会大打折扣。如果匹配索引值的数据分散在表的多个数据块中，就必

须从表中把每个数据块都选出来以得到需要的查询结果。在一些情况下，你会发现当数据分

散在表的多个数据块时，最好是不使用索引，而是选择全表扫描。执行全表扫描时，Oracle

使用多块读取以快速扫描表。基于索引的读是单块读（基本上），因此在使用索引是的目标

是减少完成查询所需的单块读的数量。

通过对 Oracle 一些可用选项的利用，比如分区、并行 DML 、并行查询以及调整

DB_FILE_MULTIBLOCK_READ_COUNT 进行更大的 I/O 操作，全表扫描和索引查找之间的平和

点正发生着变化。硬件更为快捷，可以在磁盘的高速缓存中缓存更多的信息，内存也变得更

为廉价。与此同时,Oracle 已经增强的索引特性，包括跳跃式扫描索引和其他内部操作作用

来减少返回数据所需要的时间。

注意：当升级 ORACLE 版本时，确保确实应用程序中的查询以确定查询的执行路径是否仍然

使用在升级前使用的索引。看看执行计划是否改变，这种改变的效果是变好还是变差了。

一般来说，索引能提高查询的性能。SELECT 语句、UPDATE 和 DELETE 命令的 WHERE 字

句的性能（当访问的行较少时）可以借助索引获得提高。一般来说，增加索引降低 INSERT

语句的性能（因为需要同时对表和索引进行操作）。索引列上的 UPDATE 操作将会比没有索

引慢很多，因为数据库必须管理对表和索引的改动。因此，大量行的 DELETE 操作将会有表

中存在索引而变慢。

一条删除表中一般数据的 DELETE 语句同时需要删除所有这些行对应的索引（这种情况

是非常耗时的）。通常来说，在表上加一个索引都会使该表上的 INSERT 操作的执行时间变

成原来的三倍。再加一个索引就会在再慢一倍。然而，一个由两列组成的索引并不比只有一

个列的索引差很多。索引列的 UPDATE 和 DELETE 操作同样也会变慢你需要平衡索引带来的

查询性能的提升和对数据修改性能的影响。

通过查询 DBA_INDEXES 视图可以获得一个表上所有的索引的清单，也可以通过访问

USER_INDEXES 视图查询当前方案（schema）的索引。查询 ALL_INDEXES 视图可以查看能够

访问的所有表的索引。

例如，在一张随着 ORACLE 产品一起提供的演示表 emp 上创建两个索引。

create table emp_test as select * from scott.emp;

create index emp_id1 on emp_test(empno, ename,deptno);

create index emp_id2 on emp_test(sal);

当执行这些命令时，数据库将在 emp 表上创建两个单独的索引。每个索引将包含 emp

表中的特定值以及匹配这些值得行的 rowid。如果需要查找 sal 值为 1000 的 emp 表中

的记录，优化器就会使用 emp_id2 索引查找该值，并找到相关的 rowid，接着使用该 rowid

在表中查找对应的行。

使用 USER_INDEXES 显示 emp 表上索引：

select table_name,index_name from user_indexes where table_name=’EMP_TEST’;

输出显示有两个索引，但是没有显示每个索引中包含的列。为了获得当前用户的表中被

索引的列信息，可以查询 USER_IND_COLUMNS 视图，DBA 可以通过访问 DBA_IND_COLUMNS

视图检索所有方案中被索引的列。而通过 ALL_IND_COLUMNS 视图可以查看当前用户有权看

到的所有表的被索引列。

select table_name,index_name,column_name,column_position from user_ind_columns

where table_name='EMP_TEST';

不可视索引

决定对哪些列建立索引往往是很难得。主键是被自动索引的。外检也应该被索引（防止死锁

和性能问题），接下来怎么办？更困难的是决定删除某个可能不好的索引。每插入一条记录

时，所有索引都必须更新。如果一个被索引的列被更新，该索引也要被更新。在不引起用户

查询出现一系列全表扫描或笛卡尔连接的情况下，决定哪些索引需要被删除是很困难的，尤

其是第三方应用程序不可以改变的情况下。不可视索引是该问题的一个可能的解决方案。

ORACLE 允许你关闭索引（使其不可见），但是索引上的维护工作还会继续（任何 DML 操作

（INSERT,DELETE,UPDATE））期间，因为你可能需要快速重新打开它。你可以通过下面命令

使索引可视或者不可视：

1. ALTER INDEX idx1 invisible;

2. alter index idx1 visible;

3. create index ....invisible;

我们尝试在表的列上创建一个不可视索引，然后在查询中，我们看不到那个索引：

SQL> create table dept_test as select * from scott.DEPT;

SQL> create index dept_test_ind_deptno on dept_test(deptno) invisible;

SQL> select count(*) from DEPT_test where deptno=30;

Execution Plan

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

Plan hash value: 2597284190

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

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

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

of 36

5墨值下载

oracle 19c 索引技术

关注

评论