Oracle11g新特性系統分區表
在11g中有個新特性是系統的分區表，下面來做個實驗：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> drop table S_P_TEST purge;
SQL> CREATE TABLE S_P_TEST
(
col1 number,
name varchar2(100)
)
PARTITION BY SYSTEM
(
PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);
--插入時要指明分區
SQL> insert into S_P_TEST select object_id,object_name from dba_objects;
insert into S_P_TEST select object_id,object_name from dba_objects
*
第 1 行出現錯誤:
ORA-14701: 對於按“系統”方法進行分區的表, 必須對 DML 使用分區擴展名或綁定變量
SQL> insert into S_P_TEST partition(p1) select object_id,object_name from dba_objects
where object_type='TABLE';
已創建2115行。
SQL> insert into S_P_TEST partition(p2) select object_id,object_name from dba_objects
where object_type='INDEX';
已創建2888行。
SQL> commit;
SQL> select count(1) from S_P_TEST;
COUNT(1)
----------
5003
SQL> select count(1) from S_P_TEST partition(p1);
COUNT(1)
----------
2115
SQL> select count(1) from S_P_TEST partition(p2);
COUNT(1)
----------
2888
--相同的數據可以插入到不同的分區中，這說明數據和分區沒有任何關系
SQL> insert into S_P_TEST partition(p3) values(1,'aa');
SQL> insert into S_P_TEST partition(p4) values(1,'aa');
SQL> commit;
SQL> select * from S_P_TEST partition(p3);
COL1 NAME
---------- ------------------------------------------
1 aa
SQL> select * from S_P_TEST partition(p4);
COL1 NAME
---------- -----------------------------------------
1 aa
--傳統的分區裁剪和分區智能關聯無效，不能建立本地化的索引
SQL> ALTER TABLE S_P_TEST SPLIT PARTITION p1 at(1000)
into(partition p3,partition p4);
ALTER TABLE S_P_TEST SPLIT PARTITION p1 at(1000)
*
第 1 行出現錯誤:
ORA-14255: 未按范圍, 列表, 組合范圍或組合列表方法對表進行分區
--如果你知道數據在哪個表裡面，做好要制定分區條件
SQL> set autotrace traceonly
SQL> select * from S_P_TEST where col1=1;
執行計劃
----------------------------------------------------------
Plan hash value: 2020968526
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 130 | 10 (0)| 00:00:01 | | |
| 1 | PARTITION SYSTEM ALL| | 2 | 130 | 10 (0)| 00:00:01 | 1 | 4 |
|* 2 | TABLE ACCESS FULL | S_P_TEST | 2 | 130 | 10 (0)| 00:00:01 | 1 | 4 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
統計信息
----------------------------------------------------------
5 recursive calls
0 db block gets
70 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select * from S_P_TEST partition(p3) where col1=1;
執行計劃
----------------------------------------------------------
Plan hash value: 1626227678
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION SYSTEM SINGLE| | 1 | 65 | 4 (0)| 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | S_P_TEST | 1 | 65 | 4 (0)| 00:00:01 | 3 | 3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
統計信息
----------------------------------------------------------
5 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
385 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
系統分區表的使用場景我想並不多，如果對傳統的分區表數據的特性理解是：1.數據和分區是有聯系的； 2.分區就是把很多的表粘在一起管理。那系統分區表只有第二個特性。
