Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

0

积分

0

好友

1

主题
1#
发表于 2012-12-13 19:49:50 | 查看: 5544| 回复: 3
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

不是很明白,请拍砖



2#
发表于 2012-12-13 20:31:50

  1. SQL> drop table concat_test;
  2. create table concat_test (col1 number, col2 number,  col3 number);

  3. set autot trace explain

  4. create index ct1 on concat_test(col1);
  5. create index ct2 on concat_test(col2);drop table concat_test
  6.            *
  7. ERROR at line 1:
  8. ORA-00942: table or view does not exist


  9. SQL>
  10. Table created.

  11. SQL> SQL> SQL> SQL>
  12. Index created.

  13. SQL>

  14. Index created.

  15. SQL> select                   * from concat_test where col1 =1 or col2 =1;

  16. no rows selected


  17. Execution Plan
  18. ----------------------------------------------------------
  19. Plan hash value: 1803790311

  20. ---------------------------------------------------------------------------------
  21. | Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  22. ---------------------------------------------------------------------------------
  23. |   0 | SELECT STATEMENT  |             |     1 |    39 |     2   (0)| 00:00:01 |
  24. |*  1 |  TABLE ACCESS FULL| CONCAT_TEST |     1 |    39 |     2   (0)| 00:00:01 |
  25. ---------------------------------------------------------------------------------

  26. Predicate Information (identified by operation id):
  27. ---------------------------------------------------

  28.    1 - filter("COL1"=1 OR "COL2"=1)

  29. Note
  30. -----
  31.    - dynamic sampling used for this statement (level=2)


  32. Statistics
  33. ----------------------------------------------------------
  34.           0  recursive calls
  35.           0  db block gets
  36.           3  consistent gets
  37.           0  physical reads
  38.           0  redo size
  39.         464  bytes sent via SQL*Net to client
  40.         512  bytes received via SQL*Net from client
  41.           1  SQL*Net roundtrips to/from client
  42.           0  sorts (memory)
  43.           0  sorts (disk)
  44.           0  rows processed
  45.                   
  46.                   
  47.                   
  48. SQL> select /*+ use_concat */ * from concat_test where col1 =1 or col2 =1;

  49. no rows selected


  50. Execution Plan
  51. ----------------------------------------------------------
  52. Plan hash value: 1266984604

  53. --------------------------------------------------------------------------------------------
  54. | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  55. --------------------------------------------------------------------------------------------
  56. |   0 | SELECT STATEMENT             |             |     2 |    78 |     2   (0)| 00:00:01 |
  57. |   1 |  CONCATENATION               |             |       |       |            |          |
  58. |   2 |   TABLE ACCESS BY INDEX ROWID| CONCAT_TEST |     1 |    39 |     1   (0)| 00:00:01 |
  59. |*  3 |    INDEX RANGE SCAN          | CT2         |     1 |       |     1   (0)| 00:00:01 |
  60. |*  4 |   TABLE ACCESS BY INDEX ROWID| CONCAT_TEST |     1 |    39 |     1   (0)| 00:00:01 |
  61. |*  5 |    INDEX RANGE SCAN          | CT1         |     1 |       |     1   (0)| 00:00:01 |
  62. --------------------------------------------------------------------------------------------

  63. Predicate Information (identified by operation id):
  64. ---------------------------------------------------

  65.    3 - access("COL2"=1)
  66.    4 - filter(LNNVL("COL2"=1))
  67.    5 - access("COL1"=1)

  68. Note
  69. -----
  70.    - dynamic sampling used for this statement (level=2)


  71. Statistics
  72. ----------------------------------------------------------
  73.           1  recursive calls
  74.           0  db block gets
  75.           7  consistent gets
  76.           0  physical reads
  77.           0  redo size
  78.         464  bytes sent via SQL*Net to client
  79.         512  bytes received via SQL*Net from client
  80.           1  SQL*Net roundtrips to/from client
  81.           0  sorts (memory)
  82.           0  sorts (disk)
  83.           0  rows processed
  84.                   


  85. SQL> select /*+ use_concat */ * from concat_test where col1 =1 or col1 =2;

  86. no rows selected


  87. Execution Plan
  88. ----------------------------------------------------------
  89. Plan hash value: 2628373517

  90. --------------------------------------------------------------------------------------------
  91. | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  92. --------------------------------------------------------------------------------------------
  93. |   0 | SELECT STATEMENT             |             |     1 |    39 |     0   (0)| 00:00:01 |
  94. |   1 |  INLIST ITERATOR             |             |       |       |            |          |
  95. |   2 |   TABLE ACCESS BY INDEX ROWID| CONCAT_TEST |     1 |    39 |     0   (0)| 00:00:01 |
  96. |*  3 |    INDEX RANGE SCAN          | CT1         |     1 |       |     0   (0)| 00:00:01 |
  97. --------------------------------------------------------------------------------------------

  98. Predicate Information (identified by operation id):
  99. ---------------------------------------------------

  100.    3 - access("COL1"=1 OR "COL1"=2)

  101. Note
  102. -----
  103.    - dynamic sampling used for this statement (level=2)


  104. Statistics
  105. ----------------------------------------------------------
  106.           1  recursive calls
  107.           0  db block gets
  108.           2  consistent gets
  109.           0  physical reads
  110.           0  redo size
  111.         464  bytes sent via SQL*Net to client
  112.         512  bytes received via SQL*Net from client
  113.           1  SQL*Net roundtrips to/from client
  114.           0  sorts (memory)
  115.           0  sorts (disk)
  116.           0  rows processed






复制代码
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.

回复 只看该作者 道具 举报

3#
发表于 2012-12-13 20:34:35
跟多关于 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

回复 只看该作者 道具 举报

4#
发表于 2012-12-13 20:45:47
意思是INLIST ITERATOR 在做index access  时根据INDEX VALUE会对每一条INLIST里面的值做比对,最后返回值,所以做一次INDEX SCAN

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

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-26 03:22 , Processed in 0.060125 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569