Database Reference
In-Depth Information
SELECT /*+ index(t i_n1) */ * FROM t WHERE
n1
IN (6, 8, 19, 28)
------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1579 |
| 1 |
INLIST ITERATOR
| | 1 | | 1579 |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
4
| 1710 | 1579 |
|* 3 | INDEX RANGE SCAN | I_N1 |
4
| 1710 | 1579 |
------------------------------------------------------------------------
3 - access(("N1"=6 OR "N1"=8 OR "N1"=19 OR "N1"=28))
SELECT /*+ index(t i_n4) */ * FROM t WHERE
n4
IN (6, 8, 19, 28)
----------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1579 |
| 1 |
INLIST ITERATOR
| | 1 | 1579 |
| 2 | TABLE ACCESS BY INDEX ROWID | T |
4
| 1579 |
| 3 | BITMAP CONVERSION TO ROWIDS| |
4
| 1579 |
|* 4 | BITMAP INDEX SINGLE VALUE | I_N4 |
4
| 3 |
----------------------------------------------------------------
4 - access(("N4"=6 OR "N4"=8 OR "N4"=19 OR "N4"=28))
DYNaMIC IN CONDItIONS WIth MaNY eXpreSSIONS
Oracle Database doesn't support
IN
conditions with more than 1,000 expressions. even though you could use
several disjunctive predicates as a workaround, from a performance point of view, there's a good reason for
setting a limit. hence, you should neither use
IN
conditions with many expressions nor disjunctive predicates,
to avoid the 1,000 expressions limit. In fact, long lists of expressions frequently lead to performance problems.
You should avoid them as much as possible. Instead, you should implement one of the following techniques:
•
Use an
IN
condition based on a subquery reading a (temporary) table.
•
Use an
IN
condition based on a subquery with a pipelined table function that takes as input a
nested table based on an object type and returns one row for each element.
•
Use a
MEMBER
condition that tests whether an element is member of a nested table based on an
object type.
the
dynamic_in_conditions.sql
script provides an example for each one of these techniques.
LIKE Conditions
The database engine is able to apply
LIKE
conditions as access predicates based on the character string preceeding
the first wild card only. As a result, provided that patterns don't begin with a wildcard (the underscore and the percent
characters),
LIKE
conditions are carried out in the same way as range conditions. Otherwise, a full table scan or a full
