Home » SQL & PL/SQL » SQL & PL/SQL » Help tune this SQL
Help tune this SQL [message #196659] Fri, 06 October 2006 07:22 Go to next message
kirso491
Messages: 8
Registered: October 2006
Junior Member
&Orig_Where_Clause = "WHERE (ORIGINATOR_ID = 545 OR AUTHORIZATION_ID = 545 OR RECRUITER_ID = 545 OR EXISTS (SELECT 'X' FROM PS_APPLICATN_ROUTE A WHERE A.ROUTE_TO_ID = 545 AND PS_ER_SS_REQ_SRCH2.JOB_REQ_NBR = A.JOB_REQ_NBR))";

The above where clause is taking more time
I need to tune this SQL.I need to assign the where clause to a variable since I am using the same where clause at different places.
Re: Help tune this SQL [message #196674 is a reply to message #196659] Fri, 06 October 2006 08:14 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Try putting a concatenated index on PS_APPLICATN_ROUTE (ROUTE_TO_ID, JOB_REQ_NBR).
Re: Help tune this SQL [message #196676 is a reply to message #196659] Fri, 06 October 2006 08:17 Go to previous messageGo to next message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
Try separate UNION ALL queries
Re: Help tune this SQL [message #196745 is a reply to message #196659] Fri, 06 October 2006 23:58 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
  • Change the EXISTS to an IN. It shouldn't matter in 9i or above, I just want to make it easier for the optimizer, because it has a few things to do.
  • Place separate indexes on PS_ER_SS_REQ_SRCH2 for
    • ORIGINATOR_ID
    • AUTHORIZATION_ID
    • RECRUITER_ID
    • JOB_REQ_NBR
    If you already have an index with the first column of ORIGINATOR_ID, don't create a new one. Same goes for the other 3.
  • Use the INDEX_COMBINE hint to get the optimizer to use a bitmap path. This does NOT require the indexes to be BITMAP indexes, but it uses the results from the b-tree indexes like a bitmap index.
  • Change the INDEX_COMBINE hint to USE_CONCAT and try again - compare performance.
  • Run EXPALIN PLAN on both SQLs. If performance is not satisfactory for either, post the full SQLs and plans here. Make sure you enclose them in [code] and [/code] tags so that we can read them.


SELECT /*+ INDEX_COMBINE*/ *
FROM PS_ER_SS_REQ_SRCH2
WHERE (   ORIGINATOR_ID = 545 
       OR AUTHORIZATION_ID = 545 
       OR RECRUITER_ID = 545 
       OR JOB_REQ_NBR  IN (
          SELECT JOB_REQ_NBR
          FROM PS_APPLICATN_ROUTE A 
          WHERE A.ROUTE_TO_ID = 545 
       )


Ross Leishman
Previous Topic: how it's working?
Next Topic: display date with time
Goto Forum:
  


Current Time: Tue Dec 06 06:26:08 CST 2016

Total time taken to generate the page: 0.07148 seconds