执行计划中的关键字
partition list single    --扫描单个分区
partition list iterator --连续扫描N个分区
partition list inlist    --不连续扫描N个分区
partition list full       --扫描全部分区？

测试结果

SQL> drop table test purge;
SQL> remark 创建测试数据
SQL> CREATE TABLE test (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  );
SQL> insert into test values(1);
SQL> insert into test values(2);
SQL> insert into test values(11);
SQL> insert into test values(12);
SQL> insert into test values(21);
SQL> insert into test values(22);
SQL> insert into test values(31);
SQL> insert into test values(32);
SQL> insert into test values(41);
SQL> insert into test values(42);
SQL> commit;
SQL> analyze table test compute statistics;
SQL> set autot on explain;
SQL> remark 扫描单个分区
SQL> select * from test where id=1;
                                 1

执行计划
----------------------------------------------------------
Plan hash value: 3030469310

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    1 |    2 | 14 (0)| 00:00:01 |    |    |
| 1 | PARTITION RANGE SINGLE|    |    1 |    2 | 14 (0)| 00:00:01 |    1 |    1 |
|*  2 | TABLE ACCESS FULL | test |    1 |    2 | 14 (0)| 00:00:01 |    1 |    1 |
-----------------------------------------------------------------------------------------------

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

2 - filter("ID"=1)

SQL> remark 不连续扫描多个分区
SQL> select * from test where id=1 or id=31;
                                 1
                                 31

执行计划
----------------------------------------------------------
Plan hash value: 3679944805

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    2 |    4 | 27 (0)| 00:00:01 |    |    |
| 1 | PARTITION RANGE INLIST|    |    2 |    4 | 27 (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 | TABLE ACCESS FULL | test |    2 |    4 | 27 (0)| 00:00:01 |KEY(I) |KEY(I) |
-----------------------------------------------------------------------------------------------

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

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

SQL> remark 连续扫描多个分区
SQL> select * from test where id between 1 and 32;
                                 1
                                 2
                                 11
                                 12
                                 21
                                 22
                                 31
                                 32

执行计划
----------------------------------------------------------
Plan hash value: 1049729311

-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |    |    9 | 18 | 51 (0)| 00:00:01 |    |    |
| 1 |  PARTITION RANGE ITERATOR|    |    9 | 18 | 51 (0)| 00:00:01 | 1 |    4 |
|*  2 | TABLE ACCESS FULL    | test |    9 | 18 | 51 (0)| 00:00:01 |    1 |    4 |
-------------------------------------------------------------------------------------------------

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

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

SQL> remark 扫描全部分区
SQL> select * from test ;
                                 1
                                 2
                                 11
                                 12
                                 21
                                 22
                                 31
                                 32
                                 41
                                 42

执行计划
----------------------------------------------------------
Plan hash value: 1271614465

--------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    | 10 | 20 | 64 (0)| 00:00:01 |    |    |
| 1 |  PARTITION RANGE ALL|    | 10 | 20 | 64 (0)| 00:00:01 | 1 |    5 |
| 2 | TABLE ACCESS FULL | test | 10 | 20 | 64 (0)| 00:00:01 |    1 |    5 |
--------------------------------------------------------------------------------------------

SQL> spool off;

出处:http://www.itpub.net/thread-1854955-1-1.html

ITPub博客

oracle分区表执行计划

最新文章