Thanks Jan! Is the use of "distinct" going to cost me performance? Also, I have the M.pro_id=? condition right? My manager tells me the "DPM.Affil_id(+)=A.affil_id and OPM.affil_id=A.affil_id 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?
Is the use of "distinct" going to cost me performance?
Yes. Oracle will have to sort the results by all fields, to allow it to remove duplicates. Only then can it start to serve the records to you.
I have the M.pro_id=? condition right?
Right. You are restricting the scope of the query.
...might take a long time to execute due the permutations and combinations possible out of it, is there a way to change this?
Doesn't look too bad. You have table M (-with a restriction on it-), and thats joined to A. 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) Regards, Jan
Joined: Mar 15, 2007
So, ways to optimize the query could be: 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.