XML Count rollup [message #631615] |
Fri, 16 January 2015 08:34 |
|
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 #631655 is a reply to message #631621] |
Fri, 16 January 2015 12:36 |
|
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.
|
|
|