1 Reply Latest reply: Apr 9, 2014 3:28 AM by odie_63 RSS

    Performance over binary XML

    2650484

      Please provide your view based on the scenario below (Oracle 11gR2)

       

      CREATE TABLE Table_XML

      (

      RULESETKEY INTEGER,

      ACCESSRIGHTSKEY NUMBER NOT NULL,

      TARGETTYPE VARCHAR2(255 BYTE) NOT NULL,

      EXECUTIONCONTEXT VARCHAR2(255 BYTE),

      SERIALIZEDRULESET SYS.XMLTYPE

      )

      XMLTYPE SERIALIZEDRULESET STORE AS BINARY XML;

       

      CREATE TABLE Table_XMLCLOB

      (

      RULESETKEY INTEGER,

      ACCESSRIGHTSKEY NUMBER NOT NULL,

      TARGETTYPE VARCHAR2(255 BYTE) NOT NULL,

      EXECUTIONCONTEXT VARCHAR2(255 BYTE),

      SERIALIZEDRULESET SYS.XMLTYPE

      )

      XMLTYPE SERIALIZEDRULESET STORE AS BASICFILE CLOB;

       

      CREATE TABLE Table_NCLOB

      (

      RULESETKEY INTEGER,

      ACCESSRIGHTSKEY NUMBER NOT NULL,

      TARGETTYPE VARCHAR2(255 BYTE) NOT NULL,

      EXECUTIONCONTEXT VARCHAR2(255 BYTE),

      SERIALIZEDRULESET NCLOB

      );

       

      ALL the tables have exactly the same data(102 rows), and SERIALIZEDRULESET has XML data.

       

       

      1) Total space consumed by Table_XML is more than Table_NCLOB and Table_XMLCLOB.

      TABLE_XML 256 KB

      TABLE_NCLOB 128 KB

      TABLE_XMLCLOB 128 KB

       

       

      2) After updating table stats on all the tables

       

      select * from TABLE_XML

      WHERE extract(SERIALIZEDRULESET, '/things/animals/text()')

      like '%tig%';

       

       

      FULL TABLE SCAN , COST 11

      -------------------------------------------

      select * from TABLE_NCLOB

      where SERIALIZEDRULESET like '%ti%';

       

      FULL TABLE SCAN , COST 7

       

      No indexes on SERIALIZEDRULESET on any of the tables.

      XML data initially was in Table_NCLOB. I have moved all the data to Table_XML with an intention that it would improve performance.

       

      My question to you is-

      Query on Binary XML is supposed to show an improved performance , but it is not the case here.

      why is that so ?

      What should I do to reduce cost when quering on table TABLE_xML and size of the table compared to others (if possible) ?

       

       

      Here is the sample xml -

      <things>

      <numbers><item>1</item><item>59</item></numbers>

      <animals><item>bird</item><item>cat</item><item>dog</item></animals>

      </things>

        • 1. Re: Performance over binary XML
          odie_63

          Query on Binary XML is supposed to show an improved performance , but it is not the case here.

          why is that so ?

          Sorry, I hardly see any proof.

          Do not rely on the cost figure to decide whether a query is performant or not.

           

          But first and foremost, both queries are obviously different. The first one works on a structured content (using a wrong approach btw), the second performs a simple pattern search.

          They're not comparable.

           

          If you want to use XMLType, then you'll have to adapt your queries so that they treat the content as XML, and not a bucket of random characters.

           

          e.g.

          select *

          from table_xml

          where xmlexists (

            '/things/animals/item[contains(., $pat)]'

            passing serializedruleset

                  , 'tig' as "pat"

          );