Re: XMLType performance

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 13 Apr 2007 19:06:01 -0700
Message-ID: <1176516354.471922@bubbleator.drizzle.com>

Serge Rielau wrote:
> 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

I've made myself perfectly clear.

If you wish to store docs ... store docs. If you wish to store data ... store data.

XML was not designed for the usage you suggest.

Just because something is the marketing buzzword d'jour doesn't mean we all have to jump on it.

And when you write: "I don't think that any major DBMS vendor wants to see ...." You should be able, after all of our exchanges, to know exactly how much I care about what any DBMS vendor wants. My comments are with respect to what makes most sense for that vendor's customers.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Apr 13 2007 - 21:06:01 CDT