A question on Speed

# Thread: A question on Speed

1. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## A question on Speed

I have to select data from a table based on a where clause, where the column should be equal to either of 2 values<BR><BR>i can go with <BR>where ....... and (ColumnName = 1 or ColumnName = 2) and ....<BR>OR<BR>where ....... and ColumnName IN (1, 2) and ....<BR><BR>Which would be faster and WHY.<BR><BR><BR>Please dont tell me to create a form and TRY it and see if it is faster but would there be a "tech" reason why one would be faster than the other.<BR><BR><BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
11,334

## Cocaine

Well, you said not to try it yourself, but I&#039;m not gonna... here&#039;s what you do:<BR><BR>Fire up ye ole query analyzer<BR>Look at each statement in the analysis<BR><BR>One may make the table scan longer than the other... who knows? Once you see what&#039;s happening underneath the sheets, you should be able to draw your own conclusions.

3. Tim Snyder Guest

## RE: Cocaine

There isn&#039;t any logic reasoning to asume one is faster than the other. As far as I can determine it is a question of how you debug and logic read your program.... No doubt someone will now prove me wrong!

4. Senior Member
Join Date
Dec 1969
Posts
3,921

## I believe "IN" is faster...

I asked an Oracle DBA once. It had something to do with the query optimizer making fewer reference checks on the column value in the index.

5. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## That is what i was told

But logially with an index on the ColumnName dont you think the = should be faster. Would it have to do a table scan for BOTH. I would think for the IN it would and NOT for the or = .<BR><BR>But when i check it shows that it does not do one for EITHER of them.<BR><BR>

6. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## Well you desert sand brain

they BOTH seem to do the same things "underneath the sheets" :)<BR><BR>wow till just now i never realised people STILL type underneath.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
3,921

## He said...

The each time the optimizer had to recompare the column name to the values in the index. However, using an IN clause, it only had to make that comparison once. He did mention that the issue would only become really noticable on larger tables.<BR><BR>This several years ago when I first got into Oracle. I wish I could remember his terminology better. I may post this to an Oracle DBA forum and ask for a more expert opinion.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•