构筑高速运行的SQL语句(二)

摘自《成功之路：Oracle 11g学习笔记》

17.3  SQL语句调整

SQL语句调整是本章的重点，也是数据库性能调整中技巧性最强的部分。SQL语句调整针对的是特定的SQL语句，而不是整个数据库。在介绍SQL语句调整之前，我们先介绍索引。

 17.3.1  数据查询的加速器——索引

本节将要介绍各种类型的索引。需要记住的是，索引是一把双刃剑！

1．索引简介

索引是一种与表相关的、可选的结构，用于加速对表的访问。当用户访问表中的数据时，Oracle从索引中找到数据的指针，然后根据指针指向的位置从表中直接取得数据。索引可以减少磁盘I/O，加快数据访问速度。

索引由Oracle自动维护，无须用户的干预。索引也是模式对象。

& 唯一性索引

索引有唯一性索引（Unique Indexes）和非唯一性索引（Nonunique Indexes），唯一性索引能够确保索引列不会出现重复值。

& 复合索引

通常索引都是由一列组成，而复合索引由多个索引列组成。

& 索引的分类

Ø 二叉树索引或者叫B树索引（B-tree Indexes），B树索引是使用得最多的一种索引。在默认情况下，我们创建的索引都是B树索引。B树索引基于二叉树原理，有关二叉树的原理，请大家参考与数据结构相关的书籍。

Ø 二叉树聚簇索引（B-tree Cluster Indexes），主要用于聚簇。

Ø 哈希聚簇索引（Hash Cluster Indexes），主要用于哈希（Hash）聚簇。

Ø 反向索引（Reverse Key Indexes），反向索引也属于B树索引，它把索引值按字节反转过来。

Ø 位图索引（Bitmap Indexes），指通过位图对索引进行管理，位图索引适合唯一值很少的列，也就是重复值很多的列。

Ø 位图连接索引（Bitmap Join Indexes），用于两个表的连接。

Ø 基于函数的索引（Function-Based Indexes），如果在SQL语句的WHERE子句中经常用到函数或者表达式，则可以创建基于函数的索引。

2．创建索引

¢ 创建二叉树索引（普通索引）

CREATE INDEX ind_name4  ON ct(name);

在表ct的列name上创建索引ind_name4。

¢ 创建唯一性索引

CREATE UNIQUE INDEX ind_name

ON ct(name)

TABLESPACE BIGTBS_01;

¢ 创建位图索引

CREATE BITMAP INDEX ind_name5  ON ct2(name);

¢ 创建反向索引

CREATE INDEX ind_name3 ON ct(name) REVERSE;

¢ 创建函数索引

CREATE INDEX ind_name2 ON ct(UPPER(name));

3．把索引移动到另外一个表空间

由于空间的调整，需要把索引从一个表空间移动到另外一个表空间，Oracle的ALTER INDEX ... REBUILD TABLESPACE…命令提供了索引移动的功能。

示例：移动索引INDEX_PS。

 移动索引以前，索引INDEX_PS在表空间SYSTEM中。

SQL> SELECT index_name,tablespace_name FROM dba_indexes WHERE index_name= 'INDEX_PS';

INDEX_NAME                             TABLESPACE_NAME

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

INDEX_PS                           SYSTEM

索引INDEX_PS存在于表空间SYSTEM中。

 执行下面的命令，把索引从表空间SYSTEM移动到另外一个表空间product。

ALTER INDEX INDEX_PS REBUILD TABLESPACE product;

把索引INDEX_PS移动到product表空间。

 确认索引是否已经成功移动到另外一个表空间。

SQL> SELECT index_name,tablespace_name FROM dba_indexes WHERE index_name= 'INDEX_PS';

SQL语句的执行结果是：

INDEX_NAME                         TABLESPACE_NAME

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

INDEX_PS                       PRODUCT

由此可以看出，索引INDEX_PS已经成功地从表空间SYSTEM移动到表空间PRODUCT。

4．为何要重建索引

如果索引因为某些原因无效或者因为很长时间没有维护而产生过多的索引碎片（Index Fragment），需要通过重建索引来消除索引碎片。何时需要重建索引，可以利用下面的过程进行判断。

 查询数据库中有哪些索引。

SQL>SELECT OWNER,INDEX_NAME FROM DBA_INDEXES;

SQL语句的执行结果是：

OWNER                          INDEX_NAME

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

ITME                           PK1

ITME                           INDF5

ITME                           INDF4

ITME                           KK

ITME                           KT2

ITME                           PK11

…

本文将以索引INDF5为例。

 对索引INDF5进行分析。

SQL> ANALYZE INDEX INDF5 VALIDATE STRUCTURE;

Index analyzed

 从视图INDEX_STATS中获得索引INDF5的统计信息。

SQL>SELECT HEIGHT,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 FROM INDEX_STATS WHERE NAME='INDF5';

SQL语句的执行结果是：

 HEIGHT (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*

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

         1   23.1

其中，字典INDEX_STATS表示存放索引的统计信息；列DEL_LF_ROWS表示索引删除行数；列LF_ROWS表示索引总行数；列HEIGHT表示二叉树中从根块到叶块的层次（深度）。

如果满足下面其中一个条件，则要考虑重建索引：

Ø  (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100的值大于20

Ø  HEIGHT的值大于4（说明二叉树的层次太多）

 使用ALTER INDEX ... REBUILD命令重建索引INDF5。

SQL> ALTER INDEX INDF5 REBUILD;

5．消除索引的副作用——删除多余的索引

如果表上存在过多的索引，可能会降低DML语句的执行速度，需要使用DROP INDEX命令删除索引。用户可以删除自己创建的索引。如果要删除其他用户的索引，必须具有DROP ANY INDEX系统权限。

删除索引ind_name3。

SQL> DROP INDEX ind_name3;

6．查看索引信息

数据字典dba_indexes用于列出数据库中索引的信息，从dba_indexes中，可以知道数据库中有哪些索引、每个索引的名字、索引的类型、索引对应的基表等信息。

示例：查询数据库中有哪些索引。

SQL> SELECT index_name,index_type,table_name

FROM dba_indexes;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13804621/viewspace-683179/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13804621/viewspace-683179/