Home » SQL & PL/SQL » SQL & PL/SQL » XML Count rollup (Oracle 10g)
XML Count rollup [message #631615] Fri, 16 January 2015 08:34 Go to next message
zpele
Messages: 2
Registered: January 2015
Junior Member
I am trying to do some email validation on a big xml table. Because of the nature of the code a full table scan is being performed and an index cannot be used to speed it up. After some thought I realized that I can start by rolling up the results to just give me a count but I am not sure how to do this or if there are any other tricks to make this query work quicker. Each row is unique. Code is below minus proprietary information:

SELECT /*+ PARALLEL(example_table, 16)index(c) no_xmlindex_rewrite */ c.id,
        to_number(c.eid) as eid,
        cx.firstName,
        cx.lastName,
        cx.dob,
       em.dataSource,
        em.emailCategory,
        em.emailAddress,
        (case when cx.prefEmailId = em.id then 'PREFERRED' else null end) as PREF_INDICATOR,
        dump( em.emailAddress, 1010, length(em.emailAddress), 1 ) dump_result
FROM
        some_schema.example_table c,
        xmltable(
            xmlnamespaces(
                DEFAULT 'http://www.some_address.com',
                'http://www.some_address.com2' as "cm",
                'http://www.some_address.com3' as "util"
            ),
            '/customerProfile' passing c.object_value columns
            emailAddresses xmltype PATH 'contactInformation/emailAddresses',
            prefEmailId VARCHAR2(50) PATH 'preferences/contactInfoPreferences/preferredEmailMarker/@markedId',
            firstName VARCHAR2(40) PATH 'name/cm:givenName',
            lastName VARCHAR2(40) PATH 'name/cm:surName',
            dob DATE PATH 'personInformation/cm:birthDate'
        ) cx,
        xmltable(
            xmlnamespaces(
                DEFAULT 'http://www.some_address.com4',
                'http://www.some_address.com5' as "cm",
                'http://www.some_address.com6' as "ut"
            ),
            '/emailAddresses/emailAddress' passing cx.emailAddresses columns
            id VARCHAR2(50) PATH '@id',
            auditId VARCHAR2(50) path '@auditId',
            collectDate VARCHAR2(10) path '@collectDate',
            dataSource VARCHAR2(50) path '@dataSource',
            emailCategory VARCHAR2(50) path '@emailCategory',
            emailAddress VARCHAR2(50) PATH 'text()'
        ) em
WHERE 1=1
and cx.prefEmailId = em.id
AND NOT REGEXP_LIKE(em.emailaddress,'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Fri, 16 January 2015 08:41] by Moderator

Report message to a moderator

Re: XML Count rollup [message #631617 is a reply to message #631615] Fri, 16 January 2015 08:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Re: XML Count rollup [message #631620 is a reply to message #631617] Fri, 16 January 2015 09:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Explain why you do not validate the data prior to it getting into this database; instead to trying to identify bad data after the fact?
Re: XML Count rollup [message #631621 is a reply to message #631620] Fri, 16 January 2015 09:15 Go to previous messageGo to next message
zpele
Messages: 2
Registered: January 2015
Junior Member
Without getting into it too much... I work for a big corporation and do not have the influence to cause that change. I wish it weren't true that the validation isnt done application side but I can only work with what I have. I also know that using XML tables in this way is not generally a good idea but- again- I work with what I have.

The challenge I have working for this client is that you have to streamline basically all of your queries to use XML tables and they are not the quickest in the world by any means.

Basically my whole point in doing this in the first place is to find all the garbage that is present and get rid of it. This is not a query that will be running on a regular basis.
Re: XML Count rollup [message #631655 is a reply to message #631621] Fri, 16 January 2015 12:36 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
So figuratively speaking you have your right wrist tied to your left ankle & you come here asking us how to run a fast marathon.

>This is not a query that will be running on a regular basis.
IMO, if any query is run more than once, it is being run on a regular basis. It is just a matter of time horizon perspective.

Please explain the downside if this query takes days or even weeks to complete.

You reap what has been sowed.
Previous Topic: cursor_for_loop allows PL/SQL SELECT..INTO statement
Next Topic: CR/LR in column
Goto Forum:
  


Current Time: Fri Mar 29 00:07:23 CDT 2024