1 2 Previous Next 23 Replies Latest reply: Aug 23, 2012 8:08 AM by Ric79 RSS

    Referencing a node

    Ric79
      Hi all,
      let us suppose
      <mi>
       <granularityperiod>900</granularityperiod>
       <col>Col1</col>
       <col>Col2</col>
       <col>Col3</col>
       <mv>
         <moid>Measure1</moid>
         <r>1</r>
         <r>2</r>
         <r>3</r>
       </mv>
       <mv>
         <moid>Measure2</moid>
         <r>4</r>
         <r>5</r>
         <r>6</r>
       </mv>
      </mi>
      The result is
       moid       | Col1 | Col2 | Col3
       Measure1   |  1   |  2   |  3
       Measure2   |  4   |  5   |  6
      In Plsql a want to refer to the first/second/third <r> using the name of the corresponding column given in <col>
        L_NODELIST   := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(L_DOC), '/mi/mv');
        FOR CUR_NODE IN 0 .. DBMS_XMLDOM.GETLENGTH(L_NODELIST) - 1
          L_N                                 := DBMS_XMLDOM.ITEM(L_NODELIST, CUR_NODE);          
          DBMS_XSLPROCESSOR.VALUEOF(L_N, 'r[ ... I want to refer to Col1 to get the value 1 ]/text()', Col1Value); 
          DBMS_XSLPROCESSOR.VALUEOF(L_N, 'r[ ... I want to refer to Col2 to get the value 2 ]/text()', Col2Value); 
          DBMS_XSLPROCESSOR.VALUEOF(L_N, 'r[ ... I want to refer to Col3 to get the value 3 ]/text()', Col3Value); 
          ...
      Any help is appreciated!

      Riccardo
        • 1. Re: Referencing a node
          odie_63
          Hi,

          Is it not the same thing you were asking in your previous thread yesterday?

          What's your database version? (select * from v$version)

          Are you aware of XQuery features in the database?
          For instance, XMLTable function can give you the same set-based output in a single statement, and much more.
          • 2. Re: Referencing a node
            Ric79
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            PL/SQL Release 11.2.0.3.0 - Production
            CORE     11.2.0.3.0     Production
            TNS for Solaris: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production
            I've tried with XMLTABLE but still the same problem
            /* Formatted on 22/08/2012 09:39:33 (QP5 v5.215.12089.38647) */
            SELECT X.*
              FROM XMLTABLE(
                      '/mi/mv'
                      PASSING XMLTYPE(
                                    '<mi>'
                                 || ' <granularityperiod>900</granularityperiod>'
                                 || ' <col>Col1</col>'
                                 || ' <col>Col2</col>'
                                 || ' <col>Col3</col>'
                                 || ' <mv>'
                                 || '   <moid>Measure1</moid>'
                                 || '   <r>1</r>'
                                 || '   <r>2</r>'
                                 || '   <r>3</r>'
                                 || ' </mv>'
                                 || ' <mv>'
                                 || '   <moid>Measure2</moid>'
                                 || '   <r>4</r>'
                                 || '   <r>5</r>'
                                 || '   <r>6</r>'
                                 || ' </mv>'
                                 || '</mi>')
                      COLUMNS SEQ FOR ORDINALITY, 
                      MOID VARCHAR2(1000) PATH 'moid', 
                      COL1 NUMBER PATH 'r[1]', 
                      COL2 NUMBER PATH 'r[2]', 
                      COL3 NUMBER PATH 'r[3]') AS X;
            I don't want to use r[1] but r[.... "Col1"]

            -----
            WITH XMLDATA
                 AS (SELECT XMLTYPE(
                                  '<mi>'
                               || ' <granularityperiod>900</granularityperiod>'
                               || ' <col>Col1</col>'
                               || ' <col>Col2</col>'
                               || ' <col>Col3</col>'
                               || ' <mv>'
                               || '   <moid>Measure1</moid>'
                               || '   <r>1</r>'
                               || '   <r>2</r>'
                               || '   <r>3</r>'
                               || ' </mv>'
                               || ' <mv>'
                               || '   <moid>Measure2</moid>'
                               || '   <r>4</r>'
                               || '   <r>5</r>'
                               || '   <r>6</r>'
                               || ' </mv>'
                               || '</mi>')
                               VAL
                       FROM DUAL)
            SELECT POSITION,
                   EXTRACTVALUE(COLUMN_VALUE, 'mv/moid/text()') MOID,
                   EXTRACTVALUE(COLUMN_VALUE, 'mv/r[' || COL.COL1 || ']') P1,
                   EXTRACTVALUE(COLUMN_VALUE, 'mv/r[' || COL.COL2 || ']') P2,
                   EXTRACTVALUE(COLUMN_VALUE, 'mv/r[' || COL.COL3 || ']') P3
              FROM XMLDATA,
                   XMLTABLE('/mi/mv' PASSING VAL COLUMNS POSITION FOR ORDINALITY) T,
                   (SELECT COL1, COL2, COL3
                      FROM (SELECT MT_POSITION, EXTRACTVALUE(COLUMN_VALUE, '/col/text()') MT_VALUE
                              FROM XMLDATA, XMLTABLE('//col' PASSING VAL COLUMNS MT_POSITION FOR ORDINALITY) C) 
                      PIVOT (
                        SUM(MT_POSITION) FOR MT_VALUE IN (
                           'Col1' COL1, 
                           'Col2' COL2, 
                           'Col3' COL3
                         )
                      )
                    ) COL                       
            Anyone has a better solution?
            Riccardo

            Edited by: Ric79 on Aug 22, 2012 10:23 AM
            • 3. Re: Referencing a node
              odie_63
              Hi Riccardo,

              Yes, PIVOTing is an option.
              Here's another version that doesn't involve dynamic XPath expressions :
              with sample_data as (
                select xmltype('<mi>
               <granularityperiod>900</granularityperiod>
               <col>Col3</col>
               <col>Col2</col>
               <col>Col1</col>
               <mv>
                 <moid>Measure1</moid>
                 <r>1</r>
                 <r>2</r>
                 <r>3</r>
               </mv>
               <mv>
                 <moid>Measure2</moid>
                 <r>4</r>
                 <r>5</r>
                 <r>6</r>
               </mv>
              </mi>') doc
                from dual
              )
              select moid, col1, col2, col3 
              from (
                select x1.column_name, x2.moid, x3.r
                from sample_data t
                   , xmltable(
                       '/mi/col'
                       passing t.doc
                       columns column_name varchar2(30) path '.'
                             , rn          for ordinality
                     ) x1
                   , xmltable(
                       '/mi/mv'
                       passing t.doc
                       columns moid varchar2(30) path 'moid'
                             , rset xmltype      path 'r'
                     ) x2
                   , xmltable(
                       '/r'
                       passing x2.rset
                       columns r    number       path '.'
                             , rn   for ordinality
                     ) x3
                where x3.rn = x1.rn
              ) 
              pivot ( 
                max(r) for column_name in ( 'Col1' as col1
                                          , 'Col2' as col2
                                          , 'Col3' as col3 ) 
              );
              or, by grouping x2 and x3 together :
              select moid, col1, col2, col3 
              from (
                select x1.column_name, x2.moid, x2.r
                from sample_data t
                   , xmltable(
                       '/mi/col'
                       passing t.doc
                       columns column_name varchar2(30) path '.'
                             , rn          for ordinality
                     ) x1
                   , xmltable(
                       'for $i in /mi/mv
                          , $j at $p in $i/r
                        return element e { attribute rn {$p}, $i/moid, $j }'
                       passing t.doc
                       columns moid varchar2(30) path 'moid'
                             , r    number       path 'r'
                             , rn   number       path '@rn'
                     ) x2
                where x2.rn = x1.rn
              ) 
              pivot ( max(r) for column_name in ('Col1' as col1, 'Col2' as col2, 'Col3' as col3) )
              ;
              And a pure XQuery solution :
              select x1.*
              from sample_data t
                 , xmltable(
                     'for $i in $d/mi/mv
                      return element e { 
                        $i/moid
                      , for $j at $p in $i/r 
                        return element col { 
                          attribute name {$d/mi/col[$p]}
                        , $j/text()
                        } 
                      }'
                     passing t.doc as "d"
                     columns moid varchar2(30) path 'moid'
                           , col1 number       path 'col[@name="Col1"]'
                           , col2 number       path 'col[@name="Col2"]'
                           , col3 number       path 'col[@name="Col3"]'
                   ) x1
              ;
              • 4. Re: Referencing a node
                Ric79
                Thanks, I was trying to code the third solution, but you were faster than me
                • 5. Re: Referencing a node
                  Ric79
                  Just another question: the quey is really slow if num_columns or num_elements grow

                  Is there a way to speed up the query?

                  Riccardo
                  • 6. Re: Referencing a node
                    odie_63
                    Where does the input XML reside? XMLType PL/SQL variable?

                    Performance is generally improved by first storing the XML in an XMLType column (binary XML) and performing the subsequent queries from there.
                    • 7. Re: Referencing a node
                      Ric79
                      I have a XML File info filesystem (for now)

                      Here there is my example
                      /* Formatted on 22/08/2012 13:24:39 (QP5 v5.215.12089.38647) */
                      WITH XMLDOC AS (SELECT XMLTYPE(BFILENAME('PNGTOCLOB_CK6EPM1', 'A20120822.1230+0200-1245+0200_GMI05U.xml'), NLS_CHARSET_ID('UTF-8'), SCHEMA => NULL, VALIDATED => 1, WELLFORMED => 1) DOC FROM DUAL)   
                           DECODED_DATA
                           AS (                   SELECT VAL.*
                                                    FROM XMLDOC XD,
                                                         XMLTABLE(XMLNamespaces(DEFAULT 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec'),
                                                            'for $xml_mv_row in $mydoc/measCollecFile/measData/measInfo/measValue[starts-with(@measObjLdn, "ggsnGtpu,ggsnGtpuAddress=")]
                              return element e { 
                                $xml_mv_row/@measObjLdn,
                                for $column_val at $column_id in $xml_mv_row/r 
                                return element xml_column_name { 
                                  attribute column_name {$mydoc/measCollecFile/measData/measInfo[measValue[starts-with(@measObjLdn, "ggsnGtpu,ggsnGtpuAddress=")]]/measType[$column_id]}, 
                                  $column_val/text()
                                } 
                              }'
                                                            PASSING XD.DOC AS "mydoc"
                                                            COLUMNS NATIVE_OBJ_IDT VARCHAR2(1000) PATH 'substring-after(@measObjLdn,"ggsnGtpu,ggsnGtpuAddress=")',
                                                                    PDP_CAPACITY NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuPdpCapacity"]',
                                                                    USER_UPLINK_DROPS NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuUserUplinkDrops"]',
                                                                    USER_DOWNLINK_DROPS NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuUserDownlinkDrops"]',
                                                                    NUM_ACT_PDP_CONTEXTS NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuNbrOfActivePdpContexts"]',
                                                                    MEMORY NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuMemory"]',
                                                                    MEMORY_USED NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuMemoryUsed"]',
                                                                    CPU_USAGE NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuCpuUsage"]',
                                                                    PAYLOAD_LOAD NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuPayloadLoad"]',
                                                                    NUM_ACT_PDP_CONTEXTS_IPV6 NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuNbrOfActivePdpContextsIpv6"]',
                                                                    PEAK_CPU_USAGE NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuPeakCpuUsage"]',
                                                                    UPLINK_PACKETS NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuUplinkPackets"]',
                                                                    DOWNLINK_PACKETS NUMBER PATH 'xml_column_name[@column_name="ggsnGtpuDownlinkPackets"]') VAL)
                      select *
                      from DECODED_DATA;
                      There are cases with +100 columns and +1000 rows.

                      I tried to switch to your first 2 examples, but they don't work

                      Riccardo

                      Edited by: Ric79 on Aug 22, 2012 9:13 PM
                      • 8. Re: Referencing a node
                        odie_63
                        I'd like to run some tests, is the following file consistent with the one you're using?

                        http://www.3gpp.org/ftp/Specs/archive/32_series/32.435/32435-a20.zip 32435-a20.doc
                        <?xml version="1.0" encoding="UTF-8"?>
                        <?xml-stylesheet type="text/xsl" href="MeasDataCollection.xsl"?>
                        <measCollecFile xmlns="http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec"
                        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                        xsi:schemaLocation="http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec">
                             <fileHeader fileFormatVersion="32.435 V7.0" vendorName="Company NN" dnPrefix="DC=a1.companyNN.com,SubNetwork=1,IRPAgent=1">
                                  <fileSender localDn="SubNetwork=CountryNN,MeContext=MEC-Gbg-1,ManagedElement=RNC-Gbg-1" elementType="RNC"/>
                                  <measCollec beginTime="2000-03-01T14:00:00+02:00"/>
                             </fileHeader>
                             <measData>
                                  <managedElement localDn="SubNetwork=CountryNN,MeContext=MEC-Gbg-1,ManagedElement=RNC-Gbg-1" userLabel="RNC Telecomville"/>
                                  <measInfo>
                                       <job jobId="1231"/>
                                       <granPeriod duration="PT900S" endTime="2000-03-01T14:14:30+02:00"/>
                                       <repPeriod duration="PT1800S"/>
                                       <measType p="1">attTCHSeizures</measType>
                                       <measType p="2">succTCHSeizures</measType>
                                       <measType p="3">attImmediateAssignProcs</measType>
                                       <measType p="4">succImmediateAssignProcs</measType>
                                       <measValue measObjLdn="RncFunction=RF-1,UtranCell=Gbg-997">
                                            <r p="1">234</r>
                                            <r p="2">345</r>
                                            <r p="3">567</r>
                                            <r p="4">789</r>
                                       </measValue>
                                       <measValue measObjLdn="RncFunction=RF-1,UtranCell=Gbg-998">
                                            <r p="1">890</r>
                                            <r p="2">901</r>
                                            <r p="3">123</r>
                                            <r p="4">234</r>
                                       </measValue>
                                       <measValue measObjLdn="RncFunction=RF-1,UtranCell=Gbg-999">
                                            <r p="1">456</r>
                                            <r p="2">567</r>
                                            <r p="3">678</r>
                                            <r p="4">789</r>
                                            <suspect>true</suspect>
                                       </measValue>
                                  </measInfo>
                             </measData>
                             <fileFooter>
                                  <measCollec endTime="2000-03-01T14:15:00+02:00"/>
                             </fileFooter>
                        </measCollecFile>
                        • 9. Re: Referencing a node
                          Ric79
                          Yes,
                          they are the standard xml files for umts/lte measurement

                          Be careful because the file contains multiple measinfo (measurement class)

                          Riccardo

                          Edited by: Ric79 on Aug 22, 2012 4:12 PM
                          • 10. Re: Referencing a node
                            odie_63
                            BTW, why not relying on the p attribute to join a <measType> to a corresponding <r> value?

                            Positional predicates are not optimized, using attribute references should be better.
                            • 11. Re: Referencing a node
                              Ric79
                              Don't understand. Could you provide a simple example?

                              Riccardo

                              ----------

                              I followed your tutorial and now I have the XML in an XML Table with indexes
                              CREATE TABLE TEST_XML
                              (
                                 XML_DOC   XMLTYPE
                              )
                              XMLTYPE COLUMN XML_DOC STORE AS OBJECT RELATIONAL XMLSCHEMA "measCollec.xsd" ELEMENT "measCollecFile";
                              
                              
                              create index IX_XML_DOC_measType on TEST_XML ('measType');
                              
                              create unique index IX_XML_DOC_measValue on TEST_XML ('measValue', 'r');
                              
                              
                              INSERT INTO TEST_XML(XML_DOC)
                                   VALUES ('A20120822.1230+0200-1245+0200_GMI05U.xml', XMLTYPE(BFILENAME('-----', 'A20120822.1230+0200-1245+0200_GMI05U.xml'), NLS_CHARSET_ID('UTF-8')).CREATESCHEMABASEDXML('measCollec.xsd'));
                              I have no performance increment...

                              Edited by: Ric79 on Aug 22, 2012 6:08 PM
                              • 12. Re: Referencing a node
                                odie_63
                                I followed your tutorial and now I have the XML in an XML Table with indexes
                                Which tutorial?

                                How did you register the schema?
                                If you want to use OR storage, there are a few things to consider, such as schema annotations.
                                If it's the first time you're trying it, I suggest you read this first : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb05sto.htm#g1070409
                                create index IX_XML_DOC_measType on TEST_XML ('measType');

                                create unique index IX_XML_DOC_measValue on TEST_XML ('measValue', 'r');
                                Those indexes don't do what you think they do... :)
                                You've merely indexed constant strings, not columns.
                                • 13. Re: Referencing a node
                                  Ric79
                                  I followed http://odieweblog.wordpress.com/2011/11/23/oracle-xml-db-a-practical-example/

                                  For the XSD:
                                  1) download the doc from http://www.3gpp.org/ftp/specs/archive/32_series/32.435/32435-700.zip
                                  2) The measCollec.xsd is at 4.2.2 paragraph
                                  3) Modify the xsd according to the last post in Re: Database only likes one flavour of DateTime format for handling correct dates (beginTime, endTime, endTime)


                                  About the index: i followed the tutorial, but I'm starting now to use xmldb...

                                  Riccardo
                                  • 14. Re: Referencing a node
                                    odie_63
                                    Riccardo,

                                    Actually, I would have tried Binary XML storage first :
                                    create table tmp_xml of xmltype;
                                    
                                    insert into tmp_xml values(
                                     xmltype(bfilename('TEST_DIR','measCollecSample.xml'), nls_charset_id('AL32UTF8'))
                                    );
                                    Then a query like this :
                                    with t as (
                                      select x1.column_name, x2.measObjLdn, x3.r
                                      from tmp_xml t
                                         , xmltable(
                                             xmlnamespaces(default 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec')
                                           , '/measCollecFile/measData/measInfo[measValue[starts-with(@measObjLdn, $oid)]]/measType'
                                             passing t.object_value
                                                   , 'RncFunction=RF-1,UtranCell=' as "oid"
                                             columns column_name varchar2(30) path '.'
                                                   , column_id   number       path '@p'
                                           ) x1
                                         , xmltable(
                                             xmlnamespaces(default 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec')
                                           , '/measCollecFile/measData/measInfo/measValue[starts-with(@measObjLdn, $oid)]'
                                             passing t.object_value
                                                   , 'RncFunction=RF-1,UtranCell=' as "oid"
                                             columns measObjLdn varchar2(40) path '@measObjLdn'
                                                   , rset xmltype            path 'r'
                                           ) x2
                                         , xmltable(
                                             xmlnamespaces(default 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec')
                                           , '/r'
                                             passing x2.rset
                                             columns r    number       path '.'
                                                   , p    number       path '@p'
                                           ) x3
                                      where x3.p = x1.column_id
                                    )
                                    select measObjLdn
                                         , max(case when column_name = 'attTCHSeizures' then r end)           as attTCHSeizures
                                         , max(case when column_name = 'succTCHSeizures' then r end)          as succTCHSeizures
                                         , max(case when column_name = 'attImmediateAssignProcs' then r end)  as attImmediateAssignProcs
                                         , max(case when column_name = 'succImmediateAssignProcs' then r end) as succImmediateAssignProcs
                                    from t
                                    group by measObjLdn
                                    ;
                                    I've reverted to the old pivoting technique because it works, contrary to the PIVOT operator which causes internal errors in this situation.

                                    The query has a decent response time : ~10s to fetch 1000 rows.
                                     
                                     

                                    Now my attempt using Object-Relational storage :

                                    I've annotated the schema with xdb:defaultTable and xdb:SQLType (as you mentioned).
                                    begin
                                     
                                      dbms_xmlschema.registerSchema(
                                        schemaURL => 'measCollec.xsd'
                                      , schemaDoc => xmltype(bfilename('TEST_DIR','measCollec.xsd'), nls_charset_id('AL32UTF8'))
                                      , local => true
                                      , genTypes => true
                                      , genTables => true
                                      , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
                                      );
                                      
                                    end;
                                    /
                                    
                                    insert into MEAS_COLLEC_XML values(
                                     xmltype(bfilename('TEST_DIR','measCollecSample.xml'), nls_charset_id('AL32UTF8'))
                                    );
                                    
                                    call dbms_stats.gather_schema_stats(user);
                                    Renaming the nested tables...
                                    begin
                                       dbms_xmlstorage_manage.renameCollectionTable(
                                         tab_name              => 'MEAS_COLLEC_XML'
                                       , xpath                 => '"XMLDATA"."measData"'
                                       , collection_table_name => 'MEAS_DATA_TAB'
                                       );
                                    end;
                                    /
                                    
                                    begin
                                       dbms_xmlstorage_manage.renameCollectionTable(
                                         tab_name              => 'MEAS_DATA_TAB'
                                       , xpath                 => 'measInfo'
                                       , collection_table_name => 'MEAS_INFO_TAB'
                                       );
                                    end;
                                    /
                                    
                                    begin
                                       dbms_xmlstorage_manage.renameCollectionTable(
                                         tab_name              => 'MEAS_INFO_TAB'
                                       , xpath                 => 'measType'
                                       , collection_table_name => 'MEAS_TYPE_TAB'
                                       );
                                    
                                       dbms_xmlstorage_manage.renameCollectionTable(
                                         tab_name              => 'MEAS_INFO_TAB'
                                       , xpath                 => 'measValue'
                                       , collection_table_name => 'MEAS_VALUE_TAB'
                                       );
                                    end;
                                    /
                                    
                                    begin
                                       dbms_xmlstorage_manage.renameCollectionTable(
                                         tab_name              => 'MEAS_VALUE_TAB'
                                       , xpath                 => 'r'
                                       , collection_table_name => 'MEAS_R_TAB'
                                       );
                                    end;
                                    /
                                    Creating an index on "measObjLdn"...
                                    create index meas_value_objldn_idx on meas_value_tab ( "measObjLdn" );
                                    Then this query :
                                    with t as (
                                      select /*+ no_merge */
                                             x1.column_name
                                           , substr(x2.measObjLdn,length('RncFunction=RF-1,UtranCell=')+1) as measObjLdn
                                           , x3.r
                                      from MEAS_COLLEC_XML t
                                         , xmltable(
                                             xmlnamespaces(default 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec')
                                           , '/measCollecFile/measData/measInfo'
                                             passing t.object_value
                                           ) x0
                                         , xmltable(
                                             xmlnamespaces(default 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec')
                                           , '/measInfo/measType'
                                             passing x0.column_value
                                             columns column_name varchar2(30) path '.'
                                                   , column_id   number       path '@p'
                                           ) x1
                                         , xmltable(
                                             xmlnamespaces(default 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec')
                                           , '/measInfo/measValue'
                                             passing x0.column_value
                                             columns measObjLdn varchar2(4000) path '@measObjLdn'
                                                   , rset xmltype              path 'r'
                                           ) x2
                                         , xmltable(
                                             xmlnamespaces(default 'http://www.3gpp.org/ftp/specs/archive/32_series/32.435#measCollec')
                                           , '/r'
                                             passing x2.rset
                                             columns r    number       path '.'
                                                   , p    number       path '@p'
                                           ) x3
                                      where x3.p = x1.column_id
                                      and x2.measObjLdn like 'RncFunction=RF-1,UtranCell=%'         
                                    )
                                    select measObjLdn
                                         , max(case when column_name = 'attTCHSeizures' then r end)           as attTCHSeizures
                                         , max(case when column_name = 'succTCHSeizures' then r end)          as succTCHSeizures
                                         , max(case when column_name = 'attImmediateAssignProcs' then r end)  as attImmediateAssignProcs
                                         , max(case when column_name = 'succImmediateAssignProcs' then r end) as succImmediateAssignProcs
                                    from t
                                    group by measObjLdn
                                    ;
                                    The performance is much better : 1.2 s for 1000 rows.

                                    For this test, I used a sample file with two different <measInfo> having 1000 <measValue> each.
                                    The explain plan looks like this, and shows the index is used as expected :
                                    --------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                                    --------------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT                    |                        |     4 |  8128 |    13  (16)| 00:00:01 |
                                    |   1 |  HASH GROUP BY                      |                        |     4 |  8128 |    13  (16)| 00:00:01 |
                                    |   2 |   VIEW                              |                        |     4 |  8128 |    12   (9)| 00:00:01 |
                                    |*  3 |    HASH JOIN                        |                        |     4 |   944 |    12   (9)| 00:00:01 |
                                    |   4 |     NESTED LOOPS                    |                        |       |       |            |       |
                                    |   5 |      NESTED LOOPS                   |                        |     4 |   784 |     8   (0)| 00:00:01 |
                                    |   6 |       NESTED LOOPS                  |                        |     1 |   172 |     5   (0)| 00:00:01 |
                                    |   7 |        NESTED LOOPS                 |                        |     1 |   155 |     5   (0)| 00:00:01 |
                                    |   8 |         NESTED LOOPS                |                        |     1 |   121 |     4   (0)| 00:00:01 |
                                    |   9 |          TABLE ACCESS BY INDEX ROWID| MEAS_VALUE_TAB         |     1 |    70 |     3   (0)| 00:00:01 |
                                    |* 10 |           INDEX RANGE SCAN          | MEAS_VALUE_OBJLDN_IDX  |     1 |       |     2   (0)| 00:00:01 |
                                    |  11 |          TABLE ACCESS BY INDEX ROWID| MEAS_INFO_TAB          |     1 |    51 |     1   (0)| 00:00:01 |
                                    |* 12 |           INDEX UNIQUE SCAN         | MEAS_VALUE_TAB_MEMBERS |     1 |       |     0   (0)| 00:00:01 |
                                    |  13 |         TABLE ACCESS BY INDEX ROWID | MEAS_DATA_TAB          |     1 |    34 |     1   (0)| 00:00:01 |
                                    |* 14 |          INDEX UNIQUE SCAN          | MEAS_INFO_TAB_MEMBERS  |     1 |       |     0   (0)| 00:00:01 |
                                    |* 15 |        INDEX UNIQUE SCAN            | MEAS_DATA_TAB_MEMBERS  |     1 |    17 |     0   (0)| 00:00:01 |
                                    |* 16 |       INDEX RANGE SCAN              | SYS_C007797            |     4 |       |     1   (0)| 00:00:01 |
                                    |  17 |      TABLE ACCESS BY INDEX ROWID    | MEAS_R_TAB             |     4 |    96 |     3   (0)| 00:00:01 |
                                    |  18 |     TABLE ACCESS FULL               | MEAS_TYPE_TAB          |     8 |   320 |     3   (0)| 00:00:01 |
                                    --------------------------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       3 - access("SYS_ALIAS_4"."p"="SYS_ALIAS_3"."p" AND
                                                  "NESTED_TABLE_ID"="SYS_ALIAS_1"."SYS_NC0001700018$")
                                      10 - access("SYS_ALIAS_2"."measObjLdn" LIKE 'RncFunction=RF-1,UtranCell=%')
                                           filter("SYS_ALIAS_2"."measObjLdn" LIKE 'RncFunction=RF-1,UtranCell=%')
                                      12 - access("NESTED_TABLE_ID"="SYS_ALIAS_1"."SYS_NC0001900020$")
                                      14 - access("NESTED_TABLE_ID"="SYS_ALIAS_0"."SYS_NC0001000011$")
                                      15 - access("NESTED_TABLE_ID"="T"."SYS_NC0001700018$")
                                      16 - access("NESTED_TABLE_ID"="SYS_ALIAS_2"."SYS_NC0000700008$")
                                    1 2 Previous Next