This discussion is archived
5 Replies Latest reply: Sep 25, 2013 4:39 AM by odie_63 RSS

[11g] can create xmltype holding "forrest" with xmlagg(), but not with dbms_lob.append()

user8704911 Newbie
Currently Being Moderated

i am testing aggregation of xmltype elements via a) xmlagg() vs. b) dbms_lob.append().

Well it depends on the number of xmltype elements and their structure and size, which is more performant.

But what drives me crazy, is that i am allowed to create some xmltype which is a "forrest" (not a SINGLE root node, but A NUMBER OF root nodes) using xmlagg().
But when i do the aggregation via dbms_lob.append(), i can't create some xmltype from the result, because it holds a "forrest".

So why is it allowed one way, but not allowed the other way?

regards,

Frank

  • 1. Re: [11g] can create xmltype holding "forrest" with xmlagg(), but not with dbms_lob.append()
    BluShadow Guru Moderator
    Currently Being Moderated

    Because that's the way it works.  You wouldn't be able to save your Forest to an XML document and treat it as valid XML because it's not.

    When working with XML, you should always use XMLTYPE and the XML functions, rather than manually stringing things together using DBMS_LOB.

  • 2. Re: [11g] can create xmltype holding "forrest" with xmlagg(), but not with dbms_lob.append()
    user8704911 Newbie
    Currently Being Moderated

    ... unless dealing with performance?

    Here's a log from a demo program (will take some time to adjust it and post it here):

     

    :I:ORA:051104.418:create buffer...

    :I:ORA:051104.425:size = 235533

    :I:ORA:051104.425:create xmldata1...

    :I:ORA:051104.440:create xmldata table...

    :I:ORA:051104.479:xmlagg() aggregation...

    :I:ORA:051136.127:get clob...

    :I:ORA:051202.868:calculate length...

    :I:ORA:051202.869:size = 176135500

    :I:ORA:051202.869:dbms_log.append() aggregation...

    :I:ORA:051217.555:calculate length...

    :I:ORA:051217.555:size = 117766501

    :I:ORA:051217.555:clobs conversion to xmltype...

    :I:ORA:051250.079:... all done

     

    xmlagg() aggregation takes 05:11:04 .. 05:11:36, i.e. around #32 seconds

    dbms_lob.append() aggregation takes 05:12:02 .. 05:12:17, i.e. around #15 seconds.

    (both the same data)

     

    Btw, i could adopt a clob variable holding a forrest into some xmltype this way:

     

    xmldata := xmltype('<root>' || buffer || '</root>').extract('root');

     

    But that took 05:12:17 .. 05:12:50, i.e. around #33 seconds.

     

    P.S. forgot to mention: for both cases the aggregation is for already validated XML data

  • 3. Re: [11g] can create xmltype holding "forrest" with xmlagg(), but not with dbms_lob.append()
    BluShadow Guru Moderator
    Currently Being Moderated

    And the data wasn't cached for the second test with dbms_lob?  (your log shows "dbms_log" for some reason)

    You can repeatedly show the same performance difference over and over?

     

    If you want to provide some demo data and code to demonstrate the performance difference then people may be able to help, but without it, people are just going to guess and then we'll be here for ages (well I won't as I'm off home in a minute).

  • 4. Re: [11g] can create xmltype holding "forrest" with xmlagg(), but not with dbms_lob.append()
    user8704911 Newbie
    Currently Being Moderated

    create type xmltype_tab as table of xmltype;

     

     

    create or replace package test_pkg is

      procedure test;

    end test_pkg;

     

     

    create or replace package body test_pkg is

     

       PROCEDURE log(p_text IN VARCHAR2)

       IS

       BEGIN

          dbms_output.put_line('LOG:' || to_char(systimestamp, 'hhmiss.FF3') || ':' || p_text);

       END;

      

      procedure test

      is

         duplications number := 11;

         ctr number := 2000;

         buffer clob;

         result_data clob;

         size1 number;

         xmldata1 xmltype;

         xmldata2 xmltype;

         xmldatas xmltype_tab := xmltype_tab();

      begin

         log('create clob buffer...');

         buffer := '<entry><level1><level2><level3>hello</level3><level3>hello</level3><level3>hello</level3></level2></level1></entry>';

     

     

         for i in 1..duplications

         loop

             buffer := buffer || buffer;

         end loop;

         buffer := '<root>' || buffer || '</root>';

     

     

         size1 := dbms_lob.getLength(buffer);

         log('size = ' || size1);

        

         log('create xmldata from clob...');

         xmldata1 := xmltype(buffer);

        

         log('create xmldata table...');

         for i in 1..ctr

         loop

             xmldatas.extend;

             xmldatas(xmldatas.last) := xmldata1;

         end loop;

     

     

         -- disable this

         log('dbms_lob.append() aggregation of #' ||ctr || ' instances...');

         buffer := ' ' ;

         for i in 1..ctr

         loop

             dbms_lob.append(buffer, xmldatas(i).getclobval());

         end loop;

     

     

         log('calculate length...');

         size1 := dbms_lob.getLength(buffer);

         log('size = ' || size1);

     

     

    /*

         -- or disable this

         log('xmlagg() aggregation of #' ||ctr || ' instances...');    

         select xmlagg(column_value) into xmldata2 from table(cast (xmldatas as xmltype_tab));

        

         log('get clob...');

         result_data := xmldata2.getClobVal();

        

         log('calculate length...');

         size1 := dbms_lob.getLength(result_data);

         log('size = ' || size1);

      

    /*

         log('clob conversion to xmltype with artificial SINGLE root node and without extract()...');

         xmldata2 := xmltype('<root>' || buffer || '</root>');

     

     

         -- this hits internal storage issues of xmltype

         log('clob conversion to xmltype with artificial SINGLE root node and with extract()...');

         xmldata2 := xmltype('<root>' || buffer || '</root>').extract('root');

    */    

         log('... all done');

      end;

     

     

    end test_pkg;

     

     

    -- run

    alter system flush buffer_cache;

    begin  test_pkg.test; end;

     

     

    -- switch disablements in test_pkg.test

     

     

    -- run again

    alter system flush buffer_cache;

    begin  test_pkg.test; end;

     

     

     

     

    For my system this was 04:29 for xmlagg() vs 03:31 for dbms_lob.append().

    a) xmlagg():

    LOG:075155.143:create clob buffer...

    LOG:075155.150:size = 235533

    LOG:075155.150:create xmldata from clob...

    LOG:075155.166:create xmldata table...

    LOG:075243.739:xmlagg() aggregation of #2000 instances...

    LOG:075712.861:get clob...

    LOG:075846.836:calculate length...

    LOG:075846.836:size = 704542000

    LOG:075846.836:... all done

    b) dbms_lob.append():

    LOG:074038.122:create clob buffer...

    LOG:074038.130:size = 235533

    LOG:074038.130:create xmldata from clob...

    LOG:074038.145:create xmldata table...

    LOG:074128.703:dbms_lob.append() aggregation of #2000 instances...

    LOG:074459.599:calculate length...

    LOG:074459.599:size = 471066001

    LOG:074459.599:... all done

     

     

    But the initial question was, why it's not allowed to create xmltype from clob, if the latter holds A NUMBER OF root nodes (a forrest).

    And let me add:

    ... especially, when it's possible to do it with this workaround:

     

     

    xmldata2 := xmltype('<root>' || buffer || '</root>').extract('root');

  • 5. Re: [11g] can create xmltype holding "forrest" with xmlagg(), but not with dbms_lob.append()
    odie_63 Guru
    Currently Being Moderated

    But the initial question was, why it's not allowed to create xmltype from clob, if the latter holds A NUMBER OF root nodes (a forrest).

    And let me add:

    ... especially, when it's possible to do it with this workaround:

     

     

    xmldata2 := xmltype('<root>' || buffer || '</root>').extract('root');

    BluShadow already answered that part (sort of).

     

    The XMLType constructor builds an XML document, which is, as you know, a single-rooted tree.

    To build a valid XMLType instance out of an XML fragment, you may want to use XMLParse() SQL function with the CONTENT option.

Legend

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