3 Replies Latest reply: Nov 17, 2012 4:12 AM by odie_63 RSS

    performance issue with EXTRACTVALUE function.

    RamaKrishna.CH
      Hi all,
      I am getting the XML string length approximately 20 billion .I have to extract some values from that XML initially. But when i tried like below, it takes approximately 10 mins. So can any one help me to extract more faster.
      My DB version:
        BANNER                                                         
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi 
      PL/SQL Release 10.1.0.5.0 - Production                           
      CORE     10.1.0.5.0     Production                                         
      TNS for HPUX: Version 10.1.0.5.0 - Production                    
      NLSRTL Version 10.1.0.5.0 - Production
      WITH t as ( select EXTRACT(XMLTYPE('<InputParameters><ClientName>XXX</ClientName><HpUserId>2500</HpUserId><UserName>ABC</UserName><UserPass></UserPass>
      <ParamSet>
      <alerttype>1000</alerttype>
      <emailtext><![CDATA[test]]></emailtext>
      <smstext><![CDATA[]]></smstext>
      <pushtext><![CDATA[]]></pushtext>
      <voicetext><![CDATA[]]></voicetext>
      <emailmsgsub><![CDATA[test]]></emailmsgsub>
      <smsmsgsub><![CDATA[]]></smsmsgsub>
      <pushmsgsub><![CDATA[]]></pushmsgsub>
      <voicemsgsub><![CDATA[]]></voicemsgsub>
      <flag><![CDATA[N]]></flag>
      <listitems>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
      </listitems>
      </ParamSet></InputParameters>'),'//msgdata') input from dual)
       
      select   EXTRACTVALUE(input,'//emailmsgsub')
               ,EXTRACTVALUE(input,'//smsmsgsub')
               ,EXTRACTVALUE(input,'//pushmsgsub')
               ,EXTRACTVALUE(input,'//voicemsgsub')
               ,EXTRACTVALUE(input,'//alerttype')
               ,EXTRACTVALUE(input,'//flag') from t;
      Thanks,Ram.