執行計劃中的關鍵字
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

oracle分割槽表執行計劃

相關文章