OraFAQ Forum: Performance Tuning » PLSQL Query Tuning Suggestion/Feedback needed

Home » RDBMS Server » Performance Tuning » PLSQL Query Tuning Suggestion/Feedback needed (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PLSQL Query Tuning Suggestion/Feedback needed [message #390427]

Fri, 06 March 2009 05:28

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 Smile

----- 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 1893 times)

Report message to a moderator

Re: PLSQL Query Tuning Suggestion/Feedback needed [message #390512 is a reply to message #390427]

Fri, 06 March 2009 15:55

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

Report message to a moderator

Re: PLSQL Query Tuning Suggestion/Feedback needed [message #390528 is a reply to message #390512]

Sat, 07 March 2009 00:02

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I don't know. Seems to me we have not been told something.

You say that the first query has distinct to remove duplicates. But then you include a distinct in the second query as well.

I believe the following should be considered:

1) the distinct is not necessary in the second query?
2) unless there is only one row returned by the logic of the correlated subquery and this is enforced by database constraints, then these two queries join wise are not semantically equivelant. This is born out by your need to remove duplicates from the first query. I would expect the second query to perform faster because it does not require a sort in order to remove duplicates (assuming the distinct in the second query is an error).

Good luck, Kevin

Report message to a moderator

Re: PLSQL Query Tuning Suggestion/Feedback needed [message #390554 is a reply to message #390528]

Sat, 07 March 2009 05:52

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

Report message to a moderator

Re: PLSQL Query Tuning Suggestion/Feedback needed [message #390562 is a reply to message #390554]

Sat, 07 March 2009 08:59

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

Report message to a moderator

Previous Topic:	IOT secondary indexes SLOW performance
Next Topic:	Changes to the query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 07:08:12 CST 2024