I am using oracle10gR2 10.2.0.4 and solaris10 64bit
I need to select data value from an xml in a xmltype column table (word.testmeta) and insert into another table (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)
and I use xmltable and execute:
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
Here is the explain plan 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.
and the more the number of rows in table word.testmeta, the more time spent per row
My XML is simple and small but need to process a huge quantity of them (5000000) and the processing is very very slow when the rows more than 8000 and it will take several hours. Is there any optimization or faster way?
word.testmeta
in thefrom
?