Вставить в столбец varchar2 выбрать из столбца xmltype: очень медленно

Question

Я использую oracle10gR2 10.2.0.4 и solaris10 64bit

Мне нужно выбрать значение данных из xml в таблице столбцов xmltype (word.testmeta) и вставить в другую таблицу (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 простой и маленький, но мне нужно обработать их огромное количество (5000000), и обработка очень и очень медленная, когда строк больше 8000, и это займет несколько часов. Есть ли оптимизация или более быстрый способ?

Answer 1

Вы определили столбец KEYWOR как XMLTYPE. Это почему? Весь смысл XMLTABLE заключается в преобразовании XML-структур в реляционные столбцы. Если вы определили столбец как простую строку, вы можете избежать множества ненужных преобразований.

"Содержимое тега содержит более 4000 символов. >>> Есть ли методы для подстановки содержимого тега в xmltable?"

Есть XPath substring функция.

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

Здесь я начал подстроку со смещением 254, которое вы используете в своем исходном посте. Я также явно установил его длину в 4000.

Я не думаю, что вам нужно явно ссылаться на text() узел при объявлении столбца.