Q

Tune this complex WHERE clause

How do we tune the following where clause?

This Content Component encountered an error

How do we tune the following where clause:

 (  shipper_id
     IN (select organization_id from orgrelation
          where act_as_to_org_id = 900009
       union
         select 900009 from dual)
 OR consignee_id
     IN (select organization_id from orgrelation
          where act_as_to_org_id = 900009 
       union
         select 900009 from dual)
 OR freight_payer_id
     IN (select organization_id from orgrelation
          where act_as_to_org_id = 900009 
       union
         select 900009 from dual)
 OR beneficial_owner_id
     IN (select organization_id from orgrelation
          where act_as_to_org_id = 900009 
       union
         select 900009 from dual)
 OR care_of_party_id
     IN (select organization_id from orgrelation
          where act_as_to_org_id = 900009 
       union
         select 900009 from dual)
 OR notify_party_id
     IN (select organization_id from orgrelation
          where act_as_to_org_id = 900009 
       union
         select 900009 from dual)
 OR property_owner_id
     IN (select organization_id from orgrelation
          where act_as_to_org_id = 900009 
       union
         select 900009 from dual)
 )

Let's look at this portion of the IN subquery --

select organization_id from orgrelation
  where act_as_to_org_id = 900009  

Notice that this is exactly the same query in every IN clause. Now, if this query returns exactly one row, then you can simplify your big complex WHERE condition like this --

 (  (select organization_id from orgrelation
          where act_as_to_org_id = 900009 )
      IN ( shipper_id
         , consignee_id
         , freight_payer_id
         , beneficial_owner_id
         , care_of_party_id
         , notify_party_id
         , property_owner_id  )
 OR 900009
      IN ( shipper_id
         , consignee_id
         , freight_payer_id
         , beneficial_owner_id
         , care_of_party_id
         , notify_party_id
         , property_owner_id  )
 )

This isn't actually "tuning" but it does simplify things a little.

This was first published in June 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close