Re: XMLType performance

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Fri, 13 Apr 2007 16:39:10 -0400
Message-ID: <58a83gF2fs4sfU1@mid.individual.net>

DA Morgan wrote:
> peteh wrote:

>> DA Morgan wrote:
>>> Please don't top post. Scroll to the bottom to reply.
>>>
>>> Consider this:
>>>
>>> set serveroutput on
>>>
>>> DECLARE
>>>   ctx    dbms_xmlgen.ctxHandle;
>>>   xml    CLOB;
>>>   emp_no NUMBER := 7369;
>>>   xmlc   VARCHAR2(4000); -- Required to convert LOB to VARCHAR2
>>>   off    PLS_INTEGER := 1;
>>>   len    PLS_INTEGER := 4000;
>>> BEGIN
>>>    ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno =
>>>    '|| emp_no);
>>>    dbms_output.put_line(ctx);
>>>
>>>    xml := dbms_xmlgen.getXML(ctx);
>>>    dbms_output.put_line(xml);
>>>
>>>    dbms_xmlgen.closeContext(ctx);
>>>
>>>    dbms_lob.read(xml, len, off, xmlc);
>>>    dbms_output.put_line(xmlc);
>>> END;
>>> /
>>>
>>> Why would I want to store the XML?
>>> -- 
>> How does this example relate in any meaningful way to the business
>> scenario I provided? How does this illustrate the ability to treat the
>> data WITHIN the xml doc as columns in a table to be SELECTed, UPDATEd,
>> INSERTed  ....

>
> It doesn't nor is it intended to. It is meaningful in that it
> demonstrates how to properly store information ... relationally
> and then reconstitute the XML if required to do so at a later date.
>
> I couldn't be clearer about my feelings about XML and what you are
> trying to do. I think it is a bad practice. Inefficient with respect
> to storage, io, and CPU.

So, now you have this XML document stored. Perhaps an insuarnce policy.

Next year the business decides that the terms and conditions of that contract need be modified for all customers whose terms and conditions state XYZ. How do we find these customers? Clearly not a piece of information stored in a relational column (for arguments sake).
To find those customers we have now a few choices: Run some sort of regular expression search on the text representation of the XML document and then eyeball to filter out all the false hits where XYZ showed up out of context.
Extract the XML documents of all customers from the database and pass them to an XQUERY engine which will find XYZ in the terms and conditions tag.
Run an XQUERY against the stored documents right where they are to find the affected customers. It will return exactly what you need. It may even be able to exploit an XML index.

One way or another this XML document needs to be searched. It is anyone's choice as a DBA whether one wants to embrace that need and cope with it or give control away and be treated like the XML-bit-bucket one   chose to make the database.

I don't think that any major DBMS vendor wants to see this XML document that's there whether we like it or not be processed outside of the database with no fine grained authorization check, no transaction control and no option to push down predicates.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Received on Fri Apr 13 2007 - 15:39:10 CDT