执行计划中的关键字
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;