Home » SQL & PL/SQL » SQL & PL/SQL » Why is the keyword 'in' so much slower than '=' ?
Why is the keyword 'in' so much slower than '=' ? [message #269606] Sun, 23 September 2007 16:56 Go to next message
emilSverige
Messages: 21
Registered: October 2006
Junior Member
I have a query like this:

select column1, column2, column3 from table1 t1 where
t1.username = 'a name'


The table has 500'000 rows, the query takes apx 0,4 seconds to execute.

I execute an almost identical query:

select column1, column2, column3 from table1 t1 where
t1.username = 'another name'


This is also executed in 0,4 seconds.

But if I try to merge these two queries into one, like this:

select column1, column2, column3, username from table1 t1 where
t1.username in ('a name', 'another name')


It takes as long as 17 seconds. Is that how it should work, or is there something wrong with the table/database?

I have the same performance problem if instead of 'in' I use 'or':
select column1, column2, column3, username from table1 t1 where
t1.username = 'a name' t1.username = 'another name'
Re: Why is the keyword 'in' so much slower than '=' ? [message #269607 is a reply to message #269606] Sun, 23 September 2007 19:12 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
You've been told repeatedly to follow the posting guideline which to decide to ignore.
Therefore I hope the rest of the regulars decide not to answer until you decide to meet us half way.
Re: Why is the keyword 'in' so much slower than '=' ? [message #269622 is a reply to message #269607] Sun, 23 September 2007 21:58 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Run an Explain Plan for the two queries and you'll see they are different. The fast one will be using an index, the slow one won't.

Make sure your statistics are up to date (run DBMS_STATS.GATHER_TABLE_STATS), and if that doesn't help, use a hint to force index usage.

Ross Leishman
Previous Topic: How can i print the DDL for "create table in PLSQL(function get table name) ?
Next Topic: fucntion/view/design question
Goto Forum:
  


Current Time: Mon Dec 05 10:46:40 CST 2016

Total time taken to generate the page: 0.10736 seconds