範圍和列表技術的組合,首先對錶進行範圍分割槽,然後用列表技術對每個範圍分割槽再次分割槽。與組合範圍-雜湊分割槽不同的是,每個子分割槽的所有內容表示資料的邏輯子集,由適當的範圍和列表分割槽設定來描述。(注意:
先一定要進行範圍分割槽)
select * from v$option s order by s.parameter desc
create tablespace "partion_03"
logging
datafile 'd:oracleoradatajzhuapartion_03.dbf' size 50m
extent management local segment space management auto
drop tablespace partion_01
create table partition_test
( pid number not null,
pitem varchar2(200),
pdata date not null
) partition by range(pid)
( partition part_01 values less than(50000) tablespace dinya_space01,
partition part_02 values less than(100000) tablespace dinya_space02,
partition part_03 values less than(maxvalue) tablespace dinya_space03
) create table partition_ttest
( pid number not null,
pitem varchar2(200),
pdata date not null
) partition by range(pdata)
( partition part_t01 values less than(to_date('2004-01-01','yyyy-mm-dd')) tablespace dinya_space01,
partition part_t02 values less than(to_date('2008-01-01','yyyy-mm-dd')) tablespace dinya_space02,
partition part_t03 values less than(maxvalue) tablespace dinya_space03
) insert into partition_test(pid,pitem,pdata) select h.id, from st_handle h
select * from partition_test partition(part_01) t where t.pid = '1961'
create table partition_hashtest
( pid number not null,
pitem varchar2(200),
pdata date not null
) partition by hash(pid)
( partition part_h01 tablespace dinya_space01,
partition part_h02 tablespace dinya_space02,
partition part_h03 tablespace dinya_space03
) insert into partition_hashtest(pid,pitem,pdata) select h.id, from st_handle h
select * from partition_hashtest partition(part_h03) t where t.pid = '1961'
create table partition_fhtest
( pid number not null,
pitem varchar2(200),
pdata date not null
) partition by range(pdata) subpartition by hash(pid) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
( partition part_fh01 values less than(to_date('2004-01-01','yyyy-mm-dd')) tablespace dinya_space01,
partition part_fh02 values less than(to_date('2008-01-01','yyyy-mm-dd')) tablespace dinya_space02,
partition part_fh03 values less than(maxvalue) tablespace dinya_space03
) insert into partition_fhtest(pid,pitem,pdata) select h.id, from st_handle h
select * from partition_fhtest partition(part_fh02) t where t.pid = '1961'
select * from partition_fhtest partition(part_fh03) t
select * from st_handle h where h.rectime > to_date('2008-01-01','yyyy-mm-dd');
select * from partition_fhtest partition(part_fh03) t where t.pdata > to_date('2008-01-01','yyyy-mm-dd');
alter table partition_test add partition part_05 values less than (10020) tablespace dinya_space03
select * from partition_fhtest partition(part_fh02) t
update partition_fhtest partition(part_fh02) t set t.pitem = 'jzhua' where t.pid = '1961'
delete from partition_fhtest partition(part_fh02) t where t.pid = '1961'
create table partition_hb
( pid number not null,
pitem varchar2(200),
pdata date not null
) partition by range(pid)
( partition part_01 values less than(50000) tablespace dinya_space01,
partition part_02 values less than(100000) tablespace dinya_space02,
partition part_03 values less than(maxvalue) tablespace dinya_space03
) insert into partition_hb(pid,pitem,pdata) select h.id, from st_handle h
select * from partition_hb partition(part_03) t where t.pid = '100001'
alter table partition_hb merge partitions part_01,part_02 into partition part_02;
-- spilt partition 分割槽名 at(這裡是乙個臨界區,比如:50000就是說小於50000的放在part_01,而大於50000的放在part_02中)
alter table partition_hb split partition part_02 at (50000) into (partition part_01 tablespace dinya_space01, partition part_02 tablespace dinya_space02);
alter table partition_hb rename partition part_01_test to part_02;
分割槽表和一般表一樣可以建立索引,分割槽表可以建立區域性索引和全域性索引。當分割槽中出現許多事務並且要保證所有分割槽中的資料記錄的唯一性時採用全域性索引。全域性索引建立時 global 子句允許指定索引的範圍值,這個範圍值為索引欄位的範圍值。
其實理論上有3中分割槽索引。
索引分割槽是在您建立了表分割槽後,要建索引就必須是建立索引分割槽。分2大類:一類是把索引資訊建立在各個分割槽上,這叫區域性索引分割槽(或叫本地索引分割槽)。
另一類是把索引集中起來,叫全域性索引。
Oracle分割槽表總結
create table emp empno number 4 ename varchar2 30 location varchar2 30 partition by list location partition p1 values 北京 partition p2 values 上海 天津 重慶 ...
Oracle分割槽表和索引
什麼時候使用分割槽 1 大資料量的表,比如大於2gb。一方面2gb檔案對於32位os是乙個上限,另外備份時間長。2 包括歷史資料的表,比如最新的資料放入到最新的分割槽中。典型的例子 歷史表,只有當前月份的資料可以被修改,而其他月份只能read only oracle只支援以下分割槽 tables,i...
分割槽表損壞
分割槽表為何會出故障 在分割槽表被破壞後,啟動系統時往往會出現 non system disk or disk error,replace disk and press a key to reboot 非系統盤或盤出錯 error loading operating system 裝入dos引導記錄...