Oracle 11g新特性系統磁碟分割表
在Oracle 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.分區就是把很多的表粘在一起管理。那系統磁碟分割表只有第二個特性。
在CentOS 6.4下安裝Oracle 11gR2(x64)
Oracle 11gR2 在VMWare虛擬機器中安裝步驟
Debian 下 安裝 Oracle 11g XE R2
Oracle效能最佳化 之 共用池