使用 CTX_REPORT 生成索引信息的 XML 输出

Oracle 9i 引入了 CTX_REPORT 来帮助用户和 Oracle Support 找出有关现有 Oracle Text 索引的信息。

在 Oracle 10g 中，已经通过为其许多过程生成 XML 输出的选项增强了 CTX_REPORT。该功能简化了将从 CTX_REPORT 中获得的信息整合到其他应用程序（如可能是定制的系统监视应用程序）的过程。

本文逐步介绍了生成报表并使用 Oracle 的一些 XML 功能处理 XML 输出的全过程。

本文假定您具备 Oracle Text 和 XML 的基础知识，但几乎对 Oracle 的 XML 功能的毫无了解。希望本文还能充当使用 Oracle 的一些 XML 功能将 XML 转换至 SQL 的入门读物。

强烈建议读者试验所有提供的示例。一般来说，本文中 不会 显示示例的输出，我们假定读者将自己试验这些例子。您将需要一个 Oracle 10g 数据库和一个 SQL*Plus 会话 — 无需额外的软件或产品。

这些示例均假定用户为 TUSER，他具有一个名为 TI 的 Text 索引。您可以通过以 DBA 用户身份运行以下 SQL 来创建合适的测试索引：

                               
create user tuser identified by tuser default tablespace users
temporary tablespace temp;
grant connect,resource,ctxapp to tuser;
connect tuser/tuser
create table testtab(text varchar2(2000));
create index ti on testtab(text) indextype is ctxsys.context;
— 本文其余部分假定您以用户 TUSER 的身份在 SQL*Plus 中运行。

获得总索引大小

要找出索引的大小，我们可以使用函数 CTX_REPORT.INDEX_SIZE。这是返回一个 CLOB 的函数。读取它的最简单方式可能是在 SQL*Plus 中使用 SELECT ...FROM DUAL，如下：

                               
SQL> select ctx_report.index_size('ti') from dual;
很可能我们在这里将只能看到几行。这是因为我们需要使用以下语句让 SQL*Plus 显示更多的数据 
                               
SQL> set long 50000
这次当我们重新运行该 Select 语句时，我们将获得更多的文本，其中最后一行显示为： 
                               
TOTAL BYTES USED:                                311,296 (304.00 KB)
这是我们的键值。但为了在程序中使用这个值，我们必须获得 CTX_REPORT 的输出，分析它以获得最终值，然后将这个值转换成一个正确的数字。这需要大量的工作。

让我们请求一个 XML 报表：

                               
SQL> select ctx_report.index_size('ti', null, 'XML') from dual;
（注意第二个参数 — 分区名称 — 是不需要的，但又必须提供，因为您不能在作为 SELECT 一部分的函数中使用命名参数。）

这将为我们提供许多 XML 输出，包括每一对象的大小。

下面是输出的一部分：

                               
<SIZE_OBJECT_NAME>
TUSER.SYS_IL0000051565C00006$$
</SIZE_OBJECT_NAME>
<SIZE_OBJECT_TYPE>
INDEX (LOB)
</SIZE_OBJECT_TYPE>
<SIZE_TABLE_NAME>
TUSER.DR$TI$I
</SIZE_TABLE_NAME>
<SIZE_TABLESPACE_NAME>
USERS
</SIZE_TABLESPACE_NAME>
<SIZE_BLOCKS_ALLOCATED>
8
</SIZE_BLOCKS_ALLOCATED>
<SIZE_BLOCKS_USED>
4
</SIZE_BLOCKS_USED>
<SIZE_BYTES_ALLOCATED>
65536
</SIZE_BYTES_ALLOCATED>
<SIZE_BYTES_USED>
32768
</SIZE_BYTES_USED>
</SIZE_OBJECT>
<SIZE_OBJECT>
现在暂时让我们只获得有关对象 $I 表的大小信息。我们可以利用一个 XML DB 提取操作符和一个 XPATH 谓词来实现这一操作。

逐步执行该操作：

首先，不直接选择 CLOB，而是将其强制转换成 XMLTYPE 值：

                               
SQL> select xmltype(ctx_report.index_size('ti', null, 'XML')) from dual;
上述操作的输出实际上将和以前的输出一样，这是因为 SQL 只是将 XMLTYPE 转换回 CLOB — 但它为我们的下一个步骤作好了准备：

接下来，我们将使用一个简单的 XPATH 表达式来指定我们想要的结果：

                               
SQL> select extract(xmltype(ctx_report.index_size('ti', null, 'XML')), '
//SIZE_OBJECT') from dual;
我们现在使用 XML DB 的 extract 函数来获得 <SIZE_OBJECT> 标记内的所有 XML。语法 '//SIZE_OBJECT' 是一个 XPATH 表达式，它表示“根元素下任何地方的 SIZE_OBJECT 元素内的所有 XML”。XPATH 的完整说明超出了本文的范围，但我们建议读者阅读 w3Schools 上的教程。

请注意，在输出的每个元素周围都有换行符。这实际上是不正确的 — 这是个缺陷。现在我们通过使用 SQL 的 replace 函数删除所有的换行（字符 10）来修补这个缺陷。XMLType 将自动重新格式化我们的 XML，以获得满意的输出。

                               
select 
extract(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),chr(10),'')), 
'//SIZE_OBJECT')
from dual;
接下来我们只需指定我们只需要 $I 表的信息。我们通过在 xpath 表达式中使用谓词来实现这一点： 
                               
select 
extract(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),chr(10),'')), 
'//SIZE_OBJECT[SIZE_OBJECT_NAME="TUSER.DR$TI$I"]')
from dual;
要使这个阶段更进一步，我们可以通过将以下语句添加到 XPATH 表达式中来获得以字节显示的大小： 
                               
select 
extract(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),chr(10),'')), 
'//SIZE_OBJECT[SIZE_OBJECT_NAME="TUSER.DR$TI$I"]/SIZE_BYTES_USED')
from dual;
— 但我们实际上不想要这些标记 — 只要值。有两种方式可以实现这一点 — 我们可以在 XPATH 中使用 text() 函数，或者我们可以使用 extractValue 而不是 extract。

使用 text()：

                               
select 
extract(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),chr(10),'')), 
'//SIZE_OBJECT[SIZE_OBJECT_NAME="TUSER.DR$TI$I"]/SIZE_BYTES_USED/text()')
as "Table Size"
from dual;

                            
                               
使用 extractValue：

                            
                               
select 
extractValue(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),
chr(10),'')), 
'//SIZE_OBJECT[SIZE_OBJECT_NAME="TUSER.DR$TI$I"]/SIZE_BYTES_USED')
as "Table Size"
from dual;
注意我们在上面两段代码中插入了“Table Size”标签，这是因为列标题的可读性越来越差。

我们现在成功地将单个值从 XML 中提取到了 SQL 环境中。但如果我们想获得所有的大小（可能将它们加在一起来获得总和），那该怎么办？要实现这一点，我们可以使用 XMLSequence — 它返回 XMLType 值的一个集合。然后我们可以在 TABLE 子句中使用这个函数将集合值分为多行。既然我们创建了一个“表”，我们就不再需要引用 DUAL。

仅获得大小：

                               
select *
from table(xmlsequence(
extract(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),chr(10),'')), 
'//SIZE_OBJECT/SIZE_BYTES_USED')));
获得所有对象信息，每行一个对象： 
                               
select * 
from table(xmlsequence(
extract(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),chr(10),'')), 
'//SIZE_OBJECT')));
现在我们将这些信息处理为单个值：

对列格式化，以获得美观的输出

                               
column "Name" format a33
column "Tablespace" format a30
column "Bytes" format a12
执行该 Select，使用 extractValue 获得单个“列” 
                               
select 
extractValue(Column_Value, '/SIZE_OBJECT/SIZE_OBJECT_NAME') as "Name",
extractValue(Column_Value, '/SIZE_OBJECT/SIZE_TABLESPACE_NAME') as "Tablespace",
extractValue(Column_Value, '/SIZE_OBJECT/SIZE_BYTES_USED') as "Bytes"
from table(xmlsequence(
extract(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),chr(10),'')), 
'//SIZE_OBJECT')));
最后，既然我们将这些值转换到了 SQL 环境中，我们就可以在这些值上使用任意的 SQL 操作符了。下面我们收集所有大小的总和，以获得在索引中使用的所有对象的累计总大小。 
                               
select 
sum(extractValue(Column_Value, '/SIZE_OBJECT/SIZE_BYTES_USED')) "Total"
from table(xmlsequence(
extract(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),chr(10),'')), 
'//SIZE_OBJECT')));

INDEX_STATS

大多数 CTX_REPORT 过程有两种形式 — 一种返回 CLOB 的函数（正如我们上面使用的那样）和一种替代的形式（需要您传递一个要填充的 CLOB）。该过程 — INDEX_STATS — 没有函数版本。这是因为它需要执行主索引表的全表扫描，这可能要花一些时间。因此在调用 INDEX_STATS 时，我们需要以稍微有点不同的方法来进行。首先我们将创建一个包含一个 XMLType 列的表。然后我们将调用一个 PL/SQL 块，后者把一个临时的 CLOB 传递给 CTX_REPORT，然后将这个 CLOB 插入到 XMLType 列中。然后我们可以在表上执行我们的各种 XML 操作。 
                               
drop table output;
create table output(report XMLType);

declare
tlob clob;
begin
ctx_report.index_stats(
index_name=>'ti', report=>tlob, 
list_size=>20, report_format=>'XML');
insert into output values (xmlType (replace(tlob,chr(10),'')) );
commit;
dbms_lob.freetemporary(tlob);
end;
/
现在，在包含我们 XML 报表的表 OUTPUT 中只有一行。首先我们将获得估计的行碎片: 
                               
select extractValue(report, 
'//STAT_STATISTIC[@NAME="estimated row fragmentation"]')
as "Fragmentation"
from output;
现在做些更加复杂的事情 — 获得前 3 个使用最频繁的标记。我们可以从 STAT_TOKEN 项中创建一个表，其中 STAT_TOKEN_LIST 有一个属性 NAME 等于“最频繁使用的标记” — 并使用 position 函数只获得前三个标记： 
                               
select extract(value(d), '//STAT_TOKEN_TEXT')
from output, table(xmlsequence(extract(report,
'/CTXREPORT/INDEX_STATS/STAT_TOKEN_STATS/STAT_TOKEN_LIST[@NAME="most frequent 
tokens"]/STAT_TOKEN[position()<4]'))) d;

结论

CTX_REPORT 的 XML 输出模式允许对索引信息进行功能强大的操作。为了充分利用这些功能，一般您需要详细了解 Oracle 数据库的 XML 特性（如 extract、extractValue、XMLSequence 和 XPATH 语法）。鉴于 XML 在数据处理领域的广泛使用，这种了解在将来可能非常有用。