Home » RDBMS Server » Performance Tuning » PLSQL Query Tuning Suggestion/Feedback needed (Oracle 10G)
PLSQL Query Tuning Suggestion/Feedback needed [message #390427] |
Fri, 06 March 2009 05:28 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
bahubcd
Messages: 40 Registered: July 2007 Location: Bangalore
|
Member |
|
|
Hi all,
I was executing a requirement to fetch data from various tables and display the same in Oracle Reports. In the Process I formulated two queries for the same functionality. Can some one please have a look at them and let me know which one is better as the volume of data is going to be huge in the system. I am also attaching the cost alongwith the queries. Thanks for the response and feed backs in advance.
Please also note that the first Query has the distinct keyword to eliminate the duplicate rows.
Also please let me know if the better of the two queries can be bettered
----- Query 1
select distinct raspgd.store_proximity_id, str.store, str.store_name, adr.city, adr.state,rgn.area,dst.region,str.district,str.store_open_date
from store str,addr adr, district dst,region rgn,add_type_module atm,rac_asr_store_prox_groupdet raspgd
where str.store = raspgd.store
and adr.addr_type = atm.address_type
and adr.module = 'ST'
and atm.primary_ind = 'Y'
and atm.mandatory_ind = 'Y'
and str.store = adr.key_value_1
and str.district = dst.district
and dst.region = rgn.region
order by raspgd.store_proximity_id, str.store; --- Query 2
select distinct raspgd.store_proximity_id,str.store, str.store_name, adr.city, adr.state,rgn.area,dst.region,str.district,str.store_open_date
from store str,addr adr, district dst,region rgn,rac_asr_store_prox_groupdet raspgd
where str.store = raspgd.store
and adr.module = 'ST'
and str.store = adr.key_value_1
and str.district = dst.district
and dst.region = rgn.region
and exists (select 1
from add_type_module atm
where adr.addr_type = atm.address_type
and atm.primary_ind = 'Y'
and atm.mandatory_ind = 'Y')
order by raspgd.store_proximity_id, str.store; ----
-
Attachment: Query.JPG
(Size: 126.62KB, Downloaded 1828 times)
|
|
|
Re: PLSQL Query Tuning Suggestion/Feedback needed [message #390512 is a reply to message #390427] |
Fri, 06 March 2009 15:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
It doesn't look like your query has any highly selective filters - the WHERE clauses all look pretty generic, but you would be the best judge.
The golden rule is this:
Ross's golden rule |
If you were to remove all of the filtering WHERE clauses (not the joins) then you would get more rows, right? If you then add them back in one-by-one, which one(s) would filter out the most rows from the final query?
Whichever filter removes the most rows, that is the table you want joined first in the query.
|
So, if (say) adr.module = 'ST' would reduce the total rows returned by the query by 90%, then ADR is a table that you want to be included early in the join.
Now, your two queries are not that much different. Oracle can (and does) perform EXISTS sub-queries as a join, so there is very little difference between joining and sub-querying.
The big difference is that Oracle will tend to perform the sub-query last (you can see this behaviour in your example). That might be good if the sub-query filtered the least rows, but it would be bad if it filtered the most rows.
If there is NO FUNCTIONAL DIFFERENCE between the two, then I prefer the join method because it gives the optimiser a better chance of getting it right, and it gives you more tuning opportunities.
So, in my opinion:- Stick with the join
- Decide which table has the most selective filters (if any)
- If the plan is not starting with that table, use a LEADING hint.
Ross Leishman
|
|
|
|
Re: PLSQL Query Tuning Suggestion/Feedback needed [message #390554 is a reply to message #390528] |
Sat, 07 March 2009 05:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Kevin Meade wrote on Sat, 07 March 2009 17:02 | I don't know. Seems to me we have not been told something.
|
Funny you should say that Kevin, I was thinking the same thing. I see HASH JOIN ANTI in both queries, but I don't see a NOT IN or NOT EXISTS sub-query to substantiate it.
Smells fishy.
Ross Leishman
|
|
|
Re: PLSQL Query Tuning Suggestion/Feedback needed [message #390562 is a reply to message #390554] |
Sat, 07 March 2009 08:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
alexzeng
Messages: 133 Registered: August 2005 Location: alexzeng.wordpress.com
|
Senior Member |
|
|
rleishman wrote on Sat, 07 March 2009 05:52 | Kevin Meade wrote on Sat, 07 March 2009 17:02 | I don't know. Seems to me we have not been told something.
|
Funny you should say that Kevin, I was thinking the same thing. I see HASH JOIN ANTI in both queries, but I don't see a NOT IN or NOT EXISTS sub-query to substantiate it.
Smells fishy.
Ross Leishman
|
Maybe views are used in the query. He should let us know the tables/views definition.
Regards,
Alex
|
|
|
Goto Forum:
Current Time: Fri Sep 06 14:34:41 CDT 2024
|