--explain plan for
SELECT 
          NVL(worg1.ucnworg_oper_distr_code,'00A00'), 
          substr(NVL(worg1.ucnworg_engineer_sector_id,'A0000'),4,2),
          scon1.uabscon_cust_code,
          scon1.uabscon_prem_code,
          serv1.ucrserv_next_visit_date,
          scon1.uabscon_end_date,
          scon1.uabscon_srvc_code,
          scon1.uabscon_status_ind,
          svco1.ucbsvco_requested_date,
          NVL(utvflue_desc,' '),
          svco1.ucbsvco_code, 
          decode(ucbsvco_code,NULL,0,1),
          serv1.ucrserv_styp_code, 
          serv1.ucrserv_srvc_code 
   FROM 
    /* HD069092 - Modified the order of the table to optimise the query */
          uimsmgr.ucrserv serv1, 
          uimsmgr.utvflue flue1, ----count(*) =5
          uimsmgr.utvsrvc srvc1, --- count(*) =448
          uimsmgr.utrjapp japp1, -- count(*) =2024
          uimsmgr.ucbsvco svco1,
          uimsmgr.uabscon scon1, 
          uimsmgr.ucnworg worg1 -- count(*) = 9709
   WHERE 
          worg1.ucnworg_postcode_sector_id (+) = scon1.uabscon_postcode_sector_id 
   AND    nvl(worg1.ucnworg_patch_type,'S') = 'S' 
   AND    svco1.ucbsvco_cust_code (+) = scon1.uabscon_cust_code 
   AND    svco1.ucbsvco_prem_code (+) = scon1.uabscon_prem_code 
   AND    svco1.ucbsvco_stus_code (+) = 'O' 
   AND    serv1.ucrserv_scon_number = scon1.uabscon_number 
   AND    serv1.ucrserv_status_ind = 'A'
   AND    scon1.uabscon_bulk_ind != 'Y' 
   AND    scon1.uabscon_status_ind != 'P'
   AND    scon1.uabscon_status_ind != 'C' 
   AND    srvc1.utvsrvc_code = serv1.ucrserv_srvc_code 
    AND    japp1.utrjapp_styp_code = serv1.ucrserv_styp_code 
   AND    japp1.utrjapp_srvc_code = serv1.ucrserv_srvc_code 
   AND    NVL(japp1.utrjapp_visit_months, 0) > 0  
   AND    flue1.utvflue_type (+) = serv1.ucrserv_flue_type_ind 
 ORDER BY 
 NVL(worg1.ucnworg_oper_distr_code,'00A00', 
 scon1.uabscon_cust_code,scon1.uabscon_prem_code