Tune "where col in (val1,val2,val3) [message #446534] |
Tue, 09 March 2010 03:31 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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
|
|
|