Hi,
The below query is taking long time for just 1000 records.
WHERE condition is not an issue, but in the select columns, I am using select statements in which I am using SQL Functions.
Can we improve the performance of the select columns which is taking more time to fetch the records.
Help Appreciated.
SELECT agsj1.*, (select distinct fu.user_name from apps.fnd_user fu where fu.user_id = agsj1.updatedby) ByWhom
FROM (select csx_rebillinv_hdr_id RebillHeaderId,
ccrh.equipment_initial||ccrh.equipment_number EquipmentMarkNumber,
ap_invoice_number InvoiceNumber,
ap_invoice_date InvoiceDate,
ccrh.repair_date InvoiceRepairDate,
TRUNC(ccrh.creation_date) + 8 invoiceagsj1queueentry,
DECODE(j1hdr.gate_cd,'I','Ingate','O','Outgate') InterchangeStatus,
TO_CHAR(j1hdr.inspection_dt, 'MM/DD/YYYY HH24:MI') InterchangeDateTime,
j1hdr.geo_location_name InterchangeGeoLocation,
j1hdr.ags_location AgsLoc,
j1hdr.x_gate,
j1hdr.inspection_dt,
max(inspection_dt) over(partition by inv_number) max_insp_dt,
(select LISTAGG(DECODE(part_code||','||location_code||','||why_made_code,',,',null,' * '||part_code||','||location_code||','||why_made_code))
WITHIN GROUP (ORDER BY line_id desc) AS xx1
from csx_iem_j1_lines ln where ln.header_id = j1hdr.header_id
and seq_number in (select seq_number from(select jl.seq_number,header_id,line_id,max(seq_number) over(partition by header_id) maxseqnum from csx_iem_j1_lines jl)
where seq_number = maxseqnum and header_id= ln.header_id)
group by ln.header_id) InspectionCode,
(CASE WHEN j1hdr.attribute1 = 'R'
THEN NVL(to_char(greatest(j1hdr.last_update_date,
(select max(cjlln.last_update_date) from csx_iem_j1_lines cjlln
where cjlln.header_id = j1hdr.header_id and cjlln.attribute1 = 'AGS')
),'MM/DD/YYYY HH24:MI'),to_char(j1hdr.last_update_date,'MM/DD/YYYY HH24:MI'))
ELSE (select to_char(max(cjl.last_update_date),'MM/DD/YYYY HH24:MI') from csx_iem_j1_lines cjl
where cjl.header_id = j1hdr.header_id and cjl.attribute1 = 'AGS')
END) LastSubmitDate,
(CASE WHEN j1hdr.attribute1='R'
THEN (CASE WHEN j1hdr.last_update_date >= nvl((select max(cjl.last_update_date) from csx_iem_j1_lines cjl
where cjl.header_id = j1hdr.header_id and cjl.attribute1 = 'AGS'),j1hdr.last_update_date)
THEN j1hdr.last_updated_by
ELSE (select distinct last_updated_by from csx_iem_j1_lines
where header_id = j1hdr.header_id
and last_update_date = (select max(cjl.last_update_date) from csx_iem_j1_lines cjl
where cjl.header_id = j1hdr.header_id and cjl.attribute1 = 'AGS'))
END)
ELSE (select distinct last_updated_by from csx_iem_j1_lines where header_id = j1hdr.header_id
and last_update_date = (select max(cjl.last_update_date) from csx_iem_j1_lines cjl
where cjl.header_id = j1hdr.header_id and cjl.attribute1 = 'AGS'))
END) UpdatedBy
from csx_iem_cct_rebillinv_hdrs ccrh, csx_iem_j1_headers j1hdr, csx_iem_user.csx_iem_cct_woinv_hdrs ccwh
where ccwh.csx_woinv_hdr_id = ccrh.csx_woinv_hdr_id
and exists(select 1 from csx_iem_cct_rebillinv_lines ccrl
where ccrh.csx_rebillinv_hdr_id = ccrl.csx_rebillinv_hdr_id
and initial_rebill_logic='AGS'
and (cct_rebill_logic in ('J1','AGS') or cct_rebill_logic is null))
and ((j1hdr.equipment_initial = ccwh.repaired_equipment_initial
and j1hdr.equipment_number = ccwh.repaired_equipment_number)
OR (j1hdr.associated_equipment_initial = ccwh.repaired_equipment_initial
and j1hdr.associated_equippment_number = ccwh.repaired_equipment_number))
and (j1hdr.ags_location = 'Y' OR j1hdr.x_gate = 'Y')
and j1hdr.inspection_dt < ccrh.repair_date + NVL(SUBSTR (repair_time, 1, 2) / 24,0) + NVL(SUBSTR (REPLACE (repair_time, ':'), 3) / 1440,0)
and j1hdr.inspection_dt > sysdate - 120
) agsj1
where inspection_dt = (CASE WHEN (x_gate ='Y' AND EXISTS(select 1 from csx_iem_cct_inv_lines ccil, csx_iem_user.csx_iem_aar_matrix aar
where ccil.csx_woinv_hdr_id = csx_woinv_hdr_id and aar.job_code = ccil.inv_job_code
and repair_type = 'Tire'))
THEN max_insp_dt ELSE inspection_dt END)
and TRUNC(InvoiceDate) between '01-AUG-2016' and '30-SEP-2016'
ORDER
BY InterchangeDateTime
desc;
Thanks
Aman