| Help tune this SQL [message #196659] |
Fri, 06 October 2006 07:22  |
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 #196745 is a reply to message #196659] |
Fri, 06 October 2006 23:58  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
