xmltype 长度oracle,oracle – 插入到varchar2列中从xmltype列中选择：极慢

我使用的是oracle10gR2 10.2.0.4和solaris10 64bit

我需要从xmltype列表(word.testmeta)中的xml中选择数据值

并插入另一个表(word.testwordyy)

desc word.testmeta;

Name Null? Type

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

FILENAME CHAR(2000)

XMLDATA XMLTYPE

desc word.testwordyy;

Name Null? Type

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

ID VARCHAR2(255)

KEYWORD VARCHAR2(4000)

我使用xmltable并执行：

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

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

PLAN_TABLE_OUTPUT

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

| 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中的行数越多,每行花费的时间就越多

我的XML简单而小巧,但需要处理大量的XML(5000000)

当行数超过8000时,处理速度非常慢,需要几个小时.

有没有优化或更快的方式？