updateXML, inserChildXML and deleteXML very slow on 10M XML file
655030Aug 14 2008 — edited Aug 19 2008I register a schema on a XML DB from a 10Mega file XML.
I need to update/inert or delete fragments on this schema.
The commands are very slow.
Here are my SQL codes:
SQL> update ThomsonOMABCAST
set OBJECT_VALUE=updateXML(OBJECT_VALUE, '/SGMain/SG/ContentTable/Content[@id="bcast://thomson-nis.com/content/6080439184808353411"]/Description/text()','OK OK OK OK')
1 row updated.
Elapsed: 00:00:32.01
SQL> update ThomsonOMABCAST
set object_value = insertChildXML
(OBJECT_VALUE, '/SGMain/SG/ContentTable', 'Content',
XMLTYPE(
' <Content id="1111" version="1" validFrom="3399107277" validTo="3714726477">
<ServiceReference idRef="bcast://service/1111" weight="65535"/>
<Name>Test</Name>
<Description>Test</Description>
<StartTime>2008-06-25T18:32:36+02:00</StartTime>
<EndTime>2008-06-25T19:03:34+02:00</EndTime>
</Content>'));
1 row updated.
Elapsed: 00:00:31.50
SQL> update ThomsonOMABCAST
set object_value = deleteXML(
OBJECT_VALUE,
'/SGMain/SG/ContentTable/Content[@id="1111"]')
1 row updated.
Elapsed: 00:00:32.58
I saw that indexes are automatically created during XML registering schema.
Explain plan shows table ThomsonOMABCAST access full.
Do you think I need to add new indexes? If so, how create them ? on the varray created tables? or on XMLType data?
Thank you for your answer.
AL