| Performance issue in Plsql [message #417419] |
Sat, 08 August 2009 02:06  |
bond007
Messages: 64
Registered: March 2009
|
Member |
|
|
One of my procedure contains the following sql query which takes along time to fetch the result. Can we tune it anyway
select orv.vendor, orv.loc_code as dc, orv.transport_mode from od_routing_v orv , trans_mode tm where tm.trans_desc = orv.transport_mode and tm.trans_mode_key > 5
minus
select l1.loc_desc as vendor, l2.loc_desc as dc, tm.trans_desc from trans_lead_time tlt, location l1, location l2, trans_mode tm where tlt.source_location_key = l1.location_key and tlt.dest_location_key = l2.location_key and tlt.trans_mode_key = tm.trans_mode_key and tlt.coalition_key = 1 and tm.trans_mode_key > 5
[Updated on: Sat, 08 August 2009 02:08] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Performance issue in Plsql [message #417448 is a reply to message #417419] |
Sat, 08 August 2009 21:08  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If one or both of those row sources is very large, then you might find restructuring the query as a NOT IN () subquery will give a big improvement. This is because a NOT IN subquery can be resolved (sometimes) as a HASH join, whereas MINUS requires a sort of both row sources.
If you try the NOT IN subquery, you MUST, MUST, MUST ensure that the join columns on both the outer query and the subquery are non-nullable. This can be done with NOT NULL constraints on the database or with AND col IS NOT NULL clauses in the SQL. It is not sufficient for the columns to simply contain non-null values - it must be enforced, otherwise Oracle will not use a HASH join. Run your query through Explain Plan - if you don't see the keyword HASH JOIN ANTI (the ANTI is essential), don't bother running it.
Post your new query here if you need help. This time use CODE tags to format the query, otherwise we cannot read it properly.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
