分区表的基本操作
来源:互联网 发布:屏蔽他人手机信号软件 编辑:程序博客网 时间:2024/05/21 08:37
分区表的基本操作,简单记录一下~~~
实例:
SQL> create table t_part (id int,col2 int,col3 int)
2 partition by range (id)
3 (
4 partition p1 values less than (10000),
5 partition p2 values less than (20000),
6 partition p3 values less than (30000),
7 partition p4 values less than (40000),
8 partition p5 values less than (50000),
9 partition p6 values less than (60000),
10 partition p7 values less than (70000),
11 partition p8 values less than (80000),
12 partition p9 values less than (90000),
13 partition p10 values less than (100000),
14 partition p11 values less than (110000),
15 partition p12 values less than (maxvalue)
16 );
Table created.
SQL> insert into t_part select rownum,rownum+1,rownum+2 from dual connect by rownum < 150000;
149999 rows created.
SQL> commit;
Commit complete.
1、分区清除,分区删除
alter table [partiontion_tablename] drop/truncate partition [partitionname];
SQL> alter table t_part drop partition p1;
Table altered.
SQL> alter table t_part truncate partition p2;
Table truncated.
2、增加分区
alter table [partiontion_tablename] add partition [partitionname] values less than (120000);
在分区有maxvalue时会报一下错误,需要先把maxvalue分区的数据分区交换到一个中间表,drop 掉该分区之后,新建分区,最后把改中间表的数据重新插入该分区表。
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> alter table t_part add partition p12 values less than (120000);
Table altered.
3、分区交换:
alter table [partiontion_tablename] exchange partition [partitionname] with table [tablename];
SQL> create table t_norm (id int,col2 int,col3 int);
Table created.
SQL> select count(*) from t_part partition(p12);
COUNT(*)
----------
40000
SQL> alter table t_part exchange partition p12 with table t_norm;
Table altered.
SQL> select count(*) from t_part partition(p12);
COUNT(*)
----------
0
SQL> select count(*) from t_norm;
COUNT(*)
----------
40000
同理,我们可以把t_norm表的数据交换到p12分区中。
SQL> alter table t_part exchange partition p12 with table t_norm;
Table altered.
SQL> select count(*) from t_part partition(p12);
COUNT(*)
----------
40000
SQL> select count(*) from t_norm;
COUNT(*)
----------
0
4、分区切割
alter table [partiontion_tablename] split partition [partitionname] at ([values]) into (partition [partitionname1],partition [partitionname2]); --partitionname1,2是一分为二后新的名字
SQL> alter table t_part rename partition p12 to pmax;
Table altered.
SQL> select count(*) from t_part partition (pmax);
COUNT(*)
----------
40000
SQL> alter table t_part split partition pmax at (120000) into (partition p12,partition pmax);
Table altered.
SQL> select count(*) from t_part partition(p12);
COUNT(*)
----------
10000
SQL> select count(*) from t_part partition (pmax);
COUNT(*)
----------
30000
5、分区合并
alter table [partiontion_tablename] merge partitions [partitionname1],[partitionname2] into partition [partitionname_new];
SQL> alter table t_part merge partitions p12,pmax into partition pmax;
Table altered.
SQL> select count(*) from t_part partition (pmax);
COUNT(*)
----------
40000
实例:
SQL> create table t_part (id int,col2 int,col3 int)
2 partition by range (id)
3 (
4 partition p1 values less than (10000),
5 partition p2 values less than (20000),
6 partition p3 values less than (30000),
7 partition p4 values less than (40000),
8 partition p5 values less than (50000),
9 partition p6 values less than (60000),
10 partition p7 values less than (70000),
11 partition p8 values less than (80000),
12 partition p9 values less than (90000),
13 partition p10 values less than (100000),
14 partition p11 values less than (110000),
15 partition p12 values less than (maxvalue)
16 );
Table created.
SQL> insert into t_part select rownum,rownum+1,rownum+2 from dual connect by rownum < 150000;
149999 rows created.
SQL> commit;
Commit complete.
1、分区清除,分区删除
alter table [partiontion_tablename] drop/truncate partition [partitionname];
SQL> alter table t_part drop partition p1;
Table altered.
SQL> alter table t_part truncate partition p2;
Table truncated.
2、增加分区
alter table [partiontion_tablename] add partition [partitionname] values less than (120000);
在分区有maxvalue时会报一下错误,需要先把maxvalue分区的数据分区交换到一个中间表,drop 掉该分区之后,新建分区,最后把改中间表的数据重新插入该分区表。
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> alter table t_part add partition p12 values less than (120000);
Table altered.
3、分区交换:
alter table [partiontion_tablename] exchange partition [partitionname] with table [tablename];
SQL> create table t_norm (id int,col2 int,col3 int);
Table created.
SQL> select count(*) from t_part partition(p12);
COUNT(*)
----------
40000
SQL> alter table t_part exchange partition p12 with table t_norm;
Table altered.
SQL> select count(*) from t_part partition(p12);
COUNT(*)
----------
0
SQL> select count(*) from t_norm;
COUNT(*)
----------
40000
同理,我们可以把t_norm表的数据交换到p12分区中。
SQL> alter table t_part exchange partition p12 with table t_norm;
Table altered.
SQL> select count(*) from t_part partition(p12);
COUNT(*)
----------
40000
SQL> select count(*) from t_norm;
COUNT(*)
----------
0
4、分区切割
alter table [partiontion_tablename] split partition [partitionname] at ([values]) into (partition [partitionname1],partition [partitionname2]); --partitionname1,2是一分为二后新的名字
SQL> alter table t_part rename partition p12 to pmax;
Table altered.
SQL> select count(*) from t_part partition (pmax);
COUNT(*)
----------
40000
SQL> alter table t_part split partition pmax at (120000) into (partition p12,partition pmax);
Table altered.
SQL> select count(*) from t_part partition(p12);
COUNT(*)
----------
10000
SQL> select count(*) from t_part partition (pmax);
COUNT(*)
----------
30000
5、分区合并
alter table [partiontion_tablename] merge partitions [partitionname1],[partitionname2] into partition [partitionname_new];
SQL> alter table t_part merge partitions p12,pmax into partition pmax;
Table altered.
SQL> select count(*) from t_part partition (pmax);
COUNT(*)
----------
40000
0 0
- 分区表的基本操作
- ORACLE分区表的基本操作
- oracle分区表基本操作
- 关于分区表的操作
- ORACLE分区表的操作应用
- ORACLE分区表的操作应用
- Hive中分区表的操作
- 分区表与分区索引(一):范围分区表的操作
- 关于ORACLE分区表的概念及操作
- 分区表分区字段的update操作
- ORACLE分区表的概念及操作
- ORACLE分区表的概念及操作
- Oracle 数据库分区表的创建和操作
- 分区表的建立语法及常用操作
- 05分布式数据仓库 HIVE -- 分区表的操作
- Oracle分区表的概念及操作
- ORACLE分区表的概念及操作
- Hive学习笔记 --- Hive分区表的操作
- android 启动相机拍照并发送微博。
- 怎么恢复三星全智能手机数据
- 制作一个链表的方法
- 紅粉俏佳人
- 只能在执行 Render() 的过程中调用 RegisterForEventValidation(RegisterForEventValidation can only be called durin
- 分区表的基本操作
- Could not open key: ***** Verify that you have sufficient access to that key
- Android中AsyncTask的简单用法
- J2EE框架下几个通用功能介绍
- Tomcat7.0配备,解决点击startup.bat后闪退的情况
- 哈哈,程序猿
- easyui validatebox 验证集合
- 淘宝技术发展(Java时代:创造技术-TFS)
- Linux学习之CentOS(十三)--CentOS6.4下Mysql数据库的安装与配置