11G新特性SYSTEM分区测试

SYSTEM分区类型是11G中新推出的，如果一个表没有合适的分区键，可以考虑
使用SYSTEM分区，不过由于SYSTEM分区的很多限制，感觉用处不是很大。

SYSTEM 分区的创建方式非常简单，在PARTITION BY 后面紧跟着SYSTEM即可。

SQL> CREATE TABLE SYSTEM_PARTITION(ID INT,NAME CHAR(20))
  2  PARTITION BY SYSTEM
  3  (
  4  PARTITION P1 TABLESPACE USERS,
  5  PARTITION P2 TABLESPACE TEST
  6  )
  7  /

Table created.

SQL> SELECT TABLE_NAME,PARTITIONING_TYPE FROM USER_PART_TABLES;

TABLE_NAME                     PARTITION
------------------------------ ---------
A                              RANGE
CHILDREN                       REFERENCE
LIST_LIST                      LIST
SYSTEM_PARTITION               SYSTEM

由于没有分区键，因此直接插入数据，ORACLE无法映射到任何一个分区，会报错。

SQL> INSERT INTO SYSTEM_PARTITION VALUES(1,'YAN');
INSERT INTO SYSTEM_PARTITION VALUES(1,'YAN')
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method

必须得有分区名 才能插入。

SQL> INSERT INTO SYSTEM_PARTITION PARTITION (P1) VALUES(1,'YAN');

1 row created.

SQL> COMMIT;

Commit complete.

由于没有分区键，因此在查询的时候ORACLE无法进行分区排除，对表的查询将会扫描所有的分区。

SQL> SET AUTOT TRACEONLY EXP
SQL> SET LINESIZE 200
SQL> SELECT  * FROM SYSTEM_PARTITION WHERE ID=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 4026186747

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |     1 |    35 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION SYSTEM ALL|                  |     1 |    35 |     3   (0)| 00:00:01 |     1 |     2 |
|*  2 |   TABLE ACCESS FULL  | SYSTEM_PARTITION |     1 |    35 |     3   (0)| 00:00:01 |     1 |     2 |
---------------------------------------------------------------------------------------------------------

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

   2 - filter("ID"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> SELECT  * FROM SYSTEM_PARTITION WHERE NAME='YAN';

Execution Plan
----------------------------------------------------------
Plan hash value: 4026186747

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |     1 |    35 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION SYSTEM ALL|                  |     1 |    35 |     3   (0)| 00:00:01 |     1 |     2 |
|*  2 |   TABLE ACCESS FULL  | SYSTEM_PARTITION |     1 |    35 |     3   (0)| 00:00:01 |     1 |     2 |
---------------------------------------------------------------------------------------------------------

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

   2 - filter("NAME"='YAN')

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> SELECT  * FROM SYSTEM_PARTITION WHERE ID=1 AND NAME='YAN';

Execution Plan
----------------------------------------------------------
Plan hash value: 4026186747

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |     1 |    35 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION SYSTEM ALL|                  |     1 |    35 |     3   (0)| 00:00:01 |     1 |     2 |
|*  2 |   TABLE ACCESS FULL  | SYSTEM_PARTITION |     1 |    35 |     3   (0)| 00:00:01 |     1 |     2 |
---------------------------------------------------------------------------------------------------------

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

   2 - filter("ID"=1 AND "NAME"='YAN')

Note
-----
   - dynamic sampling used for this statement (level=2)

如果要进行分区排除，在查询的时候指定分区名。

SQL> SELECT * FROM SYSTEM_PARTITION PARTITION (P1) WHERE ID = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 935586943

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

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

   2 - filter("ID"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL>