where M.pro_id=? and DPM.Affil_id(+)=A.affil_id
and M.seqno=A.seqno and M.pubid=A.pubid
order by A.year desc;
So this is a query which is being used in my project and apparently it takes a long time for it to execute. Is there so way to optimize the where clause so that the query executes faster??
Also, what is the significance of the (+) symbol used??
This is an outer join.
what is the significance of the (+) symbol
Your query will return a row, even when a.affil is null (which means that there is no DPM record for that A record)
Look up outer join in the Oracle SQL reference for more info.
You are returning all rows. There is no filter in this query that restricts the data that is returned. You only have join conditions in your where clause.
apparently it takes a long time for it to execute
You use distinct, which means you are expecting duplicate rows. It might be a good idea to analyze why you get duplicated.
Look if proper indexes are in place for the fields you are using in the where clause.
Buy Oracle's SQL Tuning book. Lots of good advice.
Is the use of "distinct" going to cost me performance? Also, I have the M.pro_id=? condition right? My manager tells me the
and M.seqno=A.seqno and M.pubid=A.pubid" might take a long time to execute due the permutations and combinations possible out of it, is there a way to change this?
Yes. Oracle will have to sort the results by all fields, to allow it to remove duplicates.
Is the use of "distinct" going to cost me performance?
Only then can it start to serve the records to you.
Right. You are restricting the scope of the query.
I have the M.pro_id=? condition right?
Doesn't look too bad. You have table M (-with a restriction on it-), and thats joined to A.
...might take a long time to execute due the permutations and combinations possible out of it, is there a way to change this?
A has a join to OPM
A has an outer join to DPM.
If all comumns are properly indexed, and not too much records match any of the joins, it should not take too long.
Do an explain plan to see how oracle approaches your query.
Reordering the links might help in some cases (pseudocode):(secret: sometimes Oracle performs better if you put joins in both directions, e.g.:
and A.affil_id = OPM.affil_id
and OPM.affil_id = A.affil_id)
1. Check the database, see if there is a chance that duplicate values can exist in the resultset, if duplicates dont exist, eliminate "distinct" clause from the query.
2. Try and use joins in both the directions and see if it improves performance.
3. Try to reorder the query and see how that affects the performance.
Have I got this right?
Do an explain plan to see how oracle approaches your query
This means, I run this query on SQL?
Is this right?
Thanks so much for your detailed help jan! Really appreciate it. And yeah, I think I will get Oracle's SQL Tuning book as there are a lot of sql queries used in my project and the application processes thousands of records everyday, so there is serious room for performance improvement through improving the sql queries used.