xml - repeatable blocks with range in oracle -
please find below table. entire data within docstart , docend. data further enclosed within baccstart , baccend. type of block repeatable. have pick of abcd repeatable , total(occurring once per block) , accname (occurring once per block) each block within baccstart , baccend , form xml
<baccstart> <total>100</total> <abcd>abcd</abcd> <accname>name</accname> </baccstart> for each such block. presently using loop, performance not mark. have around 200 such blocks have form xmls within 15 seconds. presently loop taking around 53 secs.
rownum name value 1 docstart null 2 baccstart null 3 abcd abcd 4 abcd abcd2 5 pqrs pqrs 6 pqrs pqrs2 7 total 100 8 accname name 9 baccend null 10 baccstart null 11 abcd abcd 12 abcd abcd2 13 pqrs pqrs3 14 pqrs pqrs4 15 total 150 16 accname name 17 baccend null 18 docend null please me out this. if possible time effective query.
it oracle 10g.
i have across modification in requirement. value of accname present in cofig table of around 90 rows. there have pick 3 values value1 accname , corresponding value2 , value3 , populate tags in xml. if join table performance impact. please suggest. config table like
header1 header2 header3 header4 ........ <accname> value2 value3 ............. ............. ............. ............. the data presented above, table contains long. after converting clob , using xmltable getting above data. better use global temporary table insert data before proceeding. like
insert data(row_num,name,value) select /*+ no_xml_query_rewrite , parallel(x,8) */ rownum rn, substr(extractvalue(x.column_value,'/e'),1,instr(extractvalue(x.column_value,'/e'),' ')-1) name, substr(extractvalue(x.column_value,'/e'),instr(extractvalue(x.column_value,'/e'),' ')+1) value dual, xmltable('e' passing xmltype('<e><e>' || replace(long2clob('select longdata billedacc order segment_number'), '|'||chr(10), '</e><e>') || '</e></e>').extract('e/e') ) x; also suggest if there better way handle this.
given 9000 data rows (500 * 18 sample rows provided above) following code runs in around second, should hit performance targets:
declare l_xml_data xmltype; begin <<baccstart_loop>> r_baccstart in ( -- initially, start/end rows delimiters_all_rows ( select d.* data d d.name in ('baccstart', 'baccend') ) -- lead on rows order row_num start/end pairs -- single row , delimiters_all_rows_joined ( select dar.row_num baccstart_row_num , lead(dar.row_num) on (order row_num) baccend_row_num , dar.name delimiters_all_rows dar ) -- eliminate baccend rows have row_num values select * delimiters_all_rows_joined darj name = 'baccstart' ) loop -- relevant rows data block data_rows ( select d.* data d d.row_num > r_baccstart.baccstart_row_num , d.row_num < r_baccstart.baccend_row_num , ( d.name in ('total','accname') or (d.name = 'abcd' , d.row_num = ( select min(d2.row_num) data d2 d2.row_num > r_baccstart.baccstart_row_num , d2.row_num < r_baccstart.baccend_row_num , d2.name = 'abcd' )) ) ) -- agg rows single xml block (note: evalname dynamic xml element name) select xmlelement("baccstart" , xmlagg( xmlelement(evalname(dr.name), dr.value) order decode(dr.name, 'total', 1, 'abcd', 2, 3) ) ) xml_data l_xml_data data_rows dr; insert data_xml values (l_xml_data); end loop baccstart_loop; end; i tried work in single query, xmlagg performance seems drop through floor apply group by many rows (something i've seen happen time , time again). performance became more acceptable when xmlagg limited few rows possible.
the resulting xml gets inserted table called data_xml (just see results), can substitute whatever need xml fragments once they're generated!
i'd advise if inserting data table, insert/select opposed select into followed insert it'll faster due lack of context switching between sql , pl/sql.
the following ddl/dml used set example:
create table data ( row_num integer , name varchar2(10) , value varchar2(10) ) / create table data_xml ( xml_data xmltype ) / create sequence data_seq start 1 increment 1 cache 1000 / begin idx in 1 .. 500 loop insert data (row_num, name, value) values (data_seq.nextval, 'docstart', null); insert data (row_num, name, value) values (data_seq.nextval, 'baccstart', null); insert data (row_num, name, value) values (data_seq.nextval, 'abcd', 'abcd'); insert data (row_num, name, value) values (data_seq.nextval, 'abcd', 'abcd2'); insert data (row_num, name, value) values (data_seq.nextval, 'pqrs', 'pqrs'); insert data (row_num, name, value) values (data_seq.nextval, 'pqrs', 'pqrs2'); insert data (row_num, name, value) values (data_seq.nextval, 'total', '100'); insert data (row_num, name, value) values (data_seq.nextval, 'accname', 'name'); insert data (row_num, name, value) values (data_seq.nextval, 'baccend', null); insert data (row_num, name, value) values (data_seq.nextval, 'baccstart', null); insert data (row_num, name, value) values (data_seq.nextval, 'abcd', 'abcd'); insert data (row_num, name, value) values (data_seq.nextval, 'abcd', 'abcd2'); insert data (row_num, name, value) values (data_seq.nextval, 'pqrs', 'pqrs3'); insert data (row_num, name, value) values (data_seq.nextval, 'pqrs', 'pqrs4'); insert data (row_num, name, value) values (data_seq.nextval, 'total', '150'); insert data (row_num, name, value) values (data_seq.nextval, 'accname', 'name'); insert data (row_num, name, value) values (data_seq.nextval, 'baccend', null); insert data (row_num, name, value) values (data_seq.nextval, 'docend', null); end loop; end; /
Comments
Post a Comment