5 Replies Latest reply: Feb 12, 2013 3:42 PM by User511984-OC RSS

    insert from xmltype to relational table performance

    User511984-OC
      We have a table with two columns

      SQL> desc BASE.INST_GLOSS_DAILY_BAL_XML_TEMP
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      XML_ID NUMBER
      XML_DOC SYS.XMLTYPE

      so the whole XML file is loaded as one record into this table.
      then we run this to insert into the relational table

      INSERT INTO BASE.INST_GLOSS_DAILY_BAL_REL_TEMP
      (LEDGERCODE
      ,PERIOD
      ,CCY
      ,CAT1
      ,DBCCYVALUE
      ,CRCCYVALUE
      ,DBBASEVALUE
      ,CRBASEVALUE
      ,EXTREF
      ,FILE_NAME)
      (
      SELECT
      SUBSTR(extract(value(d), '//@ledgercode').getStringVal(),1,255) AS LEDGERCODE,
      SUBSTR(extract(value(d), '//@period').getStringVal(),1,255) AS PERIOD,
      SUBSTR(extract(value(d), '//@ccy').getStringVal(),1,255) AS CCY,
      SUBSTR(extract(value(d), '//@cat1').getStringVal(),1,255) AS CAT1,
      SUM(extract(value(d), '//@dbccyvalue').getNumberVal()) AS DBCCYVALUE,
      SUM(extract(value(d), '//@crccyvalue').getStringVal()) AS CRCCYVALUE,
      SUM(extract(value(d), '//@dbbasevalue').getStringVal()) AS DBBASEVALUE,
      SUM(extract(value(d), '//@crbasevalue').getStringVal()) AS CRBASEVALUE,
      SUBSTR(extract(value(d), '//ACCEXTREF/@extref').getStringVal(),1,255) AS EXTREF,
      :V_FILE_NAME
      FROM BASE.INST_GLOSS_DAILY_BAL_XML_TEMP X,
      table(xmlsequence(extract(x.XML_DOC, '/JOURNALEXT/JOURNAL'))) d
      WHERE extract(value(d), '//ACCEXTREF/@extref').getStringVal() <> '99999999'
      GROUP BY
      SUBSTR(extract(value(d), '//@ledgercode').getStringVal(),1,255) ,
      SUBSTR(extract(value(d), '//@period').getStringVal(),1,255) ,
      SUBSTR(extract(value(d), '//@ccy').getStringVal(),1,255) ,
      SUBSTR(extract(value(d), '//@cat1').getStringVal(),1,255) ,
      SUBSTR(extract(value(d), '//ACCEXTREF/@extref').getStringVal(),1,255) ,
      :V_FILE_NAME);

      it takes around 6 hrs to insert around 250 records.

      this is a sample of the data

      <?xml version="1.0" encoding="ISO-8859-1"?>
      <JOURNALEXT>
      <JOURNAL journalno="19693058" journalpos="1" ledgercode="TD4" inputdate="2013-01-04" narrative="AQTY DEL Reversal of Journal 19628577 Pos 1" period="201309"
      ccy="AUD" accountcode="87100A03AUDAUD2" trandate="2012-12-30" source="GLAL" reference="M2WY6VPT8EQ1" dbccyvalue="4232.70" crccyvalue="0.00" dbbasevalue="423
      2.70" crbasevalue="0.00" rate="1.00000000" calcmethod="M" >
      <ACCEXTREF extcode="TD4" extref="216269" />
      </JOURNAL>

      <JOURNAL journalno="19693058" journalpos="2" ledgercode="TD4" inputdate="2013-01-04" narrative="AQTY DEL Reversal of Journal 19628577 Pos 2" period="201309"
      ccy="AUD" accountcode="92102000FAIL" cat1="AUE" cat2="NALC0001" trandate="2012-12-30" source="GLAL" reference="M2WY6VPT8EQ1" dbccyvalue="0.00" crccyvalue="4
      232.70" dbbasevalue="0.00" crbasevalue="4232.70" rate="1.00000000" calcmethod="M" >
      <ACCEXTREF extcode="TD4" extref="216258" />
      </JOURNAL>

      <JOURNAL journalno="19693059" journalpos="1" ledgercode="TD4" inputdate="2013-01-04" narrative="SQTY CBUY 6715740 25090.00@ 10.9" period="201310" ccy="AUD" a
      ccountcode="81012000" cat1="AUE" cat2="RRFID1830" trandate="2013-01-04" source="VDAC" reference="M13003F007E8" dbccyvalue="0.00" crccyvalue="273508.35" dbbas
      evalue="0.00" crbasevalue="273508.35" rate="1.00000000" calcmethod="M" >
      <ACCEXTREF extcode="TD4" extref="216238" />
      </JOURNAL>
      </JOURNALEXT>

      appreciate any help.

      we are on
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.
        • 1. Re: insert from xmltype to relational table performance
          Jason_(A_Non)
          Welcome to the forums.

          As a tip for future posts [url https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002]2. How do I ask a question on the forums?

          I am assuming that table BASE.INST_GLOSS_DAILY_BAL_XML_TEMP was created new in the 11.2.0.3 DB. I ask this to verify that the XMLType column is using storage SECUREFILE BINARY XML. This became the default in 11.2.0.2. You should ensure this is the storage, via dbms_metadata.get_ddl() or your favorite method, as this provides improved performance when querying the data.

          As for your SELECT statement itself, [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions060.htm#SQLRF00640]EXTRACT is deprecated in 11.2. The documentation suggests to use XMLQuery, but what you are doing is more [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions253.htm#SQLRF06232]XMLTable based (as I see it).

          A very simplified version of your code is
          SQL> WITH INST_GLOSS_DAILY_BAL_XML_TEMP AS
            2  (SELECT XMLTYPE('<?xml version="1.0" encoding="ISO-8859-1"?>
            3  <JOURNALEXT>
            4  <JOURNAL journalno="19693058" journalpos="1" ledgercode="TD4" inputdate="2013-01-04" narrative="AQTY DEL Reversal of Journal 19628577 Pos 1" period="201309"
            5  ccy="AUD" accountcode="87100A03AUDAUD2" trandate="2012-12-30" source="GLAL" reference="M2WY6VPT8EQ1" dbccyvalue="4232.70" crccyvalue="0.00" dbbasevalue="423
            6  2.70" crbasevalue="0.00" rate="1.00000000" calcmethod="M" >
            7  <ACCEXTREF extcode="TD4" extref="216269" />
            8  </JOURNAL>
            9  
           10  <JOURNAL journalno="19693058" journalpos="2" ledgercode="TD4" inputdate="2013-01-04" narrative="AQTY DEL Reversal of Journal 19628577 Pos 2" period="201309"
           11  ccy="AUD" accountcode="92102000FAIL" cat1="AUE" cat2="NALC0001" trandate="2012-12-30" source="GLAL" reference="M2WY6VPT8EQ1" dbccyvalue="0.00" crccyvalue="4
           12  232.70" dbbasevalue="0.00" crbasevalue="4232.70" rate="1.00000000" calcmethod="M" >
           13  <ACCEXTREF extcode="TD4" extref="216258" />
           14  </JOURNAL>
           15  
           16  <JOURNAL journalno="19693059" journalpos="1" ledgercode="TD4" inputdate="2013-01-04" narrative="SQTY CBUY 6715740 25090.00@ 10.9" period="201310" ccy="AUD"
           17  accountcode="81012000" cat1="AUE" cat2="RRFID1830" trandate="2013-01-04" source="VDAC" reference="M13003F007E8" dbccyvalue="0.00" crccyvalue="273508.35"
           18  dbbasevalue="0.00" crbasevalue="273508.35" rate="1.00000000" calcmethod="M" >
           19  <ACCEXTREF extcode="TD4" extref="216238" />
           20  </JOURNAL>
           21  </JOURNALEXT>') xml_doc
           22    FROM DUAL)
           23  -- Ignore the WITH above as using to simulate your table.
           24  -- You only need the below
           25  SELECT d.ledgercode,
           26         SUM(d.dbccyvalue)
           27  FROM BASE.INST_GLOSS_DAILY_BAL_XML_TEMP X,
           28       XMLTable('JOURNALEXT/JOURNAL'
           29                PASSING x.xml_doc
           30                COLUMNS
           31                LEDGERCODE  VARCHAR2(25)  PATH '@ledgercode',
           32                extref      VARCHAR2(25)   PATH 'ACCEXTREF/@extref',
           33                DBCCYVALUE  INTEGER        PATH '@dbccyvalue') d
           34  WHERE d.extref != '99999999'
           35  GROUP BY d.ledgercode;
           
          LEDGERCODE                SUM(D.DBCCYVALUE)
          ------------------------- -----------------
          TD4                                    4233
          Without knowing how large your XML really is or your environment, you should easily be getting performance in the seconds range.
          • 2. Re: insert from xmltype to relational table performance
            odie_63
            987055 wrote:
            it takes around 6 hrs to insert around 250 records.
            6 hours, really?
            As suggested, try out BINARY XML and XMLTable, you'll have a good surprise.
            • 3. Re: insert from xmltype to relational table performance
              User511984-OC
              Thanks Non. i am not sure if the table was created in 11.2.

              I can create another one if htat would help performance.

              but i need help, can you tell me how to create this table?

              Thanks.
              • 4. Re: insert from xmltype to relational table performance
                odie_63
                987055 wrote:
                I can create another one if htat would help performance.

                but i need help, can you tell me how to create this table?
                Check the STORAGE_TYPE of the XMLType column in USER_XML_TAB_COLS view :
                select storage_type
                from USER_XML_TAB_COLS 
                where table_name = 'INST_GLOSS_DAILY_BAL_XML_TEMP' 
                and column_name = 'XML_DOC' ;
                If it's "BINARY" then it's OK.

                If you get "CLOB", then you have to either :
                - migrate the column to SECUREFILE BINARY XML
                - create another table with an XMLType column, since you're on 11.2.0.3 the default storage will be BINARY.

                Jason (A_Non) covers that in this article : http://anononxml.blogspot.com/2012/06/xmltype-and-dbmsredefinition.html

                Edited by: odie_63 on 11 févr. 2013 21:42
                • 5. Re: insert from xmltype to relational table performance
                  User511984-OC
                  It goes extremely fast now. thanks to both of you