Results 1 to 3 of 3

Thread: XML processing

  1. #1
    Join Date
    Mar 2008
    Posts
    89

    Unanswered: XML processing

    Hi everyone,

    So I am having problems searching through XMLtype fields.
    Not so much the searching itself, but rather the time it takes.

    I have to work with this:
    A standard table, containing (among others) a CLOB column,
    the contents of which are actually xmltype values.
    And this table has 100s of 1000s of columns, constantly growing.

    I need to find those records that have a certain string inside of their CLOB field,
    so kinda like this:

    Code:
    Select count(*) from mytab
    where extractvalue(xmltype(message),'//messageType') = 'EMAIL'
    and created_on between to_date('01-01-2009','DD-MM-YYYY')
                                 and to_date('01-02-2009','DD-MM-YYYY');
    But trying to run this type of query on so many records didn't finish after over 60 minutes, when I finally cancelled it.

    I also tried to use the dbms_lob package, since it is, after all, just a CLOB:

    Code:
    Select count(*) from mytab
    where dbms_lob.instr(message,'EMAIL') > 0
    and created_on between to_date('01-01-2009','DD-MM-YYYY')
                                 and to_date('01-02-2009','DD-MM-YYYY');
    but I also had to stop it after 45 minutes or so...

    Unfortunately, I cannot change that somebody thought using XMLtype values in a CLOB column would be a brilliant idea.
    Any idea what I could use to make this query faster?
    Last edited by freeBatjko; 01-28-09 at 06:16.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Create a new VARCHAR2 for message type
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2008
    Posts
    89
    Quote Originally Posted by anacedent
    Create a new VARCHAR2 for message type
    Sorry, I didn't get that.
    Do you mean I should create a new varchar2 variable and assign it the value from extractvalue(xmltype(message),'//messageType')?

    Wouldn't that just add more overhead to the query? I am looking to replace this construct with something faster...
    "My brain is just no good at being a relational Database - my relations suck real bad!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •