As the title say, I have a huge SQL Query which was built over the time (as new requests came, fast workarounds were demanded).More, there were three DB Admins who contributed to this query, and I'm just a poor programmer who has to deal with it.
This query's purpose is to build a report, using some functions and apparently a HUGE SELECT. Now, the problem is that, obviously, it takes 3 hours to complete.
What I'd like is an idea (multiple ideas are welcome) on how could I reduce the run time of this query.
SPOILER ( huge query bellow ):
CREATE OR REPLACE FORCE VIEW "VDO"."V$RAP_PDM_SI_TRANSITIONS" ("FIRST_TRANS_ID", "TICKET_ID", "TICKET_TYPE", "SERVICE_ID", "New Case at", "FSR(first) at", "FS Done(last) at", "FS Done(last) by", "FS Validation(last) at", "FS validation(last) by", "Contract Definition(first) at", "Contract Signature(last) at", "Config Details(last) at", "Config Details(last) by", "SI(first) at", "SI(first) by", "SI(last) at", "Implementation need by date", "Srv Impl Due Date", "Emergency date", "Project date", "HLD_LL", "WO Generated at", "WO_GEN_FIRST_BY", "WO Closed at", "WO Closed by", "WO pending Stop Clock", "Testing(first) at", "Testing(last) at", "LL_EQP_WO", "START_BILLING_AT", "Start Billing by", "Billing Done at", "Billing Done by", "Closed at", "Cancelled at", "Cancelled by", "Start Porting at", "Porting Req Acc (last) at", "Porting Req Acc (first) at", "START_PORTING_IMP_F_AT", "Porting date", "PORTING_TESTING_L_AT", "First Comment", "First Comment At", "Last Comment", "Comment At", "Test rejected at", "CLIENT_NAME", "IS_CLIENT_WHOLESALER", "FINAL_USER_NAME", "REQUEST_TYPE", "LNO", "SERVICE_TYPE", "SRV_CAPACITY", "COUNTY_A", "LOCALITY_NAME_A", "ADDRESS_A", "COUNTY_Z", "LOCALITY_NAME_Z", "ADDRESS_Z", "CRT_STAT", "PDM_ASSIGNMENT", "BDE_ASSIGNMENT", "DSE_ASSIGNMENT", "EQP_LOC_A", "EQP_LOC_Z", "PACKET_ID", "PACKET_TYPE", "CLIENT_TYPE", "SLA Implementare", "WK_DIF_SI_TESTING", "WK_DIF_SI_PORT_TESTING", "REASON_STOP_CLOCK", "REASON_PENDING_OTHERS", "DURATION_STOP_CLOCK", "DURATION_PENDING_OTHERS", "SLA_IMPL_VALUE", "SLA_DEVIATION", "SLA_STATUS", "IS_INITIATOR_BSR", "CASE_OPENED_BY", "WK_DIF_STOP_CLOCK", "DELIVERY_PENDING_AT_FIRST", "DELIVERY_PENDING_AT_LAST", "DELIVERY_RESUME_DATE_FIRST", "DELIVERY_RESUME_DATE_LAST", "SLA_IMPLEMENTATION_TYPE") AS
select first_trans_id, ticket_id, ticket_type, service_id,
infotick.GETDATEFORSTATE(ticket_id, ticket_type,1,'F') "New Case at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,2,'F') "FSR(first) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,7,'L') "FS Done(last) by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'L') "FS Validation(last) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,16,'L') "FS validation(last) by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,16,'F') "Contract Definition(first) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,30,'L') "Contract Signature(last) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,37,'L') "Config Details(last) by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,18,'F') "SI(first) by",
infotick.GETDATEFORSTATECORR(ticket_id, ticket_type,18,'L') "SI(last) at",
infotick.getDateValueForFieldState(ticket_id, ticket_type,'IMPL_NEED_BY_DATE', 'S$START_IMPL', 18, 'L') "Implementation need by date",
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'SRV_IMPL_DUE_TIME', 'S$START_IMPL', 18, 'L') "Srv Impl Due Date",
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type,'EMERGENCY_DATE', 'S$START_IMPL', 18, 'L') "Emergency date",
infotick.getdatecorrvalueforfieldstate(ticket_id, ticket_type, 'PROJECT_DATE', 'S$START_IMPL', 18, 'L') "Project date",
get_ll_eqp(infotick.getStringValueForFieldState(ticket_id, ticket_type, 'LL_EQUIP_ID', 'S$HLD', 167, 'L')) hld_ll,
infotick.GETDATEFORSTATE(ticket_id, ticket_type,17,'F') "WO Generated at",
infotick.getUserForState(ticket_id, ticket_type, 17, 'F') wo_gen_first_by,
infotick.GETDATEFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,25,'F') "WO Closed by",
wo_pend_stop_clock "WO pending Stop Clock",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'F') "Testing(first) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,38,'L') "Testing(last) at",
get_ll_eqp(get_ll_for_case(ticket_id)) ll_eqp_wo,
start_billing_at,
infotick.GETUSERFORSTATE(ticket_id, ticket_type,40,'F') "Start Billing by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,42,'L') "Billing Done by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,21,'F') "Closed at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled at",
infotick.GETUSERFORSTATE(ticket_id, ticket_type,8,'F') "Cancelled by",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,76,'F') "Start Porting at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'L') "Porting Req Acc (last) at",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,78,'F') "Porting Req Acc (first) at",
start_porting_imp_f_at,
infotick.getDateValueForFieldState(ticket_id, ticket_type,'PORTING_DATE', 'S$START_PORTING_IMPL', 80, 'L') "Porting date",
porting_testing_l_at,
c_first.comments "First Comment",
c_first.added_at "First Comment At",
c_last.comments "Last Comment",
c_last.added_at "Comment At",
infotick.GETDATEFORSTATE(ticket_id, ticket_type,39,'L') "Test rejected at",
client_name, is_client_wholesaler, final_user_name, request_type, lno, service_type, srv_capacity, county_a,
locality_name_a, address_a,
county_z, locality_name_z, address_z, crt_stat,
getgroupassignment (68, ticket_id, ticket_type) pdm_assignment,
getgroupassignment (41, ticket_id, ticket_type) bde_assignment,
getgroupassignment (43, ticket_id, ticket_type) dse_assignment,
get_ll_eqp(eqp_a) eqp_loc_a,
get_ll_eqp(eqp_z) eqp_loc_z,
packet_id,
packet_type,
get_client_type_desc_for_case(ticket_id) client_type,
decode ((infotick.getStringValueForFieldState(ticket_id, ticket_type, 'SLA_IMPLEMENTARE', 'S$START_IMPL', 18, 'L')),1, 'Standard',2, 'Non-standard', '-') "SLA Implementare",
decode (si_f_at,null,null,decode(testing_f_at,null,null,wkdays(si_f_at,testing_f_at))) wk_dif_si_testing,
decode (start_porting_imp_f_at,null,null,decode(porting_testing_l_at,null,null,wkdays(start_porting_imp_f_at,porting_testing_l_at))) wk_dif_si_port_testing,
get_pending_ext_reason (ticket_id) reason_stop_clock,
get_pending_reason (ticket_id) reason_pending_others,
get_pending_duration (ticket_id, 44) duration_stop_clock,
get_pending_duration (ticket_id, 23) duration_pending_others,
sla.getSLAImpl_new(ticket_id) sla_impl_value,
decode(sla.getSLAImpl_new(ticket_id),null,null,
decode(si_f_at,null,null,
decode(testing_f_at,null,null,sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)) )) sla_deviation,
decode(sla.getSLAImpl_new(ticket_id),null,null,
decode(si_f_at,null,null,
decode(testing_f_at,null,null,
decode(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at) -
abs(sla.getSLAImpl_new(ticket_id) - wkdays(si_f_at,testing_f_at)),0,'in SLA',
decode((infotick.getDateValueForFieldState(ticket_id, ticket_type, 'SRV_IMPL_DUE_TIME', 'S$START_IMPL' , 18, 'L') - trunc(testing_f_at)), 0, 'in SLA','out SLA'))))) sla_status,
is_user_in_group(opened_by,66) is_initiator_bsr,
case_opened_by,
decode (wo_pend_stop_clock,null,null,decode(due_date,null,null,wkdays(due_date, wo_pend_stop_clock))) wk_dif_stop_clock,
infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'F') delivery_pending_at_first,
infotick.getDateForStatecorr(ticket_id, ticket_type, 138, 'L') delivery_pending_at_last,
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'F') delivery_resume_date_first,
infotick.getDateCorrValueForFieldState(ticket_id, ticket_type, 'RESUME_DATE', 'S$DELIVERY_PENDING', 138, 'L') delivery_resume_date_last,
sla_impl.type_name as sla_implementation_type
from (
select trans.first_trans_id, wk.ticket_id, wk.ticket_type, va.service_id, (select max(asrq_id_loc_a) from s$case_asrq sc where sc.case_id = wk.ticket_id)
asrqid_loc_a_last ,
(select max(asrq_id_loc_z) from s$case_asrq sc where sc.case_id = wk.ticket_id) asrqid_loc_z_last ,
va.client_name, va.is_client_wholesaler, va.final_user_name, va.request_type,
va.lno, va.service_type, va.capacity||' '||va.capacity_um srv_capacity,
va.county_a, va.locality_name_a, va.address_a,
va.county_z, va.locality_name_z, va.address_z, va.crt_stat,
FSD.LL_EQP_A eqp_a,
FSD.LL_EQP_z eqp_z,
va.packet_id,
va.packet_type,
FSD.CLIENT_APPRV_NEEDED_LOC_A Client_Approval_Needed_a,
FSD.CLIENT_APPRV_NEEDED_LOC_Z Client_Approval_Needed_z,
FSD.ORO_APPRV_NEEDED_LOC_A Oro_Approval_Needed_a,
FSD.ORO_APPRV_NEEDED_LOC_Z Oro_Approval_Needed_z,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,18,'F') si_f_at,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,38,'F') testing_f_at,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,83,'L') porting_testing_l_at,
infotick.GETDATEFORSTATE(wk.ticket_id, wk.ticket_type,80,'F') start_porting_imp_f_at,
infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type,40,'F') start_billing_at,
va.opened_by opened_by,
va.case_opened_by,
infotick.GETDATEFORSTATE(wk.ticket_id, ticket_type, 44,'L') wo_pend_stop_clock,
infotick.getDateValueForFieldState(wk.ticket_id, wk.ticket_type,'DUE_DATE', 'S$WO_GENERATION', 17, 'L') due_date
from wk$transitions wk, v$all_cases_for_pdm_rap va, s$fs_done fsd,
(SELECT MAX (x.trans_id) trid, x.ticket_id tid
FROM wk$transitions x
WHERE x.ticket_type = 'C'
AND x.to_stat_id = 7
/*AND x.is_correction IS NULL*/
AND x.in_pending IS NULL
group by x.ticket_id ) trans_id_max_to_fs_done,
(select min(trans_id) first_trans_id, ticket_id
from wk$transitions
where ticket_type = 'C'
group by ticket_id) trans
where wk.ticket_type='C' and wk.to_stat_id = 1
--and trunc(updated_at) between to_date(:P19_FROM_DATE,'dd-mm-yyyy') and to_date(:P19_TO_DATE,'dd-mm-yyyy')
and wk.ticket_id = va.case_id
and trans_id_max_to_fs_done.tid (+)= wk.ticket_id
AND fsd.trans_id (+) = trans_id_max_to_fs_done.trid
and trans.ticket_id = wk.ticket_id
) aux,
-- comments
(select case_id, min(id) id
from wk$comments c
group by case_id) c_min,
wk$comments c_first,
(select case_id, max(id) id
from wk$comments c
group by case_id) c_max,
wk$comments c_last,
-- Lookup table for SLA implementation
lk$implementation_type sla_impl
where ticket_id = c_min.case_id(+)
and c_min.id = c_first.id(+)
and ticket_id = c_max.case_id(+)
and c_max.id = c_last.id(+)
-- Join Condition for SLA implementation
and sla_impl.id (+) = infotick.getStringValueForFieldStateCor(ticket_id, 'C', 'SLA_IMPLEMENTARE','S$START_IMPL',18, 'L');
Some of the function definitions that were used in this query:
create or replace package infotick as
function getDateForState(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return date;
function getDateForStatecorr(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return date;
function getUserForState(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return varchar2;
function getUserForStateCorr(p_ticketid number, p_tickettype varchar2, p_groupid number, p_whichstate varchar2) return varchar2;
function getDateValueForFieldState(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return date;
function getDateCorrValueForFieldState(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return date;
function getDateValueForFieldTrans(p_transid number, p_fieldname varchar2) return date;
function getStringValueForFieldState(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return varchar2;
function getStringValueForFieldStateCor(p_ticketid number, p_tickettype varchar2,p_fieldname varchar2, p_tablename varchar2, p_groupid number,p_whichstate varchar2) return varchar2;
function getStringValueForFieldTrans(p_transid number, p_fieldname varchar2) return varchar2;
function getPortingDateforState(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return date;
function getNotPortingDateforState(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return date;
function getWOPortingTransID(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return pls_integer;
function getWONotPortingTransID(p_ticketid number, p_tickettype varchar2, p_stateid number, p_whichstate varchar2) return pls_integer;
end;
I'll provide any other information if needed.
EDIT: Just to be sure this won't be flagged for not being the maintainer of the code, I'll have to maintain it and add new functionalities to it as new requests come. I've got some experience with SQL but this simply looks like a spaghetti query to me. I'm sure it can be refactored.
SELECTusesSQL Serversyntax and as far as I know, the functions arePL/SQL. \$\endgroup\$