xmlagg high CPU utilization .

Database
Tourist

xmlagg high CPU utilization .

Hi All I have the below query using XML aggregate function. the bigtable is 1.6 TB in size and the small table is 130 gb in size. The query consumes most of the CPU just due to the XML aggregate function. On an average, the query consumes 15k with xmlagg and 1k without XML agg.

PI for both the tables is (unique_id,sqnbr)

Using the below query, i create a volatile table which is then used in the main query. Can someone please let me know if there is a replacement for xmlagg function which I can apply here? Appreciate you help

 

 

SELECT a.stn ,
a.mdn ,
a.sbc ,
a.ptc ,
a.icrd,
a.ipmc,
XMLAGG( b.shc || ',
' ) ( VARCHAR ( 30 ) ) AS spcl_cd
FROM bigtable a , smalltable b

WHERE a.unique_id = b.unique_id
AND a.sqnbr = b.sqnbr
AND b.shc IN ( '01','02',... )
AND 1 < (
SELECT COUNT ( DISTINCT shc )
FROM smalltable c
WHERE a.unique_id = c.unique_id
AND a.sqnbr = c.sqnbr
AND c.shc IN ( '01','02' ) )
AND CAST( a.dt AS DATE ) BETWEEN current_date - ( 60 )
AND current_date - ( 59 )
GROUP BY 1 , 2 , 3 , 4 , 5,6 ) T ( stn,mdn,Sbc,
Ptc,icrd,ipmc
,shc)