optiminzing the query and export : below code gives the result but it take 5 min's to execute, after which I need export data to text. there are 12 lakhs records and it does not get export even after 9 hours. could you please help in optimizing the query and improving the execution performance and also help in getting export faster. I can not use offset and fetch in SQL developer.
I use SQL DEVELPER.
with NAGP As
(select Company_common_id,PROFILECLASSVALUE from (select /* + PARALLEL( gp, 20)*/  co.Company_common_id, gp.PROFILECLASSVALUE,rank() over (partition by co.Company_common_id order by co.commit_date desc) RK from stg_cmat.cmat_sync_ref co, stg_cmat.cmat_enrich_ref gp where gp.transaction_id=co.transaction_id and co.event='ENRICHMENT'
and gp.profilename='EnrichmentProfile'and gp.PROFILECLASSNAME='NAGP ID')
where RK =1),cte2 as(select EC.system_serial_number,EC.cmat_customer_id EC_cmat_customer_id,EC.system_status EC_system_status,(select .PROFILECLASSVALUE from NAGP n where n.Company_common_id = EC.cmat_customer_id and rownum=1) EC_NAGP_ID,SN.cmat_customer_id SN_cmat_customer_id,SN.system_status SN_system_status,(select n.PROFILECLASSVALUE from NAGP n where n.Company_common_id = SN.cmat_customer_id and rownum=1) SN_NAGP_ID from (select  a.system_serial_number, a.role_id, a.cmat_customer_id, s.system_status from EIM.eim_latest_sys_party_role a, eim.eim_system s where a.system_serial_number = s.system_serial_number (+)
    and a.role_id =1) EC, (select  a.system_serial_number, a.role_id, a.cmat_customer_id, s.system_status rom EIM.eim_latest_sys_party_role a, eim.eim_system s where a.system_serial_number = s.system_serial_number (+)
    and a.role_id =19) SN where  EC.system_serial_number=SN.system_serial_number)
select ec_system_status,count(decode ec_system_status, '0',system_serial_number,0))as ec_countofnum,sn_system_status,
count(decode(sn_system_status, '0',system_serial_number,0))as SN_countofnum from cte2 GROUP BY ec_system_status,sn_system_status
