Home » RDBMS Server » Performance Tuning » Tune "where col in (val1,val2,val3) (oracle 10g)
Tune "where col in (val1,val2,val3) [message #446534] Tue, 09 March 2010 03:31 Go to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
Hi,

Is there any alternative way to tune the following query for performance improvement???

select col1,col2,col3
from tab_name
where col4 IN (val1,val2,val3);


Above query will not fire the index due to IN operator.

So how we can approach to improve the performance in such case?

Best Regards,
Harshal N.
Re: Tune "where col in (val1,val2,val3) [message #446535 is a reply to message #446534] Tue, 09 March 2010 03:36 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
In does not prevent index usage:
SQL> create table index_test as select rownum a, rownum b from dual connect by level < 100000;

Table created.

SQL> create index index_test_idx on index_test(a);

Index created.

SQL> explain plan for select * from index_test where a in (1,50,200);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 600866337

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     2 |    52 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| INDEX_TEST     |     2 |    52 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | INDEX_TEST_IDX |   391 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=1 OR "A"=50 OR "A"=200)

Note
-----
   - dynamic sampling used for this statement

19 rows selected.

SQL> 


What's the current explain?
What indexes are on the table?
How many rows in the table?

EDIT: typo

[Updated on: Tue, 09 March 2010 03:36]

Report message to a moderator

Re: Tune "where col in (val1,val2,val3) [message #446542 is a reply to message #446534] Tue, 09 March 2010 04:31 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Above query will not fire the index due to IN operator.

Where do you find this assertion? Or is it just a guess from your part that you turn into a certainty?

Regards
Michel
Previous Topic: Resource busy during stats gather
Next Topic: sga
Goto Forum:
  


Current Time: Tue Sep 27 17:55:15 CDT 2016

Total time taken to generate the page: 0.11309 seconds