Oracle表分區詳細說明

 

Oracle表分區

自從oracle8i 開始可以把一個表分割為多個小的部分,這樣可以對oracle的效能最佳化帶來很大的好處~
例如：改善表的查詢效能,更加容易管理表資料,備份和恢複操作更方便

在oracle 中分區表 分為好幾種的(定界分割,散列分區,子分區,列表分區,索引分割區)下面我們來慢慢介紹

現在我們來建立一個[定界分割]
create table RangeTable(
id int primary key,
name varchar(20),
grade int
)
partition by rang(grade)
(
partition part1 values less then(50) tablespace Part1_tb,
partition part2 values less then(MAXVALUE) tablespace Part2_tb
);
如果grade的值小於50的話 就把記錄放到名為part1的分區當中,part1分區將被儲存在Part1_tb資料表空間中
其他的就放在part2中 MAXVALUE是oracle的關鍵字 表示最大值

[散列分區]
create table HashTable(
id int primary key,
name varchar(20),
grade int
)
/*有兩種方式,1就是指定分區數目和所使用的資料表空間,2指定以命名的分區*/
partition by hash(grade)
partitions 10 -- 指定分區的數目
store in(Part1_tb,Part2_tb,Part3_tb) --如果指定的分區數目比資料表空間多,分區會以迴圈方式分配到資料表空間
/*------------------------------------*/
partition by rang(grade)--這種方式就是 指定以命名的分區
(
partition part1 tablespace Part1_tb,
partition part2 tablespace Part2_tb
);

[子分區]即是分區的分區
create table ChildTable(
id int primary key,
name varchar(20),
grade int
)
partition by rang(grade)
subpartition by hash(grade)
partitions 5
(
partition part1 values less then(30) tablespace Part1_tb,
partition part2 values less then(60) tablespace Part2_tb,
partition part3 values less then(MAXVALUE) tablespace Part3_tb
);

[列表分區]告訴oracle所有可能的值
create table ListTable(
id int primary key,
name varchar(20),
area varchar(10)
)
partition by list(area)
(
partition part1 values('guangdong','beijing') tablespace Part1_tb,
partition part2 values('shanghai','nanjing') tablespace Part2_tb
);

[索引分割區]索引也可以按照和表進行分區時使用的相同的值範圍來分區
create index IndexTable_index
on IndexTable(name)
local
(
partition part1 tablespace Part1_tb,
partition part2 tablespace Part2_tb
)
--local 告訴oracle表 IndexTable的每一個分區建立一個獨立的索引
create index IndexTable_index
on IndexTable(name)
global;
--global為全域索引 全域索引可以包含多個分區的值 局部索引比全域索引容易管理,而全域索引比較快
注意:不能為散列分區 或者 子分區建立全域索引

查詢某一個表分區
select * from table partition(part1);

 

 

 

 

 

 

 

oracle表分區主要是為了容易管理和效能方面考慮,有四種分區方法:

1.區間分區:
create table range_example
(
range_column number,
sj date
)
PARTITION BY RANGE(range_column)
(
PARTITION part_1 VALUE LESS THAN(18),
PARTITION part_2 VALUE LESS THAN(40),
PARTITION part_3 VALUE LESS THAN(120)
)
;

2.散列分區
create table range_example
(
range_column number,
sj date
)
PARTITION BY HASH(range_column)
(
PARTITION part_1,
PARTITION part_2
);
使用散列分區你無法控制一行放在哪個分區中,如果改變散列分區的個數,所有的資料都將在分區中重新分配.

3.列表分區
create table range_example
(
range_column number,
sj date
)
PARTITION BY LIST(range_column)
(
PARTITION part_1 VALUES(1,2,3),
PARTITION part_2 VALUES(4,5,6),
PARTITION part_2 VALUES(default)
);

4.組合分區
組合分區是區間分區和散列分區的組合或區間分區和列表分區的組合,
頂層分區總是區間分區,第二層分區可能是列表分區或散列分區
create table range_example
(
range_column number,
id varchar2(2),
sj date
)
PARTITION BY RANGE(range_column)
SUBPARTITION BY LIST(id)
(
PARTITION part_1 VALUES LESS THAN(18)
(
SUBPARTITOIN part_sub_1 VALUES('A','B','C'),
SUBPARTITOIN part_sub_2 VALUES('D','E','F')
)
PARTIRION part_2 VALUES LESS THAN(40)
(
SUBPARTITION part_sub_3 VALUES('AA','BB','CC'),
SUBPARTITION part_sub_4 VALUES('DD','EE','FF')
)
PARTITION part_3 VALUES LESS THAN(120)
(
SUBPARTITION part_sub_5 VALUES(default)
)
)

 

分區維護
      移動分區 alter table goods move partition p1 tablespace system;
      刪除分區 alter table goods drop partition p1;
      添加分區 alter table goods add partition p1 values
               less than(to_date('1999-08=01','YYYY-MM-DD'));
      結合分區（只能用於散列方法分區）
               alter table goods coalesce partition;
      截斷分區 alter table goods truncate partition p3;
      拆分分區 alter table goods split partiiton p2
               at(to_date('1999-12-12','yyyy-mm-dd'))
               into(partiiton p4,partiiton p5);
      合并分區 alter table goods merge partitions p4,p5 into parititon                p6

交換表中的資料
       alter table tab2 exchange partiiton p1
       with table tab1

二，--------------------------------------------------------------- Create table(建立分區表)
create table BILL_MONTHFEE_ZERO
(
  SERV_ID             NUMBER(20) not null,
  BILLING_CYCLE_MONTH NUMBER(6) not null,
  DATE_TYPE           NUMBER(1),
  ACC_NBR             VARCHAR2(80)
)
 partition by range (BILLING_CYCLE_MONTH)
  (partition p_200407 values less than (200407)
    tablespace TS_ZIKEN
      storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
   partition p_200408 values less than (200408)
    tablespace TS_ZIKEN
      storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0))
      ;
create index idx_bill_monthfee_zero_idx01 on bill_monthfee_zero(billing_cycle_month)
tablespace TS_ZIKEN_idx
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) nologging;
grant all on bill_monthfee_zero to dxsq_dev;
 
--增加分區表
 
alter table BILL_MONTHFEE_ZERO add Partition p_200409
values less than (200409) tablespace ts_ziken;

--刪除一分區
alter table part_tbl drop Partition part_tbl_08;
 
--將一個分區分為兩個分區
alter table bill_monthfee_zero split Partition p_200409 at (200409)
into (Partition p_200409_1 tablespace ts_ziken,
Partition p_200409_2 tablespace ts_ziken_idx);
 
--合并分區
ALTER TABLE bill_monthfee_zero
   MERGE PARTITIONS p_200408, p_200409 INTO PARTITION p_all
 
--將分區改名
alter table bill_monthfee_zero rename Partition p_200408 to p_fee_200408
 
--將分區改資料表空間
alter table bill_monthfee_zero move Partition p_200409
tablespace ts_ziken_01 nologging
 
--查詢特定分區
select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);
 
--添加資料
insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407)
 
--分區表的匯出
userid=dxsq/teledoone@jndxsq154
buffer=102400
tables=bill_monthfee:P_200401,
file=E:\exp_para\exp_dxsq_tables.dmp
log=E:\exp_para\exp_dxsq_tables.log
 －－exchange partition:
交換分區是將一個分區的資料和一個非分區的表資料進行資料交換。條件是兩者邏輯結構相同，而且表資料的資料不能超出分區主鍵的範圍,否則：
SQL> alter table baisadmin.t_name exchange partition p5 with table baisadmin.t_n
ame_check_reg;
alter table baisadmin.t_name exchange partition p5 with table baisadmin.t_name_bak  
技巧：
刪除表中一個欄位：
alter table bill_monthfee_zero set unused column date_type;
添加一個欄位：alter table bill_monthfee_zero add date_type number(1);