The IN-list iterator operation simply executes an indexed access for each item in a list, whether specified with IN or OR. The "IN (list)" construction is just a way to let you specify multiple OR conditions without repeating the left-hand expression.
Can you show an example of an IN (list) query not being converted to use OR?
Also can you demonstrate the scenario where an OR condition causes queries to be run several times?
Code:SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jul 21 14:28:29 2005 SQL> CREATE TABLE testit AS 2 SELECT object_id, object_name FROM all_objects; Table created. SQL> CREATE INDEX testit_ix ON testit(object_id); Index created. SQL> analyze table testit compute statistics for table for all columns for all indexes; Table analyzed. SQL> SELECT COUNT(*) FROM testit WHERE object_id IN (1,2,3) 2 SQL> @xplan -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | INLIST ITERATOR | | | | | | |* 3 | INDEX RANGE SCAN| TESTIT_IX | 3 | 12 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3) SQL> SELECT COUNT(*) FROM testit WHERE object_name IN ('x','y','z') 2 SQL> @xplan ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 17 | 6 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 17 | | | |* 2 | TABLE ACCESS FULL| TESTIT | 5 | 85 | 6 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_NAME"='x' OR "OBJECT_NAME"='y' OR "OBJECT_NAME"='z') SQL> SELECT COUNT(*) FROM testit WHERE object_id = 1 OR object_id = 2 OR object_id = 3 2 SQL> @xplan -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | INLIST ITERATOR | | | | | | |* 3 | INDEX RANGE SCAN| TESTIT_IX | 3 | 12 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3)
Reply With Quote