From a performance perspective what is the better approach for huge tables:

using OR or using IN like in

select * from table where a = 1 or a = 2; 
     vs.
select * from table where a in (1,2)

asked 30 Oct '12, 07:15

Martin's gravatar image

Martin
8.6k116150237
accept rate: 14%


AFAIK, they are semantically equivalent and should be rewritten in the same way.

Cf. page 10 from this excellent whitepaper by Ani:

Query Processing Based on SQL Anywhere 12.0.1 Architecture, as referenced in this doc page.

permanent link

answered 30 Oct '12, 07:25

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

I thought so too (equivalent) anyway in real world I see totally different plans which are used...

Thanks for the link, I wasn't aware of this paper!

(30 Oct '12, 07:28) Martin
Replies hidden

Well, I'd just noticed it today and really like to share that link:)

(30 Oct '12, 07:47) Volker Barth

Can't say for sure in SQL Anywhere database, but you can check this link for a general behavior.

permanent link

answered 01 Nov '12, 07:47

Gandalf's gravatar image

Gandalf
1995716
accept rate: 50%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242
×90
×27

question asked: 30 Oct '12, 07:15

question was seen: 880 times

last updated: 01 Nov '12, 07:47