分区表学习笔记(83天)

今天总结了一下分区表的知识

分享一下。

大体有以下内容:

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
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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.

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-05-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏王磊的博客

sql无限递归查询

--------------所有子集数据包括自己--------------------- CREATE PROCEDURE ALLSON @ID INT AS...

2083
来自专栏杨建荣的学习笔记

关于分区表的在线重定义(r5笔记第98天)

目前项目中有一个问题,存在一个分区表,因为分区规则的问题,使得分区表中的数据分布很不均匀,数据都分区在了默认的maxvalue分区上。现在需要重新划分分区。从常...

27810
来自专栏散尽浮华

openstack虚拟机迁移的操作记录

需求说明: 计算节点linux-node1.openstack:192.168.1.8   计算节点linux-node2.openstack:192.168....

4559
来自专栏张善友的专栏

CentOS设置Mono环境变量

GitHub上有直接通过脚本一键安装Mono的脚本,具体参见 https://github.com/nathanb/iws-snippets/tree/mast...

1655
来自专栏大数据学习笔记

Elasticsearch 5.x 安装与配置

Elasticsearch官方建议使用 Oracle的JDK8 1、下载安装 [root@vnode0 opt]# tar -zxvf elasticsearc...

3168
来自专栏开发与安全

win 7 + VC++ 2008 express + OpenCv 2.1.0安装

官方文档并注释: These instructions were tested with Visual C++ 9.0 (as part of Visual ...

18110
来自专栏乐沙弥的世界

Oracle cloud control 12c 如何修改sysman密码

    前阵子在虚拟机部署了Oracle Cloud Control 12c,事别几日,竟然忘记了登陆密码。主要是因为现在的Oracle有关的Software...

631
来自专栏PhpZendo

使用 MySQL Scheduler 和 Event 周期性创建数据表

使用 MySQL Scheduler 和 Event 周期性创建数据表,下面提供的是按月建表计划任务及事件通过 ON SCHEDULE EVERY 1 MINU...

942
来自专栏杨建荣的学习笔记

分区索引学习笔记 (77天)

续接上次的分区表学习笔记,对分区索引进行了总结。 --index maintance SQL> select index_name,table_name fro...

2066
来自专栏杨建荣的学习笔记

使用awk来解析dump文件 (73天)

dump文件是平时工作中经常碰见的,有时候得到一个dump,但是没有提供一些更多的信息,导入的时候就很可能会有问题。 如果某个用户默认表空间是user,但是du...

3508

扫码关注云+社区