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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top