一、什麼時候可以需要分割槽

1、表大小達到2g左右；

2、表資料達到1千萬行，並且操作越來越慢；

3、隨著資料增長效率受到很大影響；

4、資料後可以進行並行讀寫，歸檔，，恢復，檢索等操作。

二、 by range。

SQL> create table yxl_range(a number,b number)

2 partition by range(b)

3 (partition yxl_range_b_1 values less than (1),

4 partition yxl_range_b_2 values less than (10),

5 partition yxl_range_b_3 values less than (maxvalue)

6 );

Table created

SQL> drop table yxl_range;

Table dropped

三、partitioning by list

SQL> create table yxl_list(a number,b varchar2(2))

2 partition by list(b)(

3 partition yxl_list_b_1 values ('aa'),

4 partition yxl_list_b_2 values ('bb'),

5 partition yxl_list_b_3 values (default)

6 )

7 ;

Table created

SQL> drop table yxl_list;

Table dropped

四、partitioning by hash

--建立3個表空間

SQL> create tablespace yxl_hash_1

2 datafile '/tmp/yxl_hash_1.dbf' size 10m;

Tablespace created

SQL> create tablespace yxl_hash_2

2 datafile '/tmp/yxl_hash_2.dbf' size 10m

3 ;

Tablespace created

SQL> create tablespace yxl_hash_3

2 datafile '/tmp/yxl_hash_3.dbf' size 10m

3 ;

Tablespace created

SQL> create table yxl_hash(a number,b number)

2 partition by hash (b)(

3 partition yxl_hash_b_1 tablespace yxl_hash_1,

4 partition yxl_hash_b_2 tablespace yxl_hash_2,

5 partition yxl_hash_b_3 tablespace yxl_hash_3

6 );

Table created

SQL> drop table yxl_hash;

Table dropped

SQL> drop tablespace yxl_hash_1 including contents and datafiles;

Tablespace dropped

SQL> drop tablespace yxl_hash_2 including contents and datafiles;

Tablespace dropped

SQL> drop tablespace yxl_hash_3 including contents and datafiles;

Tablespace dropped

五、partitioning a in mutiple ways.（複合分割槽）

SQL> create table yxl_multi

2 (a number, b number,c varchar2(2))

3 partition by range(b)

4 subpartition by list(c)(

5 partition yxl_multi_b_1 values less than (1)

6 (subpartition yxl_multi_c_1 values ('aa'),

7 subpartition yxl_multi_c_2 values ('bb')),

8 partition yxl_multi_b_2 values less than (maxvalue)

9 (subpartition yxl_multi_cc_1 values ('aa'),

10 subpartition yxl_multi_cc_2 values ('bb'))

11 );

Table created

SQL> drop table yxl_multi;

Table dropped

六、creating partitions on demand.(按照要求自動建立分割槽 )。

SQL> select * from v$version;

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

SQL> create table f_sales(sales_amt number ,d_date date)

2 partition by range (d_date)

3 interval(numtoyminterval(1, 'YEAR'))

4 (partition p1 values less than (to_date('2008-01-01','yyyy-mm-dd')));

create table f_sales(sales_amt number ,d_date date)

partition by range (d_date)

interval(numtoyminterval(1, 'YEAR'))

(partition p1 values less than (to_date('2008-01-01','yyyy-mm-dd')))

ORA-00922: 選項缺失或無效

SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

SQL> create table yxl_inc(a number,b date)

2 partition by range(b)

3 interval(numtoyminterval(1,'YEAR'))

4 (partition yxl_inc_1 values less than (to_date('2008-1-1','yyyy-mm-dd')));

Table created

complete

七、partitioning on a vitual column（虛擬列上分割槽 11g)

SQL> create table emp1 (

2 emp_id number

3 ,salary number

4 ,comm_pct number

5 ,commission generated always as (salary*comm_pct)

6 )

7 partition by range(commission)

8 (partition p1 values less than (1000)

9 ,partition p2 values less than (2000)

10 ,partition p3 values less than (maxvalue));

Table created

八、Application-Controlled Partitioning （系統控制分割槽 11g)

SQL> create table apps

2 (app_id number

3 ,app_amnt number)

4 partition by system

5 (partition p1

6 ,partition p2

7 ,partition p3);

Table created

SQL> insert into apps values(1,100);

insert into apps values(1,100)

ORA-14701: 對於按“系統”方法進行分割槽的表, 必須對 DML 使用分割槽副檔名或繫結變數

SQL> insert into apps partition(p1) values(1,100);

1 row inserted

九、分割槽的同時指定表空間。

create table f_regs

(reg_count number

,d_date_id number

)

partition by range (d_date_id)(

partition p_2009 values less than (20100101) tablespace p_2009_tbsp,

partition p_2010 values less than (20110101) tablespace p_2010_tbsp,

partition p_max values less than (maxvalue) tablespace p_max_tbsp);

--如果你已經建立了分割槽表，則可以採取下面語句把某個分割槽移動到某個表空間。

alter table f_regs move partition p_2009 tablespace tablespace p_2009_tbsp;

--如果你移動了某個分割槽，則必須rebuild那個分割槽的索引。為什麼呢？因為移動表空間後，行的rowid發生了變化，所以必須rebuild索引。

--在rebuild index的同時，可以同時移動到某人表空間。

alter index d_date_id_fk1 rebuild partition p_2009 tablespace tablespace p_2009_tbsp;

十、Automatically Moving Updated Rows

SQL> create table yxl01 (a number ,b number)

2 partition by range (b)(

3 partition p1 values less than (10),

4 partition p2 values less than (20),

5 partition p3 values less than (maxvalue));

Table created

SQL> insert into yxl01 values(1,9);

1 row inserted

SQL> insert into yxl01 values(2,19);

1 row inserted

SQL> commit;

Commit complete

SQL> update yxl01 set b=11 where a = 1;

update yxl01 set b=11 where a = 1

ORA-14402: 更新分割槽關鍵字列將導致分割槽的更改

SQL> alter table yxl01 enable row movement;

Table altered

SQL> update yxl01 set b=11 where a = 1;

1 row updated

SQL> commit;

Commit complete

SQL> alter table yxl01 disable row movement;

Table altered

十一、對已經存在的表進行分割槽操作。

步驟：1、用create table語句新建一個分割槽表。

2、drop或者rename舊錶。

3、把新建的分割槽表rename成舊錶。

SQL> create table yxl01_new

2 partition by range (b)(

3 partition p1 values less than (10),

4 partition p2 values less than (20),

5 partition p3 values less than (maxvalue))

6 nologging

7 as select * from yxl01

8 /

Table created

SQL> drop table yxl01;

Table dropped

SQL> rename yxl01_new to yxl01;

Table renamed

十二、給分割槽表新增分割槽。

1、range分割槽表新增分割槽。分割槽表定時

alter table f_regs add partition p2011 values less than (20120101) tablespace p_tbsp;

注：分割槽表定義時最大的分割槽表沒有采用 less than (maxvalue)；如果採用了，需要切分最大的分割槽表，而不是新增分割槽操作。

2、給list分割槽表新增分割槽。

alter table f_sales add partition reg_east values('GA');

注：分割槽表定義時沒有采用default選項，如果採用了，則需要切分分割槽表，而不是新增分割槽表操作。

3、給hash分割槽表新增分割槽。

alter table browns add partition new_part tablespace p_tbsp update indexes;

注：沒有沒有采用update indexes選項，則需要rebuild分割槽表的索引。

十三、Exchanging a Partition with an Existing Table

SQL> create table f_sales

2 (sales_amt number, d_date_id number)

3 partition by range (d_date_id)

4 (partition p_2007 values less than (20080101),

5 partition p_2008 values less than (20090101),

6 partition p_2009 values less than (20100101)

7 );

Table created

SQL> create bitmap index d_date_id_fk1

2 on f_sales(d_date_id) local;

Index created

SQL> alter table f_sales add partition p_2010 values less than (20110101);

Table altered

SQL> create table workpart(sales_amt number, d_date_id number);

Table created

SQL> insert into workpart values(100,20100101);

1 row inserted

SQL> insert into workpart values(120,20100102);

1 row inserted

SQL> commit;

Commit complete

SQL> create bitmap index d_date_id_fk2 on workpart(d_date_id);

Index created

SQL> alter table f_sales exchange partition p_2010

2 with table workpart including indexes without validation;

Table altered

SQL> select * from f_sales;

SALES_AMT D_DATE_ID

---------- ----------

100 20100101

120 20100102

十四、重新命名分割槽

SQL> alter table f_sales rename partition p_2010 to p_2010_re;

Table altered

十五、切分分割槽

SQL> alter table f_sales

2 split partition p_2010_re at (20100601) into

3 (partition p_2010_r,partition p_2010_b) update indexes

4 ;

Table altered

SQL> select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

D_DATE_ID_FK1 P_2007 USABLE

D_DATE_ID_FK1 P_2008 USABLE

D_DATE_ID_FK1 P_2009 USABLE

D_DATE_ID_FK1 P_2010_B USABLE

D_DATE_ID_FK1 P_2010_R USABLE

注：如果沒有update indexes選項，則需要rebuild index操作。

十六、合併分割槽。

SQL> alter table f_sales

2 merge partitions p_2010_r,p_2010_b into partition p_2010;

Table altered

SQL> select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

D_DATE_ID_FK1 P_2007 USABLE

D_DATE_ID_FK1 P_2008 USABLE

D_DATE_ID_FK1 P_2009 USABLE

D_DATE_ID_FK1 P_2010 UNUSABLE

SQL> alter index D_DATE_ID_FK1 rebuild partition p_2010;

Index altered

注：當然上面使用時可以加上 update indexes選項。

十六、刪除分割槽

alter table f_sales drop partition p_2008;

alter table f_sales drop subpartition p2_south;

十七、快速刪除分割槽資料。

SQL> alter table f_sales truncate partition p_2009_re;

Table truncated

十八、分割槽統計資訊

SQL> exec dbms_stats.gather_table_stats('HBJB_KF_BDGS','F_SALES','P_2008');

PL/SQL procedure successfully completed

十九、建立索引。

1.Local Index

create index f_regs_idx1 on f_regs(reg_count) local;

或者：

create index f_regs_idx1 on f_regs(reg_count) local

(partition p2009 tablespace idx1

,partition p2010 tablespace idx2);

2.Global Index

reate index f_count_idx1 on f_regs(reg_count)

global partition by hash(reg_count) partitions 3;

[@more@]

表分割槽操作大全_table partitioning

相關文章