SQL> SQL> -- Use no_expand and use_concat hint to force "Inlist Iterator" or "Concatenation" SQL> SQL> drop table t1_a; Table dropped. SQL> create table t1_a as select object_name,object_type,created from dba_objects; Table created. SQL> create index t1_a_idx on t1_a(object_name) nologging; Index created. SQL> SQL> set autotrace on explain SQL> -- Table no analyzed SQL> select * from t1_a where object_name in ('TEST1','TEST5'); OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE CREATED ------------------ --------- TEST5 TABLE 24-AUG-02 TEST5 TABLE 16-APR-03 TEST1 TABLE 27-JUL-02 OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE CREATED ------------------ --------- TEST1 TABLE 05-AUG-02 TEST1 TABLE 03-OCT-02 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 CONCATENATION 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1_A' 3 2 INDEX (RANGE SCAN) OF 'T1_A_IDX' (NON-UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1_A' 5 4 INDEX (RANGE SCAN) OF 'T1_A_IDX' (NON-UNIQUE) SQL> select /*+ no_expand */ * from t1_a where object_name in ('TEST1','TEST5'); OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE CREATED ------------------ --------- TEST1 TABLE 27-JUL-02 TEST1 TABLE 05-AUG-02 TEST1 TABLE 03-OCT-02 OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE CREATED ------------------ --------- TEST5 TABLE 24-AUG-02 TEST5 TABLE 16-APR-03 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=179 Bytes=153 94) 1 0 INLIST ITERATOR 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1_A' (Cost=6 Card=179 Bytes=15394) 3 2 INDEX (RANGE SCAN) OF 'T1_A_IDX' (NON-UNIQUE) (Cost=2 Card=72) SQL> SQL> -- Force concatenation SQL> select * from test1 where c1 in ('G','f'); C1 C2 SUMC2 -- ---------- ---------- f 1 28 f 1 2008 G 7 7 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=24) 1 0 TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=4 Bytes=24) SQL> select /*+ use_concat */ * from test1 where c1 in ('G','f'); C1 C2 SUMC2 -- ---------- ---------- f 1 28 f 1 2008 G 7 7 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=4 Bytes=24) 1 0 CONCATENATION 2 1 TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=2 Bytes=12) 3 1 TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=2 Bytes=12) SQL> SQL> drop table t1_a; Table dropped. SQL> create table t1_a as select object_name,object_type,created from dba_objects; Table created. SQL> create index t1_a_idx on t1_a(object_name) nologging; Index created. SQL> analyze table t1_a compute statistics; Table analyzed. SQL> SQL> -- Inlist interator used when table is analyzed SQL> select * from t1_a where object_name in ('TEST1','TEST5'); OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE CREATED ------------------ --------- TEST1 TABLE 27-JUL-02 TEST1 TABLE 05-AUG-02 TEST1 TABLE 03-OCT-02 OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE CREATED ------------------ --------- TEST5 TABLE 24-AUG-02 TEST5 TABLE 16-APR-03 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=3 Bytes=114) 1 0 INLIST ITERATOR 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1_A' (Cost=5 Card=3 B ytes=114) 3 2 INDEX (RANGE SCAN) OF 'T1_A_IDX' (NON-UNIQUE) (Cost=2 Card=3) SQL> -- Force concatenation SQL> select /*+ use_concat */ * from t1_a where object_name in ('TEST1','TEST5'); OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE CREATED ------------------ --------- TEST5 TABLE 24-AUG-02 TEST5 TABLE 16-APR-03 TEST1 TABLE 27-JUL-02 OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE CREATED ------------------ --------- TEST1 TABLE 05-AUG-02 TEST1 TABLE 03-OCT-02 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=4 Bytes=152) 1 0 CONCATENATION 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1_A' (Cost=3 Card=2 B ytes=76) 3 2 INDEX (RANGE SCAN) OF 'T1_A_IDX' (NON-UNIQUE) (Cost=1 Card=2) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1_A' (Cost=3 Card=2 B ytes=76) 5 4 INDEX (RANGE SCAN) OF 'T1_A_IDX' (NON-UNIQUE) (Cost=1 Card=2) SQL> SQL> spool off