Oracle中从XMLType列提取数据并插入到VARCHAR2列：性能优化

在 Oracle 10gR2 (10.2.0.4) 和 Solaris 10 64-bit 环境下，我需要从一个包含 XML 数据的表（word.testmeta）中提取特定的数据值，并将其插入到另一个表（word.testwordyy）中。

以下是两个表的结构：

word.testmeta 表结构：

NAME         NULL?    TYPE
--------------------------------------
FILENAME     CHAR(2000)
XMLDATA      XMLTYPE

word.testwordyy 表结构：

NAME         NULL?    TYPE
---------------------------------------
ID           VARCHAR2(255)
KEYWORD      VARCHAR2(4000)

为了实现这一目标，我使用了 XMLTABLE 函数，并执行了以下 SQL 语句：

INSERT /*+ APPEND */ INTO word.testwordyy (KEYWORD)
SELECT /*+ GATHER_PLAN_STATISTICS */ DBMS_LOB.SUBSTR(XMLTYPE.GETCLOBVAL(b.KEWOR), 254)
FROM word.testmeta, XMLTABLE (
    '$B/mets/KEWOR'
    PASSING word.testmeta.XMLDATA AS B
    COLUMNS KEWOR XMLTYPE PATH '/KEWOR/text()'
) b;

通过执行以下命令获取执行计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'IOSTATS LAST'));

执行计划如下：

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 37ua3npnxx8su, child number 0
-------------------------------------
insert /*+ APPEND */ into word.testwordyy(KEYWORD) select /*+ GATHER_PLAN_STATISTICS */
dbms_lob.substr(xmltype.getclobval(b.KEWOR),254) from word.testmeta , xmltable ( '$B/mets/KEWOR' passing
> word.testmeta.XMLDATA as B columns KEWOR xmltype path '/KEWOR/text()' ) b
Plan hash value: 875848213
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Starts | E-Rows | A-Rows | A-Time   | Buffers | Reads | Writes |
-----------------------------------------------------------------------------------------------------------------------------------
|  1  | LOAD AS SELECT                     |                    |      1 |        |      1 | 00:10:32.72 |   16832 |     7 |    90 |
|  2  | NESTED LOOPS                       |                    |      1 |  29M   |  34688 | 00:00:25.95 |   12639 |     5 |     0 |
|  3  | TABLE ACCESS FULL                  | TESTMETA           |      1 |   3638 |   3999 | 00:00:00.08 |     909 |     0 |     0 |
|  4  | COLLECTION ITERATOR PICKLER FETCH  | XMLSEQUENCEFROMXMLTYPE |   3999 |        |  34688 | 00:00:24.50 |   11730 |     5 |     0 |
Note
-----
- dynamic sampling used for this statement
21 rows selected.

随着表 word.testmeta 中行数的增加，每行的处理时间也在增加。特别是当行数超过 8000 时，处理速度变得非常慢，需要几个小时才能完成。

虽然我的 XML 数据简单且体积较小，但需要处理大量的 XML 文件（约 500 万个）。因此，寻找一种优化或更快的处理方法显得尤为重要。

以下是一些可能的优化建议：

• 索引优化： 在 word.testmeta 表上创建适当的索引，以提高查询性能。
• 批处理插入： 使用批量插入而不是单行插入，以减少 I/O 操作。
• 并行处理： 利用 Oracle 的并行处理功能，将任务分解为多个并行进程，以加速处理速度。
• 优化 XML 解析： 尝试使用更高效的 XML 解析方法，例如使用 PL/SQL 函数来解析 XML 数据。

希望这些建议能帮助您解决性能问题。