关于INLIST ITERATOR 和 CONCATENATION 的区别

onlyring

google了一下，找到了Steve Adams 的解释：
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.
http://www.ixora.com.au/q+a/0009/21225019.htm

似乎inlist iterator 只需要扫描一遍就可以完成，而CONCATENATION 需要转换成多个UNION ALL

但似乎Tom说这两个东西是一样的，见Followup   August 7, 2008 - 12pm Central time zone:
forget the "inlist" stuff for a moment, it won't matter - concatenate + many range scans = inlist iterator - they are the same (inlist interator is just a more "compact" way of saying it - else - they are the SAME when executed, each will hit the index N times for a range scan)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8906695863428

不是很明白，请拍砖

Liu Maclean(刘相兵

1. 
   
2. SQL> drop table concat_test;
   
3. create table concat_test (col1 number, col2 number,  col3 number);
   
4. 
   
5. set autot trace explain
   
6. 
   
7. create index ct1 on concat_test(col1);
   
8. create index ct2 on concat_test(col2);drop table concat_test
   
9.            *
   
10. ERROR at line 1:
    
11. ORA-00942: table or view does not exist
    
12. 
    
13. 
    
14. SQL>
    
15. Table created.
    
16. 
    
17. SQL> SQL> SQL> SQL>
    
18. Index created.
    
19. 
    
20. SQL>
    
21. 
    
22. Index created.
    
23. 
    
24. SQL> select                   * from concat_test where col1 =1 or col2 =1;
    
25. 
    
26. no rows selected
    
27. 
    
28. 
    
29. Execution Plan
    
30. ----------------------------------------------------------
    
31. Plan hash value: 1803790311
    
32. 
    
33. ---------------------------------------------------------------------------------
    
34. | Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    
35. ---------------------------------------------------------------------------------
    
36. |   0 | SELECT STATEMENT  |             |     1 |    39 |     2   (0)| 00:00:01 |
    
37. |*  1 |  TABLE ACCESS FULL| CONCAT_TEST |     1 |    39 |     2   (0)| 00:00:01 |
    
38. ---------------------------------------------------------------------------------
    
39. 
    
40. Predicate Information (identified by operation id):
    
41. ---------------------------------------------------
    
42. 
    
43.    1 - filter("COL1"=1 OR "COL2"=1)
    
44. 
    
45. Note
    
46. -----
    
47.    - dynamic sampling used for this statement (level=2)
    
48. 
    
49. 
    
50. Statistics
    
51. ----------------------------------------------------------
    
52.           0  recursive calls
    
53.           0  db block gets
    
54.           3  consistent gets
    
55.           0  physical reads
    
56.           0  redo size
    
57.         464  bytes sent via SQL*Net to client
    
58.         512  bytes received via SQL*Net from client
    
59.           1  SQL*Net roundtrips to/from client
    
60.           0  sorts (memory)
    
61.           0  sorts (disk)
    
62.           0  rows processed
    
63.                   
    
64.                   
    
65.                   
    
66. SQL> select /*+ use_concat */ * from concat_test where col1 =1 or col2 =1;
    
67. 
    
68. no rows selected
    
69. 
    
70. 
    
71. Execution Plan
    
72. ----------------------------------------------------------
    
73. Plan hash value: 1266984604
    
74. 
    
75. --------------------------------------------------------------------------------------------
    
76. | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    
77. --------------------------------------------------------------------------------------------
    
78. |   0 | SELECT STATEMENT             |             |     2 |    78 |     2   (0)| 00:00:01 |
    
79. |   1 |  CONCATENATION               |             |       |       |            |          |
    
80. |   2 |   TABLE ACCESS BY INDEX ROWID| CONCAT_TEST |     1 |    39 |     1   (0)| 00:00:01 |
    
81. |*  3 |    INDEX RANGE SCAN          | CT2         |     1 |       |     1   (0)| 00:00:01 |
    
82. |*  4 |   TABLE ACCESS BY INDEX ROWID| CONCAT_TEST |     1 |    39 |     1   (0)| 00:00:01 |
    
83. |*  5 |    INDEX RANGE SCAN          | CT1         |     1 |       |     1   (0)| 00:00:01 |
    
84. --------------------------------------------------------------------------------------------
    
85. 
    
86. Predicate Information (identified by operation id):
    
87. ---------------------------------------------------
    
88. 
    
89.    3 - access("COL2"=1)
    
90.    4 - filter(LNNVL("COL2"=1))
    
91.    5 - access("COL1"=1)
    
92. 
    
93. Note
    
94. -----
    
95.    - dynamic sampling used for this statement (level=2)
    
96. 
    
97. 
    
98. Statistics
    
99. ----------------------------------------------------------
    
100.           1  recursive calls
     
101.           0  db block gets
     
102.           7  consistent gets
     
103.           0  physical reads
     
104.           0  redo size
     
105.         464  bytes sent via SQL*Net to client
     
106.         512  bytes received via SQL*Net from client
     
107.           1  SQL*Net roundtrips to/from client
     
108.           0  sorts (memory)
     
109.           0  sorts (disk)
     
110.           0  rows processed
     
111.                   
     
112. 
     
113. 
     
114. SQL> select /*+ use_concat */ * from concat_test where col1 =1 or col1 =2;
     
115. 
     
116. no rows selected
     
117. 
     
118. 
     
119. Execution Plan
     
120. ----------------------------------------------------------
     
121. Plan hash value: 2628373517
     
122. 
     
123. --------------------------------------------------------------------------------------------
     
124. | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
     
125. --------------------------------------------------------------------------------------------
     
126. |   0 | SELECT STATEMENT             |             |     1 |    39 |     0   (0)| 00:00:01 |
     
127. |   1 |  INLIST ITERATOR             |             |       |       |            |          |
     
128. |   2 |   TABLE ACCESS BY INDEX ROWID| CONCAT_TEST |     1 |    39 |     0   (0)| 00:00:01 |
     
129. |*  3 |    INDEX RANGE SCAN          | CT1         |     1 |       |     0   (0)| 00:00:01 |
     
130. --------------------------------------------------------------------------------------------
     
131. 
     
132. Predicate Information (identified by operation id):
     
133. ---------------------------------------------------
     
134. 
     
135.    3 - access("COL1"=1 OR "COL1"=2)
     
136. 
     
137. Note
     
138. -----
     
139.    - dynamic sampling used for this statement (level=2)
     
140. 
     
141. 
     
142. Statistics
     
143. ----------------------------------------------------------
     
144.           1  recursive calls
     
145.           0  db block gets
     
146.           2  consistent gets
     
147.           0  physical reads
     
148.           0  redo size
     
149.         464  bytes sent via SQL*Net to client
     
150.         512  bytes received via SQL*Net from client
     
151.           1  SQL*Net roundtrips to/from client
     
152.           0  sorts (memory)
     
153.           0  sorts (disk)
     
154.           0  rows processed
     
155. 
     
156. 
     
157. 
     
158. 
     
159. 
     
160. 
     

复制代码

The inlist iterator is CBO functionality that can iterate over the
   elements of an IN list and drive the query using each inlist value
   in turn taking advantage of any index on the column.

Liu Maclean(刘相兵

跟多关于 INLIST和 CONCATENATION 请参考


Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)
Part Number A96533-02       

Home       
Book List       
Contents       
Index       
Master Index       
Feedback
                View PDF
2
Optimizer Operations

onlyring

意思是INLIST ITERATOR 在做index access  时根据INDEX VALUE会对每一条INLIST里面的值做比对，最后返回值，所以做一次INDEX SCAN

而CONCATENATION会做EXPAND，把语句转换成UNION ALL 形式，对应每一个LINLIST里面的值对应一个语句，所以INDEX ACCESS的次数和INLIST 里面的值的数量相同。
不知道我理解的对不对