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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: