This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
DPM.Affil_id(+)=A.affil_id what is the significance of the (+) symbol
This is an outer join. 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.
apparently it takes a long time for it to execute
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.
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.
OCUP UML fundamental and ITIL foundation
Joined: Mar 15, 2007
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.