This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Aug 23, 2012 6:08 AM by Ric79 RSS

Referencing a node

Ric79 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks, I was trying to code the third solution, but you were faster than me
  • 5. Re: Referencing a node
    Ric79 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points