Home » SQL & PL/SQL » SQL & PL/SQL » How to tuning this query
How to tuning this query [message #324611] Tue, 03 June 2008 05:46 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
Hi all,

please find and how to tuning this query, before i change this query it wll take more time. please do needful

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, 

          uimsmgr.utvsrvc srvc1, 

          uimsmgr.utrjapp japp1, 

          uimsmgr.ucbsvco svco1, 

          uimsmgr.uabscon scon1, 

          uimsmgr.ucnworg worg1 

   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 INSTR(',',utvsrvc_bus_sector_id,',' ) <> 0 

   --AND INSTR(utvsrvc_bus_sector_id,',' ) <> 0 

   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 

 







Re: How to tuning this query [message #324617 is a reply to message #324611] Tue, 03 June 2008 06:02 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Create view if it is static query.

or

do sum index on where clause attributes.

kanish
Re: How to tuning this query [message #324625 is a reply to message #324611] Tue, 03 June 2008 06:43 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

Regards
Michel
Previous Topic: Optimized Database Creation
Next Topic: Problem: Whats wrong with this PL/SQL procedure?
Goto Forum:
  


Current Time: Sat Dec 03 13:48:52 CST 2016

Total time taken to generate the page: 0.26445 seconds