文章/答案/技术大牛

发布

问PL\SQL XMLTable性能
EN

Stack Overflow用户

提问于 2015-11-25 20:22:57

回答 3查看 2.7K关注 0票数 4

我有一个解析XMLTYPE变量的函数，对于每条消息，在XMLTYPE变量中为每个标记附加一个具有特定结构的CLOB。如下所示：

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 ;

如果我有一个小的px_Block，这个功能工作得很好。但是我有一个例子，我可以有一个很大的XMLTYPE，这个函数需要很长时间。我是使用XMLType和XMLTable的新手。我能做些什么来提高性能。也许可以使用批量收集语句？

提前谢谢你，菲利普

EDIT1:这是一个仅针对两个实例的示例。

<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:我对我的函数做了一些修改，改进了20%。我已经改变了使用临时CLOB附加CLOB的方式。但一个更好的解决方案将是有帮助的。

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 ;

oracle

plsql

xmltype

回答 3

Stack Overflow用户

发布于 2015-11-26 00:54:53

XMLTABLE的问题是，Oracle使用DOM Parser读取XML。这意味着必须以大量开销将整个XML加载到内存中。看看DBMS_XMLSTORE吧。此包使用基于事件(或基于流)的SAX Parser。

使用SAX解析器，您可以在普通PC上读取几an大小的XML文件。

然而，在50-100 MByte之前，DOM解析器应该可以很好地工作。

票数 5

Stack Overflow用户

发布于 2015-11-25 22:12:55

因为XML表示一个简单的表结构，所以可以使用dbms_xmlsave将XML数据存储在表中。

首先创建一个与XML数据匹配的表。

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

然后使用dbms_xmlsave将数据存储在表中。

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;

也许这会表现得更好。

票数 2

Stack Overflow用户

发布于 2015-11-26 18:07:44

我已经找到了问题并解决了它。正如@Rene所说，延迟不在XML解析器中，而是在CLOB附加中。

使用'||‘将varchar2追加到CLOB需要很长时间。因此，我需要使用DBMS_LOB.append。但要做到这一点，我需要在CLOB中添加一些内容。我不能追加到空的CLOB。

这就是为什么我要解决这个问题：

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 ;

appendCLOB函数如下所示：

 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;

对于XML中的36k实例，我的函数只需要不到一分钟的时间。感谢所有人！

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/33916475

复制

相似问题

问PL\SQL XMLTable性能
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PL\SQL XMLTable性能EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问PL\SQL XMLTable性能
EN