Home » RDBMS Server » Performance Tuning » How can we write this query in a better or more efficeint way? (oracle 10g)
How can we write this query in a better or more efficeint way? [message #304468] Wed, 05 March 2008 05:52 Go to next message
vijay.nimmakuri@gmail.com
Messages: 10
Registered: February 2008
Location: inida
Junior Member

      SELECT country 
      INTO   l_syst_grp
      FROM   hr_locations
      WHERE  location_id = 
              (SELECT  location_id 
               FROM    per_business_groups 
               WHERE   business_group_id = 
                           (SELECT  business_group_id 
                            FROM org_organization_definitions 
                            WHERE organization_id = 
                                        (SELECT creation_organization_id 
                                         FROM gmd_recipes_b 
                                         WHERE recipe_id=l_recipe_id) ) );


Iam using this query in an outbound interface which is decreasing performance can any one Please suggest me a query
which can me more efficeint.

Can we use EXIST operator here, please suggest me

Thanks in advance,
we_ji.
Re: How can we write this query in a better or more efficeint way? [message #304505 is a reply to message #304468] Wed, 05 March 2008 07:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
http://www.orafaq.com/forum/t/84315/94420/

Read the above link and come back to us.

Regards

Raj
Re: How can we write this query in a better or more efficeint way? [message #304653 is a reply to message #304505] Thu, 06 March 2008 00:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When you specify COL = (subquery), this is called a "Scalar Sub-query".

The requirement for a scalar subquery is that it will return ZERO or ONE row. ALWAYS!

Even if your sub-queries are querying on a Unique or Primary Key, Oracle does not seem to know that it is guaranteed to return no more than one row. So it's always on the lookout for a ORA-01427: single-row subquery returns more than one row violation.

What does this mean? It means that some logical execution paths are not available. More specifically, it is not able to UNNEST the subqueries. See here for more on the perils of nesting.

You can help by either relaxing the requirement for one-and-one-only result, or by guaranteeing that one and one only row will be returned.

Try either of the following:
SELECT country 
      INTO   l_syst_grp
      FROM   hr_locations
      WHERE  location_id IN 
              (SELECT  location_id 
               FROM    per_business_groups 
               WHERE   business_group_id IN 
                           (SELECT  business_group_id 
                            FROM org_organization_definitions 
                            WHERE organization_id IN 
                                        (SELECT creation_organization_id 
                                         FROM gmd_recipes_b 
                                         WHERE recipe_id=l_recipe_id) ) );



SELECT country 
      INTO   l_syst_grp
      FROM   hr_locations
      WHERE  location_id = 
              (SELECT  MAX(location_id) 
               FROM    per_business_groups 
               WHERE   business_group_id = 
                           (SELECT  MAX(business_group_id) 
                            FROM org_organization_definitions 
                            WHERE organization_id = 
                                        (SELECT MAX(creation_organization_id) 
                                         FROM gmd_recipes_b 
                                         WHERE recipe_id=l_recipe_id) ) );


Ross Leishman
Re: How can we write this query in a better or more efficeint way? [message #307883 is a reply to message #304653] Thu, 20 March 2008 04:20 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
You can use EXISTS instead of = (select ...) and will be faster

That is

select xxx from A
where exists
(select * from B where B.col1=A.col1 and
exists
(select * from C where C.col2=B.Col2)
)
which should be faster. Ensure that Indexes are available on key columns.

Regards,

MSMallya

[Updated on: Thu, 20 March 2008 04:24]

Report message to a moderator

Re: How can we write this query in a better or more efficeint way? [message #307891 is a reply to message #307883] Thu, 20 March 2008 04:47 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@msmallya,

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: lockwait
Next Topic: About Oracle Database Link
Goto Forum:
  


Current Time: Mon Dec 05 11:12:02 CST 2016

Total time taken to generate the page: 0.25429 seconds