These are two different approaches to how Oracle can use an index for predicates such as
where indexed_column in (:1, :2, :3)
To use the index, rather than doing a full table scan, the CONCATENATION
approach is to transform the query into a UNION-ALL of several queries (in this
case 3) and those queries each use the index to lookup just one value. The
disadvantage is that there is a distinct row source for each inlist value. Thus
this approach uses frame memory in proportion with the number of values. It can
also constrain some parts of the execution plan to be repeated in each of the
concatenated queries.
The inlist iterator does the same in a single row source. Therefore it is better in every respect.
I have a question after having explained the same query twice using hints. In one plan I can see an operator named INLIST ITERATOR and in the other plan I can see a CONCATENATION operator. Are there any differences in the work to execute the query?
Operation Options Objects Other_tag
----------------------------------- --------------- ------------------------- ----------------------
SELECT STATEMENT
VIEW VI_CRSALE_03
UNION-ALL PARTITION
CONCATENATION
TABLE ACCESS BY INDEX ROWID TBCRSALE_200009
INDEX RANGE SCAN IX_CRSALE_200009_04
TABLE ACCESS BY INDEX ROWID TBCRSALE_200009
INDEX RANGE SCAN IX_CRSALE_200009_04
TABLE ACCESS BY INDEX ROWID TBCRSALE_200009
INDEX RANGE SCAN IX_CRSALE_200009_04
CONCATENATION
TABLE ACCESS BY INDEX ROWID TBCRSALE_200008
INDEX RANGE SCAN IX_CRSALE_200008_04
TABLE ACCESS BY INDEX ROWID TBCRSALE_200008
INDEX RANGE SCAN IX_CRSALE_200008_04
TABLE ACCESS BY INDEX ROWID TBCRSALE_200008
INDEX RANGE SCAN IX_CRSALE_200008_04
CONCATENATION
TABLE ACCESS BY INDEX ROWID TBCRSALE_200007
INDEX RANGE SCAN IX_CRSALE_200007_04
TABLE ACCESS BY INDEX ROWID TBCRSALE_200007
INDEX RANGE SCAN IX_CRSALE_200007_04
TABLE ACCESS BY INDEX ROWID TBCRSALE_200007
INDEX RANGE SCAN IX_CRSALE_200007_04
CONCATENATION
TABLE ACCESS BY INDEX ROWID TBCRSALE_200006
INDEX RANGE SCAN IXX_CRSALE_200006_04
TABLE ACCESS BY INDEX ROWID TBCRSALE_200006
INDEX RANGE SCAN IXX_CRSALE_200006_04
TABLE ACCESS BY INDEX ROWID TBCRSALE_200006
INDEX RANGE SCAN IXX_CRSALE_200006_04
Operation Options Objects Other_tag
----------------------------------- --------------- ------------------------- ----------------------
SELECT STATEMENT
VIEW VI_CRSALE_03
UNION-ALL PARTITION
INLIST ITERATOR CONCATENATED
TABLE ACCESS BY INDEX ROWID TBCRSALE_200009
INDEX RANGE SCAN IX_CRSALE_200009_04
INLIST ITERATOR CONCATENATED
TABLE ACCESS BY INDEX ROWID TBCRSALE_200008
INDEX RANGE SCAN IX_CRSALE_200008_04
INLIST ITERATOR CONCATENATED
TABLE ACCESS BY INDEX ROWID TBCRSALE_200007
INDEX RANGE SCAN IX_CRSALE_200007_04
INLIST ITERATOR CONCATENATED
TABLE ACCESS BY INDEX ROWID TBCRSALE_200006
INDEX RANGE SCAN IXX_CRSALE_200006_04
TAB_NM IDX_NM ORD COL_NM UNI_GB
-------------------- ------------------------- ---- -------------------- ---------
TBCRSALE_200009 IX_CRSALE_200009_04 1 REG_DATE NO-UNIQUE
2 CMUSE_SALE_CLSS NO-UNIQUE