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?

Why do you have `word.testmeta` in the `from`? – Mat Oct 19 '14 at 08:49 — Mat, Oct 19 '14 at 08:49
testmeta in the word schema,so word.testmeta – pangjiale Oct 21 '14 at 13:12 — pangjiale, Oct 21 '14 at 13:12

APC · Answer 1 · 2014-10-20T07:15:26.870

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.

edited Oct 20 '14 at 07:15

answered Oct 19 '14 at 08:55

APC

144,005
19
170
281

the contents of the tag are more than 4000 characters,so define the KEYWOR column as an XMLTYPE can void the error : ORA-01706: user function result value was too large. are there any methods to substring the contents of the tag in the xmltable just like this : KEWOR varchar2(4000) path '/KEWOR/text().substr(4000)' – pangjiale Oct 20 '14 at 02:15
i got LPX-00601: Invalid token in: '/*/substring(KEWOR, 254, 4000)' with KEWOR varchar2(4000) path 'substring(KEWOR, 254, 4000)' – pangjiale Oct 20 '14 at 14:25
KEWOR varchar2(4000) path 'substring(KEWOR, 254, 4000)' does not work with the version 10gR2 10.2.0.4 – pangjiale Oct 21 '14 at 03:06

insert into varchar2 column select from xmltype column: extremely slow

1 Answers1