今天總結了一下分割槽表的知識

分享一下。

大體有以下內容：

1.hash partition

range partition

list partition

range hash partition

range list partitio

2.partition operations

add ,truncate,drop partitions

merge ,move ,split partitions

add values in list partitions

exchange partition

3.prefixed index,nonprefixed index

global index

4.分割槽的匯入，匯出

hash partition

SQL> CREATE TABLE HASH_PART (A NUMBER(10),B VARCHAR2(100))
2 PARTITION BY HASH(B)
3 ;

這樣預設只有一個分割槽
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
HASH_PART SYS_P21 TBS2

SQL> create table hash_part(a number(10),b varchar2(100)) tablespace tbs1
2 partition by hash(a)
3 partitions 8
4 store in (tbs2,tbs3,tbs4);

Table created.

SQL> select tablespace_name,segment_name,partition_name from user_segments where segment_name='HASH_PART';

TABLESPACE_NAME      SEGMENT_NAME         PARTITION_NAME
-------------------- -------------------- ------------------------------
TBS2                 HASH_PART            SYS_P22
TBS3                 HASH_PART            SYS_P23
TBS4                 HASH_PART            SYS_P24
TBS2                 HASH_PART            SYS_P25
TBS3                 HASH_PART            SYS_P26
TBS4                 HASH_PART            SYS_P27
TBS2                 HASH_PART            SYS_P28
TBS3                 HASH_PART            SYS_P29

8 rows selected.
SQL> SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='HASH_PART';

TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------
HASH_PART

指定partition 的儲存
SQL> create table hash_part(a number(10),b varchar2(100))
    partition by hash(a)
    (
    partition part_01 tablespace tbs2,
    partition part_02 tablespace tbs3,
    partition part_03 tablespace tbs4，
    );

Table created.

SQL> select tablespace_name,segment_name,partition_name from user_segments where segment_name='HASH_PART';

TABLESPACE_NAME      SEGMENT_NAME         PARTITION_NAME
-------------------- -------------------- ------------------------------
TBS3                 HASH_PART            PART_01
TBS4                 HASH_PART            PART_02
TBS5                 HASH_PART            PART_03

SQL> insert into hash_part select object_id,object_name from all_objects where object_id is not null;

4307 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from hash_part partition(part_01);

COUNT(*)
----------
1045

SQL> c/01/02
1* select count(*) from hash_part partition(part_02)
SQL> /

COUNT(*)
----------
2148 --如果分割槽不是2的冪，則資料分割槽不均勻

SQL> c/02/03
1* select count(*) from hash_part partition(part_03)
SQL> /

COUNT(*)
----------
1114

--測試hash table partition 的資料平均情況
SQL> create table hash_part(a number(10),b varchar2(100))
    partition by hash(a)
    ( partition part_01 tablespace tbs2,
    partition part_02 tablespace tbs3,
    partition part_03 tablespace tbs4,
    partition part_04 tablespace tbs5
    ); 2    3    4    5    6    7

Table created.

SQL> insert into hash_part select object_id,object_name from all_objects where object_id is not null;

4313 rows created.

SQL> commit;

Commit complete.

SQL> insert into hash_part select object_id,object_name from all_objects where object_id is not null;

4313 rows created. --hash 鍵值不是主鍵

SQL> commit;

Commit complete.

SQL> select count(*) from hash_part partition(part_01);

COUNT(*)
----------
2094

SQL> c/01/02;
1* select count(*) from hash_part partition(part_02)
SQL> /

COUNT(*)
----------
2192

SQL> c/02/03
1* select count(*) from hash_part partition(part_03)
SQL> /

COUNT(*)
----------
2236

SQL> c/03/04
1* select count(*) from hash_part partition(part_04)
SQL> /

COUNT(*)
----------
2104

--不是嚴格意義上的條數的平均。

--List partition
SQL> create table list_part(a number(10),b varchar2(100))
2 partition by list(b)
3 (
4 partition part_01 values('A'),
5 partition part_02 values('B'),
6 partition part_03 values('C')
7 );

SQL> INSERT INTO LIST_PART VALUES(2,'B');

1 row created.

SQL> INSERT INTO LIST_PART VALUES(3,'C');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> UPDATE LIST_PART SET B='B' WHERE A=3;
UPDATE LIST_PART SET B='B' WHERE A=3
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

--這種情況尤其要注意，因為改了值會影響分割槽，所以不支援這種操作。

--把表裡的資料清空。

SQL> insert into list_part select object_id,decode(mod(object_id,3),1,'A',2,'B','C') B from all_objects where object_id is not null;

4308 rows created.
1* select count(*) from list_part where b='B'
SQL> /

COUNT(*)
----------
1466

SQL> select count(*) from list_part partition(part_02);

COUNT(*)
----------
1466

SQL> alter table list_part add partition part_05 values(default); -list partition需要加default分割槽

Table altered.

SQL> create table range_hash_part(a number(10),b varchar2(100))
2 partition by range(a)
3 subpartition by hash(b)
4 subpartitions 4 store in (tbs2,tbs3)
5 (
6 partition part_01 values less than(1000),
7 partition part_02 values less than(4000),
8 partition part_03 values less than (6000),
9 partition part_04 values less than(8000),
10 partition part_max values less than(maxvalue)
11 subpartitions 2 store in (tbs3,tbs4)
12 );

Table created.
select partition_name,subpartition_count,high_value from user_tab_partitions where table_name='RANGE_HASH_PART'
/

PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE
------------------------------ ------------------ --------------------
PART_01                                         4 1000
PART_02                                         4 4000
PART_03                                         4 6000
PART_04                                         4 8000
PART_MAX                                        2 MAXVALUE

SQL> create table list_hash_part(a number(10),b varchar2(100))
2 partition by list(b)
3 subpartition by hash(a)
4 subpartitions 4 store in (tbs2,tbs4)
5 (partition part_01 values('A'),
6   partition part_02 values('B'),
7   partition part_03 values('C'),
8   partition part_04 values(default)
9   subpartitions 2 store in(tbs3,tbs5)
10 );
subpartition by hash(a)
*
ERROR at line 3:
ORA-00922: missing or invalid option

--這種分割槽不存在

--range_list
create table range_list_part(a number(10),b varchar2(100))
partition by range(a)
subpartition by list(b)
(
partition part_01 values less than(1000)
(subpartition part_01_01 values('A'),
subpartition part_01_02 values('B'),
subpartition part_01_03 values('C'),
subpartition part_01_04 values(default)
),
partition part_02 values less than(4000)
(subpartition part_02_01 values('A'),
subpartition part_02_02 values('B'),
subpartition part_02_03 values('C'),
subpartition part_02_04 values(default)
),
partition part_03 values less than(maxvalue)
(subpartition part_03_01 values(default)
)
)

SQL> select tablespace_name,partition_name,subpartition_count from user_tab_partitions where table_name='RANGE_LIST_PART';

TABLESPACE_NAME      PARTITION_NAME                 SUBPARTITION_COUNT
-------------------- ------------------------------ ------------------
TBS2                 PART_01                                         4
TBS2                 PART_02                                         4
TBS2                 PART_03                                         1

global_index
create index glb_ind_range_list_part on range_list_part(a)
global partition by range(a)
(partition part_01 values less than (1000) tablespace tbs2,
partition part_02 values less than(5000) tablespace tbs3,
partition part_03 values less than(maxvalue) tablespace tbs4
)

--non prefixed index
SQL> create index ind_range_list_part_nopre on range_list_part(b,a);

Index created.

SQL> select count(*) from range_hash_part partition(part_01);

COUNT(*)
----------
66

--rename partition
SQL> alter table range_hash_part rename partition part_01 to part_011;

Table altered.

SQL> alter table range_hash_part rename partition part_011 to part_01;

Table altered.

SQL> --truncate partition
SQL> alter table range_hash_part truncate partition part_01;

Table truncated.

--drop partition
SQL> alter table range_hash_part drop partition part_01;

Table altered.

SQL>

--move partition
SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_HASH_PART';

TABLESPACE_NAME                PARTITION_NAME
------------------------------ ------------------------------
TBS2                           PART_02
TBS2                           PART_03
TBS2                           PART_04
TBS2                           PART_MAX

--for range_hash partition
SQL> alter table range_hash_part move partition part_02 tablespace tbs3;
alter table range_hash_part move partition part_02 tablespace tbs3
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition

SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME                PARTITION_NAME
------------------------------ ------------------------------
TBS2                           PART_01
TBS2                           PART_02
TBS2                           PART_03

SQL> alter table range_part move partition part_01 tablespace tbs4;

Table altered.

SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME                PARTITION_NAME
------------------------------ ------------------------------
TBS4                           PART_01
TBS2                           PART_02
TBS2                           PART_03
TBS2                           PART_04

--merge partition
SQL> alter table range_part merge partitions part_01,part_02;

Table altered.
SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME                PARTITION_NAME
------------------------------ ------------------------------
TBS2                           PART_03
TBS2                           PART_04
TBS2                           SYS_P48
--指定merge後的partition名字
SQL> alter table range_part merge partitions part_03,part_04 into partition part_05;

Table altered.

SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME                PARTITION_NAME
------------------------------ ------------------------------
TBS2                           PART_05
TBS2                           SYS_P48

--split partition
SQL> /

TABLESPACE_NAME                HIGH_VALUE           PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2                           4000                 PART_01
TBS2                           MAXVALUE             PART_05

--for range partition tables
SQL> alter table range_part split partition part_01 at (2000) into (partition part_01,partition part_02);

Table altered.

SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME                HIGH_VALUE           PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2                           2000                 PART_01
TBS2                           4000                 PART_02
TBS2                           MAXVALUE             PART_05

--for list partition tables
SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='LIST_PART';

TABLESPACE_NAME                HIGH_VALUE           PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2                           'A'                  PART_01
TBS2                           'B'                  PART_02
TBS2                           'C'                  PART_03
TBS2                           default              PART_05

SQL> alter table list_part split partition part_02 values('D') into (partition part_06,partition part_07);
alter table list_part split partition part_02 values('D') into (partition part_06,partition part_07)
*
ERROR at line 1:
ORA-14314: resulting List partition(s) must contain atleast 1 value

--這樣來split不正確,有多個可選值才可以

--來模擬校正一下

SQL> alter table list_part merge partitions part_02,part_03 into partition part_02;

Table altered.

SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='LIST_PART';

TABLESPACE_NAME                HIGH_VALUE           PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2                           'A'                  PART_01
TBS2                           'C', 'B'             PART_02
TBS2                           default              PART_05
--這樣list partition就可以split了
SQL> alter table list_part split partition part_02 values('C') into (partition part_02,partition part_03);

Table altered.
SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='LIST_PART';

TABLESPACE_NAME                HIGH_VALUE           PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2                           'A'                  PART_01
TBS2                           'C'                  PART_02
TBS2                           'B'                  PART_03
TBS2                           default              PART_05

--add values to list partition
SQL> alter table list_part modify partition part_03 add values('D');

Table altered.
SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='LIST_PART';

TABLESPACE_NAME                HIGH_VALUE           PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2                           'A'                  PART_01
TBS2                           'C'                  PART_02
TBS2                           'B', 'D'             PART_03
TBS2                           default              PART_05

SQL> alter table list_part modify partition part_03 drop values('B');
alter table list_part modify partition part_03 drop values('B')
*
ERROR at line 1:
ORA-14518: partition contains rows corresponding to values being dropped
--如果parition對應的values下有資料，則drop values會失敗

SQL> c/'B'/'D'
1* alter table list_part modify partition part_03 drop values('D')
SQL> /

Table altered.

--exchange partition

SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME                HIGH_VALUE           PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2                           2000                 PART_01
TBS2                           4000                 PART_02
TBS2                           MAXVALUE             PART_05

SQL> create table exchange_test as select object_id,object_name from all_objects where object_id <3000;

Table created.

SQL> select count(*) from range_part partition(part_02);

COUNT(*)
----------
1256
SQL> alter table range_part exchange partition part_02 with table exchange_test;
alter table range_part exchange partition part_02 with table exchange_test
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

--列名要求必須一致
--刪掉重新模擬
SQL> drop table exchange_test;

Table dropped.
SQL> create table exchange_test (a number(10),b varchar2(100));

Table created.

SQL> insert into exchange_test select object_id ,object_name from all_objects where object_id <3000
2 /

1300 rows created.
SQL> alter table range_part exchange partition part_02 with table exchange_test;
alter table range_part exchange partition part_02 with table exchange_test
*
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition
--預設會校驗值的有效性，2000<3000<4000,所以對於小於2000的值，校驗會失敗。

SQL> alter table range_part exchange partition part_02 with table exchange_test
2 without validation;

Table altered.

SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME                HIGH_VALUE           PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2                           2000                 PART_01
TBS2                           4000                 PART_02
TBS2                           MAXVALUE             PART_05
--沒有變化
--檢視exchange 後的資料量
SQL> select count(*) from exchange_test;

COUNT(*)
----------
1256 --和交換前partition part_02的條數一致

--檢視交換後的partition的數量

SQL> select count(*) from range_part partition(part_02);

COUNT(*)
----------
1300 -和建表時的資料條數一致
--再交換回來

SQL> alter table range_part exchange partition part_02 with table exchange_test;

Table altered.

--當然直接插入值也可以
SQL> insert into range_part select * from exchange_test;

1300 rows created.

最後說一下分割槽的匯入，匯出

[oracle@oel1 ~]$ exp hr/hr tables=range_part:part_02 file=range_part_02.dmp

Export: Release 10.2.0.1.0 - Production on Wed Oct 17 20:37:26 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table RANGE_PART
. . exporting partition PART_02 1256 rows exported
Export terminated successfully without warnings.

[oracle@oel1 ~]$ imp hr/hr tables=range_part:part_02 file=range_part_02 ignore=y

Import: Release 10.2.0.1.0 - Production on Wed Oct 17 20:40:32 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into HR
. importing HR's objects into HR
. . importing partition "RANGE_PART":"PART_02" 1256 rows imported
Import terminated successfully without warnings.

分割槽表學習筆記

相關文章