insert into varchar2 column select from xmltype column: extremely slow

Question

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?

Answer 1

You have defined the KEYWOR column as an XMLTYPE. Why is that? The whole point of XMLTABLE is to convert XML structures into relational columns. If you defined the column as a simple string you could avoid a lot of unnecessary conversions.

"the contents of the tag are more than 4000 characters>>> are there any methods to substring the contents of the tag in the xmltable"

There is the XPath substring function.

insert /*+append */ into word.testwordyy(KEYWORD)
select /*+ gather_plan_statistics */ b.KEWOR
from word.testmeta 
    , xmltable
      (
        '$B/mets/KEWOR'
        passing
        word.testmeta.XMLDATA as B
        columns
        KEWOR varchar2(4000) path 'substring(KEWOR, 254, 4000)'
      ) b

Here I have started the substring with an offset of 254 which you use in your original post. I have also explicitly set its length to 4000.

I don't think you need to explicitly reference the text() node when declaring the column.