What kind of XML index are you using? Maintaining an XML index has a cost too.
CREATE TABLE xml_data OF XMLType XMLType STORE AS SECUREFILE BINARY XML ;
anuragtandon wrote:Difficult to make sense out of the sample XML. Isn't there any root element?
But in case we can not use the Virtual column how we can delete the dataFor example :
DELETE my_xmltype_table t WHERE XMLExists( '/SectionMain[OID=$myoid]' passing t.object_value , '99dd48cf-fd1b-46cf-9983' as "myoid" ) ;
At present we create 9 views for 9 tables and performance is good except 1 view. That view is taking much more time and yesCould you post view definition and explain plan?
that view have most of the records (around 8000).
My question is, anyway we can do indexing on that collection(<SectionAllCU>) of XML FILE. It is a simple collectionAs said earlier, I'm not sure you'll gain much by indexing this fragment. The cost of maintaining the index (depending on its type, structured?) is probably close to the cost of running the query without index in the first place (assuming a streaming XPath operation is used).
SELECT T.FILE_NM , x.OID , x.MACHINETYPE , x.MACHINENAME , op.OPMODE FROM SIM_XMLFILES T , XMLTABLE ( '/DataCollectorJobID_AutoStrapLog/SectionAllCU' PASSING T.XMLDOC COLUMNS OID VARCHAR2(80) PATH 'OID' , MACHINETYPE VARCHAR2(80) PATH 'MachineType' , MACHINENAME VARCHAR2(80) PATH 'MachineName' , opmodes xmltype PATH 'Opmode' ) X , XMLTABLE ( '/Opmode' PASSING x.opmodes COLUMNS OPMODE VARCHAR2(10) PATH 'InputMode' ) (+) OP ;
Existing code is similar to below
<Node> <SectionMain> <MachineType>CP</MachineType> <MachineName>CP_225</MachineName> </SectionMain> <SectionMain> <MachineType>CP</MachineType> <MachineName>CP_444</MachineName> </SectionMain> <SectionEvent> <SectionOID>99dd48cf-2</SectionOID> <EventName>CP.CP_225.Shredder</EventName> <OID>b3dd48cf-532d-4126-92d2</OID> </SectionEvent> </node>
SELECT x.MACHINETYPE , x.MACHINENAME FROM XMLFILES T , XMLTABLE ( '/Node/SectionMain' PASSING T.XMLDOC COLUMNS MACHINETYPE VARCHAR2(80) PATH 'MachineType' , MACHINENAME VARCHAR2(80) PATH 'MachineName' ) X;