请教执行计划 partition list single和partition list iterator分别是啥意思啊？

earth2mars

请教执行计划 partition list single和partition list iterator分别是啥意思啊？
谢谢

earth2mars

partition list single  --扫描单个分区
partition list iterator --扫描N个分区
partition list inlist     --扫描N个分区
partition list full  -- 扫描全部分区？

那么partition list iterator 和partition list inlist有什么区别呢？

dingjun123

list iterator应该是in list条件能走索引的时候用
inlist应该是走不了索引的时候用
full扫描所有分区
你可以走个例子试试，迭代器是走到索引的时候效率好

casper0511

SQL> CREATE TABLE tt (id integer) PARTITION BY RANGE (id)
  2  (PARTITION P1 VALUES LESS THAN (10),
  3   PARTITION P2 VALUES LESS THAN (20),
  4   PARTITION P3 VALUES LESS THAN (30),
  5   PARTITION P4 VALUES LESS THAN (40),
  6   PARTITION P5 VALUES LESS THAN (MAXVALUE)
  7  );

Table created.

SQL> insert into tt values(1);

1 row created.

SQL> insert into tt values(2);

1 row created.

SQL> insert into tt values(11);

1 row created.

SQL> insert into tt values(12);

1 row created.

SQL> insert into tt values(21);

1 row created.

SQL> insert into tt values(22);

1 row created.

SQL> insert into tt values(31);

1 row created.

SQL> insert into tt values(32);

1 row created.

SQL> insert into tt values(41);

1 row created.

SQL> insert into tt values(42);

1 row created.

SQL> commit;

Commit complete.

SQL> analyze table tt compute statistics;

Table analyzed.

SQL> set autot on explain;

SQL> select * from tt where id=1;

        ID
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 3030469310
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |Pstart| Pstop |
-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |      |     1 |     2 |     3   (0)| 00:00:01 |      |       |

|   1 |  PARTITION RANGE SINGLE|      |     1 |     2 |     3   (0)| 00:00:01 |    1 |     1 |

|*  2 |   TABLE ACCESS FULL    | TT   |     1 |     2 |     3   (0)| 00:00:01 |    1 |     1 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=1)

SQL> select * from tt where id=1 or id=31;

        ID
----------
         1
        31


Execution Plan
----------------------------------------------------------
Plan hash value: 3679944805
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |Pstart| Pstop |
-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |      |     2 |     4 |     4   (0)| 00:00:01 |      |       |

|   1 |  PARTITION RANGE INLIST|      |     2 |     4 |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |

|*  2 |   TABLE ACCESS FULL    | TT   |     2 |     4 |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |

-----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=1 OR "ID"=31)

SQL> select * from tt where id between 1 and 32;

        ID
----------
         1
         2
        11
        12
        21
        22
        31
        32

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1049729311
-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time| Pstart| Pstop |
-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT         |      |     9 |    18 |     6   (0)| 00:00:01|       |       |

|   1 |  PARTITION RANGE ITERATOR|      |     9 |    18 |     6   (0)| 00:00:01|     1 |     4 |

|*  2 |   TABLE ACCESS FULL      | TT   |     9 |    18 |     6   (0)| 00:00:01|     1 |     4 |

-------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"<=32 AND "ID">=1)

tom0732

partition list iterator --扫描N个分区,连续扫描N个分区,
partition list inlist     --扫描N个分区,不连续扫描N个分区

风铃中の鬼

学习了。。4楼的测试，不加where条件就会变成partition list full 了吧