Help anyone please, when i am running query (1) running fine give result 0.030 second But in this query add WM_CONCAT AND GROUP BY(query 2) then query take 40 second.
Please help anyone.........Thanks
(1)
select p.PRICE_ID,p.PRICEBOOKID, cp.CUSTOMER_NAME,NULL as channel_name, p.SERVICECODE,
decode(s.SERVICETYPE,'Voice','Voice Line','VOIP','VOIP Access',s.SERVICETYPE) SERVICETYPE,p.COUNTRYCODE, c.COUNTRYDESC,p.GEOGRAPHYCODE,
p.BANDWIDTHCODE, b.BANDWIDTHDESC,p.ZONECODE, z.ZONEDESC,p.CURRENCYCODE, p.COST_NRC, p.COST_MRC,
p.REMARKS,p.STATUS, p.OCN,P.DELIVERY_SUPPLIERCODE_A, SP.SUPPLIERNAME,P.DELIVERY_SUPPLIERCODE_B,SP1.SUPPLIERNAME as SUPPLIERNAMEb,P.ACCESSCODE_A, a.ACCESS_INTERNAL_NAME,p.ACCESSCODE_B, a1.ACCESS_INTERNAL_NAME ACCESS_INTERNAL_NAMEb,p.PRICING_TYPE,p.FEATURECODE,epf.FEATURE_NAME, p.ASSEMBLY_CODE, epa.ASSEMBLY_NAME, p.PRICE_NRC1, p.PRICE_MRC1, p.TOTALPRICE1,p.PRICE_NRC2, p.PRICE_MRC2,p.TOTALPRICE2, p.PRICE_NRC3, p.PRICE_MRC3, p.TOTALPRICE3
from eix_price p,EIX_PROD_FEATURE epf,EIX_PROD_ASSEMBLY epa,eix_accesstype a,eix_accesstype a1,eix_service s,eix_country c,eix_bandwidth b,eix_zone z,eix_supplier sp,eix_supplier sp1,eix_cust_pricebook cp
where p.assembly_code=epa.ASSEMBLY_CODE(+) and p.FEATURECODE = epf.FEATURECODE(+) and p.ACCESSCODE_A = a.ACCESSCODE(+)
and p.ACCESSCODE_B = a1.ACCESSCODE(+) and p.SERVICECODE = s.SERVICECODE and p.COUNTRYCODE = c.COUNTRYCODE
and p.BANDWIDTHCODE = b.BANDWIDTHCODE(+) AND P.ZONECODE = Z.ZONECODE(+) AND P.DELIVERY_SUPPLIERCODE_A = SP.SUPPLIERCODE(+)
AND P.DELIVERY_SUPPLIERCODE_B = SP1.SUPPLIERCODE(+) AND P.PRICEBOOKID = CP.PRICEBOOKID and P.PRICEBOOKID<>0 and s.SERVICECODE = '5'
(2)
select p.PRICE_ID,p.PRICEBOOKID, cp.CUSTOMER_NAME,NULL as channel_name, p.SERVICECODE,
decode(s.SERVICETYPE,'Voice','Voice Line','VOIP','VOIP Access',s.SERVICETYPE) SERVICETYPE,p.COUNTRYCODE, c.COUNTRYDESC,p.GEOGRAPHYCODE,
p.BANDWIDTHCODE, b.BANDWIDTHDESC,p.ZONECODE, z.ZONEDESC,p.CURRENCYCODE, p.COST_NRC, p.COST_MRC,
p.REMARKS,(select WM_CONCAT(pc1.price_attribute || '=' || pc1.price_value) from eix_price_configuration pc1
where pc1.price_id=p.PRICE_ID GROUP BY P.PRICE_ID) as Attributes,p.STATUS, p.OCN,P.DELIVERY_SUPPLIERCODE_A, SP.SUPPLIERNAME,P.DELIVERY_SUPPLIERCODE_B,SP1.SUPPLIERNAME as SUPPLIERNAMEb,P.ACCESSCODE_A, a.ACCESS_INTERNAL_NAME,p.ACCESSCODE_B, a1.ACCESS_INTERNAL_NAME ACCESS_INTERNAL_NAMEb,p.PRICING_TYPE,p.FEATURECODE,epf.FEATURE_NAME, p.ASSEMBLY_CODE, epa.ASSEMBLY_NAME, p.PRICE_NRC1, p.PRICE_MRC1, p.TOTALPRICE1,p.PRICE_NRC2, p.PRICE_MRC2,p.TOTALPRICE2, p.PRICE_NRC3, p.PRICE_MRC3, p.TOTALPRICE3
from eix_price p,EIX_PROD_FEATURE epf,EIX_PROD_ASSEMBLY epa,eix_accesstype a,eix_accesstype a1,eix_service s,eix_country c,eix_bandwidth b,eix_zone z,eix_supplier sp,eix_supplier sp1,eix_cust_pricebook cp
where p.assembly_code=epa.ASSEMBLY_CODE(+) and p.FEATURECODE = epf.FEATURECODE(+) and p.ACCESSCODE_A = a.ACCESSCODE(+)
and p.ACCESSCODE_B = a1.ACCESSCODE(+) and p.SERVICECODE = s.SERVICECODE and p.COUNTRYCODE = c.COUNTRYCODE
and p.BANDWIDTHCODE = b.BANDWIDTHCODE(+) AND P.ZONECODE = Z.ZONECODE(+) AND P.DELIVERY_SUPPLIERCODE_A = SP.SUPPLIERCODE(+)
AND P.DELIVERY_SUPPLIERCODE_B = SP1.SUPPLIERCODE(+) AND P.PRICEBOOKID = CP.PRICEBOOKID and P.PRICEBOOKID<>0 and s.SERVICECODE = '5'