CODEWITHSUNDEEP
oracle-databaseplsqlxmltype
milheiros
milheiros

Reputation: 619

PL\SQL XMLTable performance

I have a function that parses a XMLTYPE variable, and for each message, append a CLOB with a specific structure for each tag in the XMLTYPE variable. Like this:

FUNCTION myFunc (px_Header      IN     VARCHAR2,
                                px_Block       IN     XMLTYPE,
                                pn_numLines      OUT PLS_INTEGER)
      RETURN CLOB
   IS
      lcl_return   CLOB := EMPTY_CLOB;
   BEGIN
      pn_numLines := 0;

      FOR item
         IN (          SELECT RPAD (NVL (RECEIPTNUMBER, ' '), 20) AS RECEIPTNUMBER,
                              RPAD (NVL (COMPANYCODE, ' '), 3) AS COMPANYCODE,
                              RPAD (NVL (BRAND, ' '), 3) AS BRAND,
                              RPAD (NVL (POLICYNUMBER, ' '), 20) AS POLICYNUMBER,
                              RPAD (NVL (CLAIMNUMBER, ' '), 20) AS CLAIMNUMBER,
                              RECEIPTAMOUNT
                                 AS receiptAmount
                         FROM XMLTABLE (
                                 'INT_DATA/Item'
                                 PASSING px_Block
                                 COLUMNS RECEIPTNUMBER   VARCHAR2 (20)
                                                            PATH 'RECEIPTNUMBER',
                                         COMPANYCODE     VARCHAR2 (3)
                                                            PATH 'COMPANYCODE',
                                         BRAND           VARCHAR2 (3) PATH 'BRAND',
                                         POLICYNUMBER    VARCHAR2 (20)
                                                            PATH 'POLICYNUMBER',
                                         CLAIMNUMBER     VARCHAR2 (20)
                                                            PATH 'CLAIMNUMBER',
                                         RECEIPTAMOUNT   VARCHAR2 (15)
                                                            PATH 'RECEIPTAMOUNT'))
      LOOP
         lcl_return:=
               lcl_return
            || px_Header
            || 'B2'
            || item.RECEIPTNUMBER
            || item.COMPANYCODE
            || item.BRAND
            || item.POLICYNUMBER
            || item.CLAIMNUMBER
            || item.RECEIPTAMOUNT
            || CHR (13)
            || CHR (10);
         pn_numLines := pn_numLines + 1;
      END LOOP;

      RETURN lcl_return;
   END myFunc ;

This works well if I have a small px_Block. But I have a case that I can have a big XMLTYPE and this function takes a long time. I'm new using XMLType and XMLTable. Is there something that I can do to improve performance. Maybe using a BULK COLLECT statement?

Thanks in advance, Filipe

EDIT1: Here's an example of the XML only for two instances.

<INT_DATA>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>72972</POLICYNUMBER>
  <CLAIMNUMBER>2015101504</CLAIMNUMBER>
  <RECEIPTAMOUNT>-10.00</RECEIPTAMOUNT>
 </Item>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>73785</POLICYNUMBER>
  <CLAIMNUMBER>2015101505</CLAIMNUMBER>
  <RECEIPTAMOUNT>-22.50</RECEIPTAMOUNT>
 </Item>
</INT_DATA>

EDIT2: I've made some changes in my function with a 20% better improvements. I've changed the way I append the CLOB using a temporary CLOB. But a better solutions will be helpful.

FUNCTION myFunc (px_Header      IN     VARCHAR2,
                                px_Block       IN     XMLTYPE,
                                pn_numLines      OUT PLS_INTEGER)
      RETURN CLOB
   IS
      lcl_return   CLOB := EMPTY_CLOB;
      v_tmp_clob    CLOB := EMPTY_CLOB;
   BEGIN
      pn_numLines := 0;

      FOR item
         IN (          SELECT RPAD (NVL (RECEIPTNUMBER, ' '), 20) AS RECEIPTNUMBER,
                              RPAD (NVL (COMPANYCODE, ' '), 3) AS COMPANYCODE,
                              RPAD (NVL (BRAND, ' '), 3) AS BRAND,
                              RPAD (NVL (POLICYNUMBER, ' '), 20) AS POLICYNUMBER,
                              RPAD (NVL (CLAIMNUMBER, ' '), 20) AS CLAIMNUMBER,
                              RECEIPTAMOUNT
                                 AS receiptAmount
                         FROM XMLTABLE (
                                 'INT_DATA/Item'
                                 PASSING px_Block
                                 COLUMNS RECEIPTNUMBER   VARCHAR2 (20)
                                                            PATH 'RECEIPTNUMBER',
                                         COMPANYCODE     VARCHAR2 (3)
                                                            PATH 'COMPANYCODE',
                                         BRAND           VARCHAR2 (3) PATH 'BRAND',
                                         POLICYNUMBER    VARCHAR2 (20)
                                                            PATH 'POLICYNUMBER',
                                         CLAIMNUMBER     VARCHAR2 (20)
                                                            PATH 'CLAIMNUMBER',
                                         RECEIPTAMOUNT   VARCHAR2 (15)
                                                            PATH 'RECEIPTAMOUNT'))
      LOOP
         v_tmp_clob :=
           TO_CLOB (px_Header)
        || TO_CLOB ('B2')
        || TO_CLOB (item.RECEIPTNUMBER)
        || TO_CLOB (item.COMPANYCODE)
        || TO_CLOB (item.BRAND)
        || TO_CLOB (item.POLICYNUMBER)
        || TO_CLOB (item.CLAIMNUMBER)
        || TO_CLOB (item.RECEIPTAMOUNT)
        || CHR (13)
        || CHR (10);

     lcl_return := lcl_return || v_tmp_clob;

         pn_numLines := pn_numLines + 1;
      END LOOP;

      RETURN lcl_return;
   END myFunc ;

Upvotes: 4

Views: 3310

Answers (3)

milheiros
milheiros

Reputation: 619

I've found the problem and solved it. The latency was not in the XML parser, but in the CLOB append, as @Rene said.

Using '||' to append a varchar2 to a CLOB it takes a long time. So, I need to use DBMS_LOB.append. But to do that I need to something in the CLOB. I cannot append to an empty CLOB.

That why I fix with this:

FUNCTION myFunc (px_Header      IN     VARCHAR2,
                            px_Block       IN     XMLTYPE,
                            pn_numLines      OUT PLS_INTEGER)
  RETURN CLOB
IS
  lcl_return   CLOB;
  v_tmpVarchar   VARCHAR2 (32000);
  lv_line        VARCHAR2 (32000);
BEGIN
  pn_numLines := 0;

  FOR item
     IN (          SELECT RPAD (NVL (RECEIPTNUMBER, ' '), 20) AS RECEIPTNUMBER,
                          RPAD (NVL (COMPANYCODE, ' '), 3) AS COMPANYCODE,
                          RPAD (NVL (BRAND, ' '), 3) AS BRAND,
                          RPAD (NVL (POLICYNUMBER, ' '), 20) AS POLICYNUMBER,
                          RPAD (NVL (CLAIMNUMBER, ' '), 20) AS CLAIMNUMBER,
                          RECEIPTAMOUNT
                             AS receiptAmount
                     FROM XMLTABLE (
                             'INT_DATA/Item'
                             PASSING px_Block
                             COLUMNS RECEIPTNUMBER   VARCHAR2 (20)
                                                        PATH 'RECEIPTNUMBER',
                                     COMPANYCODE     VARCHAR2 (3)
                                                        PATH 'COMPANYCODE',
                                     BRAND           VARCHAR2 (3) PATH 'BRAND',
                                     POLICYNUMBER    VARCHAR2 (20)
                                                        PATH 'POLICYNUMBER',
                                     CLAIMNUMBER     VARCHAR2 (20)
                                                        PATH 'CLAIMNUMBER',
                                     RECEIPTAMOUNT   VARCHAR2 (15)
                                                        PATH 'RECEIPTAMOUNT'))
  LOOP
     lv_line :=
           px_Header
        || 'B2'
        || item.RECEIPTNUMBER
        || item.COMPANYCODE
        || item.BRAND
        || item.POLICYNUMBER
        || item.CLAIMNUMBER
        || item.RECEIPTAMOUNT
        || CHR (13)
        || CHR (10);
     pn_numLines := pn_numLines + 1;

     appendCLOB (lcl_retorno, lv_tmpVarchar, lv_linha);

  END LOOP;

  RETURN lcl_return;
END myFunc ;

And the appendCLOB function like this:

 PROCEDURE appendCLOB (pcl_clob   IN OUT NOCOPY CLOB,
                         pv_vc      IN OUT NOCOPY VARCHAR2,
                         pv_text                  VARCHAR2)
   IS
   BEGIN
      pv_vc := pv_vc || pv_text;
   EXCEPTION
      WHEN VALUE_ERROR
      THEN
         IF pcl_clob IS NULL
         THEN
            -- Add the first varchar
            pcl_clob := pv_vc;
         ELSE
            -- If the clob is not empty, uses the DBMS_LOB.append function
            DBMS_LOB.append (pcl_clob, pv_vc);
            pv_vc := pv_text;
         END IF;
   END;

For 36k instances in the XML, My function takes less than a minute. Thanks to all!

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59486

The problem with XMLTABLE is that Oracle uses a DOM Parser to read the XML. It means the entire XML has to be loaded into memory with a lot of overhead. Have a look at DBMS_XMLSTORE. This package uses a SAX Parser which is event bases (or stream based).

Using a SAX Parser you can read XML files having a size of several Gigabytes with an ordinary PC.

However, until - let's say 50-100 MByte - a DOM Parser should work well.

Upvotes: 5

Rene
Rene

Reputation: 10541

Since your XML represent a simple table structure you could use dbms_xmlsave to store the XML data in a table.

First create a table that matches the XML data.

-- Create table
create table TEST
(
  receiptnumber NUMBER,
  companycode   NUMBER,
  brand         VARCHAR2(10),
  policynumber  NUMBER,
  claimnumber   NUMBER,
  receiptamount NUMBER
)

Then use dbms_xmlsave to store the data in a table.

declare
   l_clob   clob;
   l_rows  number;
   l_insctx dbms_xmlsave.ctxtype;
begin
   l_clob := '<INT_DATA>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>72972</POLICYNUMBER>
  <CLAIMNUMBER>2015101504</CLAIMNUMBER>
  <RECEIPTAMOUNT>-10.00</RECEIPTAMOUNT>
 </Item>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>73785</POLICYNUMBER>
  <CLAIMNUMBER>2015101505</CLAIMNUMBER>
  <RECEIPTAMOUNT>-22.50</RECEIPTAMOUNT>
 </Item>
</INT_DATA>';

   l_insctx := dbms_xmlsave.newcontext('TEST');
   dbms_xmlsave.setrowtag(l_insctx, 'Item');
   l_rows := dbms_xmlsave.insertxml(l_insctx, l_clob);
   dbms_xmlsave.closecontext(l_insctx);
end;

Maybe this performs better.

Upvotes: 2

Related Questions