split 分割槽的簡單研究
1、split時需要有多少空閒空間
2、split會不會產生大量的redo
3、split的實現原理
[@more@]我們要分兩種情況討論：
1、在split邊界的兩邊都有值
2、只在split邊界的一邊有值
第一個問題：
1、在split邊界的兩邊都有值
我們先建立一個只有20M的表空間，我們構造一個大小為11M的分割槽，看空間是否足夠。
drop table s purge;
create table s(a char(1950),b date)
partition by range(b)
(partition p2008 values less than (to_date('2008-12-31','yyyy-mm-dd')) tablespace test
) pctfree 0;
--讓兩部分資料差別很大，更能說明問題
insert into s select 'a',sysdate-100 from dual connect by rownum<=1;
insert into s select 'a',sysdate from dual connect by rownum<=5500;
commit;
suk@D10R2N1> select round(sum(bytes))/1024/1024 "size(M)" from dba_segments where segment_name='S' and owner='SUK';
size(M)
----------
11
suk@D10R2N1> select sysdate from dual;
SYSDATE
-------------------
2008-10-17 02:01:04
suk@D10R2N1> ALTER TABLE S SPLIT partition p2008 AT (TO_DATE('2008-10-01','YYYY-MM-DD')) INTO (PARTITION p1 TABLESPACE TEST,PARTITION p2 TABLESPACE TEST) ;
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEST
從這個例子可以看出，如果在split邊界的兩邊都有值，則需要的空閒空間至少能放下被split的分割槽的資料。
2、只在split邊界的一邊有值
drop table s purge;
create table s(a char(1950),b date)
partition by range(b)
(partition p2008 values less than (to_date('2008-12-31','yyyy-mm-dd')) tablespace test
) pctfree 0;
--讓範圍都限制在當天
insert into s select 'a',trunc(sysdate) from dual connect by rownum<=9500;
commit;
suk@D10R2N1> select round(sum(bytes))/1024/1024 "size(M)" from dba_segments where segment_name='S' and owner='SUK';
size(M)
----------
19
suk@D10R2N1> ALTER TABLE S SPLIT partition p2008 AT (TO_DATE('2008-10-01','YYYY-MM-DD')) INTO (PARTITION p1 TABLESPACE TEST,PARTITION p2 TABLESPACE TEST) ;
Table altered.
這個例子說明，如果只在split邊界的一邊有值，則不需要額外的空間就可以split。
為什麼區別這麼大呢？ORACLE在內部是怎麼做的？
我們仍然用兩種方式做另外一個測試來驗證：
1、在split邊界的兩邊都有值
--建立表
drop table ex1 purge;
drop table ex2 purge;
drop table s purge;
suk@D10R2N1> create table s(a char(1950),b date)
2 partition by range(b)
3 (partition p2008 values less than (to_date('2008-12-31','yyyy-mm-dd')) tablespace test) pctfree 0;
Table created.
--記錄redo size
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
57820
suk@D10R2N1> insert into s select 'a',sysdate-mod(rownum,40) from dual connect by rownum<=4000;
4000 rows created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8471560
suk@D10R2N1> create table ex1 as select block_id,block_id+blocks-1 end_block from dba_extents where partition_name='P2008' and segment_name='S' order by extent_id;
Table created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8489680
suk@D10R2N1> ALTER TABLE S SPLIT partition p2008 AT (TO_DATE('2008-10-01','YYYY-MM-DD')) INTO (PARTITION P1 TABLESPACE test,PARTITION P2 TABLESPACE test) ;
Table altered.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
16931784
--從產生的redo看，spilt和第一步insert產生的redo量基本一致，故可以推斷，split partition也是insert ....select....操作，當被split的分割槽的所有的資料insert到新分割槽後，刪除舊分割槽。
suk@D10R2N1> create table ex2 as select block_id,block_id+blocks-1 end_block from dba_extents where partition_name in ('P1','P2') and segment_name='S' order by extent_id;
Table created.
--我們再從記錄下來的extent資訊看看使用情況：
suk@D10R2N1> set serverout on
suk@D10R2N1> DECLARE l_cnt INT:=0;
2 BEGIN
3 FOR c IN (SELECT * FROM ex2) LOOP
4 SELECT COUNT(1) INTO l_cnt FROM ex1 WHERE (c.block_id BETWEEN ex1.block_id AND ex1.end_block) OR (c.end_block BETWEEN ex1.block_id AND ex1.end_block);
5 IF l_cnt>0 THEN
6 dbms_output.put_line(c.block_id);
7 END IF;
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
這段程式的是為了找出S表在spilt前後被重用的block，沒有結果輸出，可以判定spilt後使用的空間都不在split前使用的空間範圍內，這也說明了spilt時，所在表空間的空閒空間必須足夠容納被spilt的分割槽的所有資料。
2、只在split邊界的一邊有值
我們再看另外一種情況：只在split邊界的一邊有值
suk@D10R2N1> create table s(a char(1950),b date)
2 partition by range(b)
3 (partition p2008 values less than (to_date('2008-12-31','yyyy-mm-dd')) tablespace test) pctfree 0;
Table created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
0
suk@D10R2N1> insert into s select 'a',sysdate-40 from dual connect by rownum<=4000;
4000 rows created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8413784
suk@D10R2N1> create table ex1 as select block_id,block_id+blocks-1 end_block from dba_extents where partition_name='P2008' and segment_name='S' order by extent_id;
Table created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8431020
suk@D10R2N1> ALTER TABLE S SPLIT partition p2008 AT (TO_DATE('2008-10-01','YYYY-MM-DD')) INTO (PARTITION P1 TABLESPACE test,PARTITION P2 TABLESPACE test) ;
Table altered.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8515144
首先我們可以看出，這種情況下，split產生的redo非常少，我們可以推測這種情況下，資料沒有做實際的移動，只是簡單修改資料字典的資訊。為了驗證這一點，我們看看spilt前後extent資訊：
suk@D10R2N1> select block_id,end_block from ex1
2 minus
3 select block_id,end_block from ex2;
no rows selected
suk@D10R2N1> select block_id,end_block from ex2
2 minus
3 select block_id,end_block from ex1;
BLOCK_ID END_BLOCK
---------- ----------
1033 1040
suk@D10R2N1> select segment_name,partition_name from dba_extents where block_id=1033 and blocks=1040-1033+1 and owner='SUK';
SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
S P2
這個結果表明：對於P1，它使用的block和split前是完全一致的；而僅僅是為P2單獨分配一個初始化的extent。這也證明了只在split邊界的一邊有值的情況下，資料沒有發生實際移動，僅僅修改一些資料字典資訊。
但是這種情況也有特例：就算只在split邊界的一邊有值，如果有值的一端在split後對應的分割槽前後表空間不一致，，則新的表空間的空閒空間至少能放下被split的分割槽的資料。
如果有興趣，可以用trace跟蹤一下，我們可以發現split時，會遞迴呼叫如下SQL：
select /*+ FIRST_ROWS PARALLEL("S", 1) */ 1
from
"SUK"."S" PARTITION ("P2008") where ( ( ( ( "B" < TO_DATE(' 2008-10-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) ) ) )
and rownum < 2
select /*+ FIRST_ROWS PARALLEL("S", 1) */ 1
from
"SUK"."S" PARTITION ("P2008") where ( ( ( ( "B" >= TO_DATE(' 2008-10-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) ) ) )
and rownum < 2
這兩個SQL就是用於判斷在split邊界的兩邊是否有值。在這裡，oracle是非常聰明的，它根據這個判斷結果來決定是否有必要移動資料，在某些情況下可以節省很多資源。
到這裡，我們可以回答開頭提出的幾個問題了：
1、split時需要有多少空閒空間
如果在split邊界的兩邊都有值，則需要的空閒空間必能能夠放下被split分割槽的所有資料。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間不一致，則需要的空閒空間必能能夠放下被split分割槽的所有資料。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間一致，則無需額外的空間。
2、split會不會產生大量的redo
split做的實際上是insert...select...操作，所以產生的日誌量與是否有資料移動有關。
如果在split邊界的兩邊都有值，則有資料移動，會為移動的資料產生redo。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間不一致，則有資料移動，會為移動的資料產生redo。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間一致，則無資料移動，不會為資料產生redo。
3、split的實現原理
split時，首先判斷在split的邊界是否都有資料存在。
如果在split邊界的兩邊都有值，則產生兩個新的分割槽，把原分割槽的所有資料insert到新分割槽後，刪除原分割槽，更新資料字典。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間不一致，則產生兩個新的分割槽，把原分割槽的所有資料insert到新分割槽後，刪除原分割槽，更新資料字典。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間一致，則新加一個分割槽，並修改原分割槽資訊為另一個新分割槽的資訊，無資料移動。
2、split會不會產生大量的redo
3、split的實現原理
[@more@]我們要分兩種情況討論：
1、在split邊界的兩邊都有值
2、只在split邊界的一邊有值
第一個問題：
1、在split邊界的兩邊都有值
我們先建立一個只有20M的表空間，我們構造一個大小為11M的分割槽，看空間是否足夠。
drop table s purge;
create table s(a char(1950),b date)
partition by range(b)
(partition p2008 values less than (to_date('2008-12-31','yyyy-mm-dd')) tablespace test
) pctfree 0;
--讓兩部分資料差別很大，更能說明問題
insert into s select 'a',sysdate-100 from dual connect by rownum<=1;
insert into s select 'a',sysdate from dual connect by rownum<=5500;
commit;
suk@D10R2N1> select round(sum(bytes))/1024/1024 "size(M)" from dba_segments where segment_name='S' and owner='SUK';
size(M)
----------
11
suk@D10R2N1> select sysdate from dual;
SYSDATE
-------------------
2008-10-17 02:01:04
suk@D10R2N1> ALTER TABLE S SPLIT partition p2008 AT (TO_DATE('2008-10-01','YYYY-MM-DD')) INTO (PARTITION p1 TABLESPACE TEST,PARTITION p2 TABLESPACE TEST) ;
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEST
從這個例子可以看出，如果在split邊界的兩邊都有值，則需要的空閒空間至少能放下被split的分割槽的資料。
2、只在split邊界的一邊有值
drop table s purge;
create table s(a char(1950),b date)
partition by range(b)
(partition p2008 values less than (to_date('2008-12-31','yyyy-mm-dd')) tablespace test
) pctfree 0;
--讓範圍都限制在當天
insert into s select 'a',trunc(sysdate) from dual connect by rownum<=9500;
commit;
suk@D10R2N1> select round(sum(bytes))/1024/1024 "size(M)" from dba_segments where segment_name='S' and owner='SUK';
size(M)
----------
19
suk@D10R2N1> ALTER TABLE S SPLIT partition p2008 AT (TO_DATE('2008-10-01','YYYY-MM-DD')) INTO (PARTITION p1 TABLESPACE TEST,PARTITION p2 TABLESPACE TEST) ;
Table altered.
這個例子說明，如果只在split邊界的一邊有值，則不需要額外的空間就可以split。
為什麼區別這麼大呢？ORACLE在內部是怎麼做的？
我們仍然用兩種方式做另外一個測試來驗證：
1、在split邊界的兩邊都有值
--建立表
drop table ex1 purge;
drop table ex2 purge;
drop table s purge;
suk@D10R2N1> create table s(a char(1950),b date)
2 partition by range(b)
3 (partition p2008 values less than (to_date('2008-12-31','yyyy-mm-dd')) tablespace test) pctfree 0;
Table created.
--記錄redo size
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
57820
suk@D10R2N1> insert into s select 'a',sysdate-mod(rownum,40) from dual connect by rownum<=4000;
4000 rows created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8471560
suk@D10R2N1> create table ex1 as select block_id,block_id+blocks-1 end_block from dba_extents where partition_name='P2008' and segment_name='S' order by extent_id;
Table created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8489680
suk@D10R2N1> ALTER TABLE S SPLIT partition p2008 AT (TO_DATE('2008-10-01','YYYY-MM-DD')) INTO (PARTITION P1 TABLESPACE test,PARTITION P2 TABLESPACE test) ;
Table altered.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
16931784
--從產生的redo看，spilt和第一步insert產生的redo量基本一致，故可以推斷，split partition也是insert ....select....操作，當被split的分割槽的所有的資料insert到新分割槽後，刪除舊分割槽。
suk@D10R2N1> create table ex2 as select block_id,block_id+blocks-1 end_block from dba_extents where partition_name in ('P1','P2') and segment_name='S' order by extent_id;
Table created.
--我們再從記錄下來的extent資訊看看使用情況：
suk@D10R2N1> set serverout on
suk@D10R2N1> DECLARE l_cnt INT:=0;
2 BEGIN
3 FOR c IN (SELECT * FROM ex2) LOOP
4 SELECT COUNT(1) INTO l_cnt FROM ex1 WHERE (c.block_id BETWEEN ex1.block_id AND ex1.end_block) OR (c.end_block BETWEEN ex1.block_id AND ex1.end_block);
5 IF l_cnt>0 THEN
6 dbms_output.put_line(c.block_id);
7 END IF;
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
這段程式的是為了找出S表在spilt前後被重用的block，沒有結果輸出，可以判定spilt後使用的空間都不在split前使用的空間範圍內，這也說明了spilt時，所在表空間的空閒空間必須足夠容納被spilt的分割槽的所有資料。
2、只在split邊界的一邊有值
我們再看另外一種情況：只在split邊界的一邊有值
suk@D10R2N1> create table s(a char(1950),b date)
2 partition by range(b)
3 (partition p2008 values less than (to_date('2008-12-31','yyyy-mm-dd')) tablespace test) pctfree 0;
Table created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
0
suk@D10R2N1> insert into s select 'a',sysdate-40 from dual connect by rownum<=4000;
4000 rows created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8413784
suk@D10R2N1> create table ex1 as select block_id,block_id+blocks-1 end_block from dba_extents where partition_name='P2008' and segment_name='S' order by extent_id;
Table created.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8431020
suk@D10R2N1> ALTER TABLE S SPLIT partition p2008 AT (TO_DATE('2008-10-01','YYYY-MM-DD')) INTO (PARTITION P1 TABLESPACE test,PARTITION P2 TABLESPACE test) ;
Table altered.
suk@D10R2N1> select value from v$mystat where statistic#=134;
VALUE
----------
8515144
首先我們可以看出，這種情況下，split產生的redo非常少，我們可以推測這種情況下，資料沒有做實際的移動，只是簡單修改資料字典的資訊。為了驗證這一點，我們看看spilt前後extent資訊：
suk@D10R2N1> select block_id,end_block from ex1
2 minus
3 select block_id,end_block from ex2;
no rows selected
suk@D10R2N1> select block_id,end_block from ex2
2 minus
3 select block_id,end_block from ex1;
BLOCK_ID END_BLOCK
---------- ----------
1033 1040
suk@D10R2N1> select segment_name,partition_name from dba_extents where block_id=1033 and blocks=1040-1033+1 and owner='SUK';
SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
S P2
這個結果表明：對於P1，它使用的block和split前是完全一致的；而僅僅是為P2單獨分配一個初始化的extent。這也證明了只在split邊界的一邊有值的情況下，資料沒有發生實際移動，僅僅修改一些資料字典資訊。
但是這種情況也有特例：就算只在split邊界的一邊有值，如果有值的一端在split後對應的分割槽前後表空間不一致，，則新的表空間的空閒空間至少能放下被split的分割槽的資料。
如果有興趣，可以用trace跟蹤一下，我們可以發現split時，會遞迴呼叫如下SQL：
select /*+ FIRST_ROWS PARALLEL("S", 1) */ 1
from
"SUK"."S" PARTITION ("P2008") where ( ( ( ( "B" < TO_DATE(' 2008-10-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) ) ) )
and rownum < 2
select /*+ FIRST_ROWS PARALLEL("S", 1) */ 1
from
"SUK"."S" PARTITION ("P2008") where ( ( ( ( "B" >= TO_DATE(' 2008-10-01
00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) ) ) )
and rownum < 2
這兩個SQL就是用於判斷在split邊界的兩邊是否有值。在這裡，oracle是非常聰明的，它根據這個判斷結果來決定是否有必要移動資料，在某些情況下可以節省很多資源。
到這裡，我們可以回答開頭提出的幾個問題了：
1、split時需要有多少空閒空間
如果在split邊界的兩邊都有值，則需要的空閒空間必能能夠放下被split分割槽的所有資料。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間不一致，則需要的空閒空間必能能夠放下被split分割槽的所有資料。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間一致，則無需額外的空間。
2、split會不會產生大量的redo
split做的實際上是insert...select...操作，所以產生的日誌量與是否有資料移動有關。
如果在split邊界的兩邊都有值，則有資料移動，會為移動的資料產生redo。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間不一致，則有資料移動，會為移動的資料產生redo。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間一致，則無資料移動，不會為資料產生redo。
3、split的實現原理
split時，首先判斷在split的邊界是否都有資料存在。
如果在split邊界的兩邊都有值，則產生兩個新的分割槽，把原分割槽的所有資料insert到新分割槽後，刪除原分割槽，更新資料字典。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間不一致，則產生兩個新的分割槽，把原分割槽的所有資料insert到新分割槽後，刪除原分割槽，更新資料字典。
只在split邊界的一邊有值，且split後有值的一端對應的表空間與原分割槽對應表空間一致，則新加一個分割槽，並修改原分割槽資訊為另一個新分割槽的資訊，無資料移動。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-1036171/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用split對分割槽表再分割槽
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 範圍分割槽表和INTERVAL分割槽表對於SPLIT分割槽的區別
- split分割槽操作導致的librarycachelock
- Oracle vs PostgreSQL DBA（13）- 拆分(split)分割槽OracleSQL
- 分割槽表split操作及maxvalue處理
- 對一條基於分割槽的簡單SQL的優化SQL優化
- 如何查詢分割槽表的分割槽及子分割槽
- DM7，DM8和ORACLE中對分割槽split的區別Oracle
- PLSQL根據分割槽表的分割槽名批次truncate分割槽SQL
- Solaris硬碟分割槽簡介(轉)硬碟
- rebuild分割槽表分割槽索引的方法Rebuild索引
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- Oracle資料庫分割槽表SPLIT操作導致歸檔瘋漲Oracle資料庫
- 對硬碟的一種簡單加密----在分割槽表上做手腳. (轉)硬碟加密
- windows8系統如何實現簡單無損分割槽Windows
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Linux系統分割槽簡介Linux
- 《RHEL6硬碟的分割槽和swap分割槽管理》——硬碟分割槽的大總結硬碟
- Hive的靜態分割槽與動態分割槽Hive
- 分割槽表入無分割槽的資料庫資料庫
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- Linux主分割槽，擴充套件分割槽，邏輯分割槽Linux套件
- Oracle drop分割槽表單個分割槽無法透過閃回恢復Oracle
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- Oracle分割槽表及分割槽索引Oracle索引
- INTERVAL分割槽表鎖分割槽操作
- java split進行字串分割Java字串
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- oracle實用sql(14)--查詢分割槽表的分割槽列和子分割槽列OracleSQL
- 帶default分割槽的列表分割槽表的擴充套件套件
- MySQL分割槽表的分割槽原理和優缺點MySql
- Greenplum計算能力估算-暨多大表需要分割槽，單個分割槽多大適宜
- 建立單獨的分割槽儲存NULL記錄Null
- 分割槽表及分割槽索引建立示例索引
- oracle分割槽表和分割槽表exchangeOracle
- Linux分割槽方案、分割槽建議Linux
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引