Oracle9i中整理索引

建立索引时需要考虑的因素
索引通常能改善应用程序的性能，因为它能减少应用程序各个单独的SQL语句执行时所牵涉到的I／O量。然而，对应用程序表进行过多的索引，也会对性能产生一些不利的影响。如果不小心对一个小表作了索引，实际上可能增大访问表数据所需的I／O量。在用户对应用程序的数据执行“数据操纵语言（Data Manipulation Language，DML）”操作时，一个不必要的索引还会带来额外的开销，这是由于DML操作不仅会改变一个索引的表中的数据，还会改变与之关联的索引项。除此之外，像数据仓库和数据集市这样的大型数据库环境需要大量的磁盘空间来保存其索引。

评估您的索引策略
那么，怎样才能知道自己采用的是一种有效的策略，可以恰当地使用索引，而不至于带来不必要的开销呢？Oracle9i Database引入了一个新的名为V$OBJECT_ USAGE的数据字典视图和一个新的名为DBMS _METADATA的PL/SQL包，它们可帮助您标识和管理对一个应用程序来说非常关键的索引，以及那些可能不需要的索引。
利用这些工具，首先标识出应用程序的SQL语句访问和没有访问的是哪些索引。然后，为任何未用的索引捕捉DDL，最终将未用的索引删除。完整的操作步骤如下：
1. 针对您希望检查的索引，启用监视功能，并允许用户访问应用程序，执行标准的、典型的操作。
2. 查询V$OBJECT_USAGE视图以确认正在对状态进行监视。
3. 检查V$OBJECT_USAGE视图的内容。
4. 针对您正在检查的索引，关闭对它的监视。
5. 针对监视期间任何应用程序没有使用的索引，捕捉数据定义语言（DDL）。
6. 删除未用的索引。
在后续的小节中，我们将讨论这些步骤，并举一些实际的例子，它们利用了Oracle9i Database随附的新的Order Entry（OE）示例模式的表和索引。用于构建这些段的脚本位于 $ORACLE_HOME/ demo/schema/order_entry/oe_main.sql 中。
1.启用索引监视功能。假定您想了解应用程序是否正在使用PRODUCT_DESCRIPTIONS表的TRANSLATED_NAME列的PROD_NAME_IX索引，为了开始对它的使用情况进行跟踪，您首先必须激活对该索引的监视功能，这是用以下的SQL命令来实现的：
SQL> alter index PROD_NAME_IX monitoring usage;
在大多数情况下，您会同时对多个索引进行监视，以判断哪些索引正在使用，哪些索引应当删除。清单1中显示的脚本可激活对一个特定模式中的所有索引的监视功能。
在您启用了监视功能后，可进行一段时间的普通应用程序处理。至于持续多长，完全取决于您的具体情况。
2. 确认监视状态。 要想确认是否已激活对一个索引的监视，可查询V$OBJECT_USAGE视图。清单2给出了针对PROD_NAME_IX索引的一次V$OBJECT_USAGE查询及其输出的实例。
表1对视图的内容进行了描述。注意对于V$OBJECT _USAGE的内容来说，只有对正在监视的索引拥有所有权的那个模式才能看见。因此，假如您对OE模式中的索引启用了监视功能，同时又以用户SYSTEM的身份登录，那么在您以SYSTEM身份登录期间，后面针对V$OBJECT _USAGE的任何查询都不会返回数据。由于PROD_NAME_IX由用户OE所有，所以您必须以用户OE的身份登录到SQL*Plus，才能看到包含在PROD_ NAME_IX索引视图中的数据。
3. 检查V$OBJECT _USAGE的内容。您可检查V$OBJECT_USAGE，以确定自从开始监视以来，应用程序访问过哪些索引。清单3显示了针对OE模式的几个索引的V$OBJECT_USAGE查询输出结果。
USED列中的NO值表明自开始监视以来，应用程序既没有访问PROD_NAME_IX索引，也没有访问ITEM_ORDER_IX索引。这样的信息有助于您找出那些应考虑删除的无用索引。相反，针对其余索引，V$OBJECT_USAGE的USED列中的YES值表明在监视期间，它们至少被访问过一次。
最终决定删除哪些索引之前，请考虑一下您的监视期限是否准确地反映了典型的用户活动。如答案是否定的，那么您删除的索引在其他情况下也许需要使用。为避免删除一个本来需要的索引，请在您最终决定删除一个未用的索引之前，在几个不同的时段对索引的使用情况进行监视。您监视的时段中还应包括非用户活动访问索引的情况（比如通过批处理），从而对应用程序中的索引使用情况有一个全盘的把握。
同样重要的是，您需要在应用程序的总体事务周期的框架之中来考虑监视结果。通过在几周、几个月或者几个季度之中，对几个特殊时间段索引的使用情况进行监视，可将删除重要索引的机率减少到最低限度，这些重要的索引也许会在一个月（或者一年）结束时才使用，而在您的监视期间却没有使用。
此外，假如您使用基于成本的优化工具，那么在监视会话开始之前，请务必保证与监视的索引相关的表和索引统计是最新的。这是非常重要的一点，因为假如基于成本的优化工具没有获得与一个重要索引及其基础数据表有关的准确信息，就可能不会使用该索引，最终导致您将这个重要索引删除。
要想万无一失地删除一个索引，一个办法是在监视期间，比较索引的访问模式与索引的基础数据表的访问模式。如果应用程序多次访问基础的数据表，但不访问它的相关索引，您便能有把握地假定这个索引很可能是不需要的。然而，由于ALTER INDEX . . . MONITORING USAGE命令只能用来监视索引，所以您还必须采用其他机制，以便在监视索引期间，监视应用程序访问数据表的频率。解决此问题的一个办法是使用Oracle数据库内置的审计特性，来了解与这些未用索引相关的数据表的访问频率有多大。
4. 关闭索引监视功能。一旦收集好索引使用统计结果，可使用nomonitoring选项禁止索引监视：
SQL> alter index PROD_NAME_IX nomonitoring usage;
在清单4中，显示了对PROD_NAME_IX索引禁止监视后，对V$OBJECT_USAGE进行查询的输出结果实例。其中，END_MONITORIN列显示了监视时段的结束日期和时间。在下一次激活对PROD_NAME_IX的监视之前，这个索引监视时段的信息将不会从该视图中清除。同大多数V$表中的统计数据不同，即使一个数据库关闭和重新启动之后，V$OBJECT_USAGE中的统计数据仍会保留下来。
5. 为任何未用的索引捕捉DDL。如决定删除一个未用的索引，那么您可能还希望捕捉索引的定义，它采取的是一个SQL DDL语句的形式--以便未来能将索引还原。为了能快速捕捉DDL，您可使用新的Oracle DBMS_METADATA包，它是在数据库创建之后、并在CATPROC.SQL脚本的执行期间构建的。届时，针对DBMS_METADATA包，会向PUBLIC自动授予执行权限。在这个包中，共有11个子程序，可用它们为任何模式对象生成DDL命令--要么采用XML格式，要么采用标准的SQL DDL格式。Oracle还提供了Metadata API，凭借它可从您的自定义应用程序中访问元数据。
在这个例子中，您使用DBMS_METADATA中带有INDEX参数的GET_DDL函数，从而为希望删除的索引生成完整的DDL。在清单5中，展示了一个示例脚本，它用于为V$OBJECT_USAGE中记录的所有未用索引捕捉DDL。
在清单6中，展示了清单5的脚本针对本例的两个未用索引而生成的输出。DBMS_METADATA包会在由实例的init.ora文件的UTL_FILE_DIR参数指定的目录中创建输出文件。
6. 删除未用的索引。一旦您保存好以后用于重建索引的DDL（如果有必要的话），可用drop index命令来删除未用的索引。使用以下命令，可删除在我们的例子中两个未用的索引：
SQL> drop index PROD_NAME_IX;Index dropped.SQL> drop index ITEM_ORDER_IX;Index dropped.
总结
通过恰当地使用索引，可显著提升Oracle应用程序的性能。然而，您的应用程序完全有可能根本未用到数据库中可用的全部索引。通过配合使用Oracle9i Database的ALTER INDEX . . . MONITORING USAGE命令，以及新的V$OBJECT_USAGE数据字典视图，您可监视应用程序对索引的使用情况，从而删除不需要的索引。在删除一个未用索引之前，您还可以使用DBMS_METADATA包捕捉它的DDL。