CODEWITHSUNDEEP
sqlopenedge
Brett Ryan
Brett Ryan

Reputation: 28255

What is more efficient, an in clause or an or clause, and does the index still get used?

Suppose that I have a customers table which has an account_number and a billing_account_number, if I link to another table which I need to test for either, I could use the following or clause:

select c.name
  from customers c
 ,left join credit_terms ct on account = c.account_number
                            or account = c.billing_account

However, I have found that the following works equally

select c.name
  from customers c
 ,left join credit_terms ct on account in (c.account_number, c.billing_account)

Now suppose that credit_terms.account is indexed, would that index get used in both cases? Are both statements just as equal? Is there any cost associated with one or the other?

I do apologise for being naive though I am fairly new to moderate levels of SQL.

Upvotes: 1

Views: 213

Answers (2)

Tom Bascom
Tom Bascom

Reputation: 14020

OpenEdge uses a cost based optimizer so the particular query plan will be influenced by the statistics relevant to the query -- it may, or may not, use the indexes that you expect depending on what the optimizer knows about the data.

This knowledgebase article explains OpenEdge's SQL query plan:

http://progresscustomersupport-survey.force.com/ProgressKB/articles/Article/P62658?retURL=%2Fapex%2FProgressKBHome&popup=false

You must also periodically update SQL statistics for the optimizer to do a good job:

http://progresscustomersupport-survey.force.com/ProgressKB/articles/Article/20992?retURL=%2Fapex%2Fprogresskbsearch&popup=false

Upvotes: 1

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

I have no knowledge of openedge, but I do not think either one will use indexes on account. The 2 of them are basically the same.

In case you want to use the index I would do something like this:

select c.name
  from customers c
  left join credit_terms ct on ct.account = c.account_number
union
select c.name
  from customers c
  left join credit_terms ct on ct.account = c.billing_account

Then again, this is somehow speculating, as I say again I have no knowledge of openedge.

Upvotes: 0

Related Questions