Hi Team,
Need your guidance, whether creating index on table SSO_APEX_ZSC_HDR_TAB on columns HDR.ZSC_HEADER_ID and HDR.status will improve the performance, if yes kindly,please let us know the order of columns. There is no histogram on any of these columns.
SELECT /*+ GATHER_PLAN_STATISTICS */ HDR.ZSC_HEADER_ID
, APRV.MAX_APRV_REQUEST_ID MAX1_APRV
, SUB.MAX_SUB_REQUEST_ID MAX2_SUB
FROM SSO.SSO_APEX_ZSC_HDR_TAB HDR
LEFT JOIN ( SELECT BILL_HEADER_ID
, MAX(REQUEST_ID) AS MAX_APRV_REQUEST_ID
FROM SSO.SSO_AP_ZSC_APRVL_TRNS_TAB