| Extract value from xml [message #654244] |
Thu, 28 July 2016 13:14  |
 |
Raj12345
Messages: 1
Registered: July 2016
|
Junior Member |
|
|
Hi Experts,
Need you quick and urgent help on the below.
I have a log table with huge number of records in,130 Millions which has a column of XMLTYPE and contains XML.
Another table (EMP) which needs to be joined to it to extract value of 4 nodes from the above table.
I have tried using the extractvalue function and the XML table with parallel but it takes a lot of time when the records in EMP table increases.
Could you please help how it can be done quickly or any other alternative
create table temp
as (
WITH mydata as (select data xx from log_table log where emp.id = log.id)
select * from mydata,xmltable('*/*[local-name()=''partya''][@id=''123'']'
passing mydata.xx
columns party_a_name varchar2(20) PATH '/',
issuer varchar2(1000) PATH '//@idscheme'
)
This takes around 27~30 mins to create the above table,when emp has around 1L records.
Name Rows
Operation
SELECT STATEMENT 661182611
NESTED LOOPS 661182611
NESTED LOOPS 80948
TABLE ACCESS FULL EMP 77816
TABLE ACCESS BY INDEX ROWID BATCHED Log_table 1
INDEX RANGE SCAN RRLD_UK1 1
COLLECTION ITERATOR PICKLER FETCH XQSEQUENCEFROMXMLTYPE 8168
Kindly excuse the brevity and typo
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
