转:分区表与分区索引（二）：范围分区表的数据管理-huaihe0410-ChinaUnix博客

huaihe0410

首页　| 　博文目录　| 　关于我

huaihe0410

博客访问： 1379914
博文数量： 247
博客积分： 10147
博客等级：上将
技术积分： 2776
用户组：普通用户
注册时间： 2008-01-24 15:18

文章分类

全部博文（247）

svn（1）
AIX（1）
协议（2）
编码（8）
测试（10）
编译（0）
python（22）

socket（1）

中文字符（4）

smtp（1）
resin/java（3）
jsp（2）
其他（3）
mysql（22）

cluster（3）
linux/unix（68）

linux性能指令（5）

磁盘（3）

cvs（4）

shell（6）

指令（19）

网络（8）
oracle（92）

oracle字符集（6）

PL/SQL（1）

Oracle9i初始化参（15）

oracle 并行（2）

oracle1011新特性（6）

oracle函数（5）

oracle索引组织表（4）

oracle分区表（9）

oracle性能优化（21）
未分配的博文（13）

文章存档

2013年（11）

2012年（3）

2011年（20）

2010年（35）

2009年（91）

2008年（87）

我的朋友

jiayanfu

相关博文

转:分区表与分区索引（二）：范围分区表的数据管理

分类： Oracle

2008-01-24 15:23:53

--0.重建分区表
SQL> drop table testrp;

表已丢弃。

SQL> create table testrp(a number(10), b varchar2(10))
2 partition by range(a)(
3 partition testrp1 values less than (51),
4 partition testrp2 values less than (81)
5 )
6 tablespace tp_p1;

表已创建。

--1.插入正常范围的数据

SQL> insert into testrp values(1,'1');

已创建 1 行。

SQL> insert into testrp values(50,'50');

已创建 1 行。

SQL> insert into testrp values(51,'51');

已创建 1 行。

SQL> insert into testrp values(80,'80');

已创建 1 行。

SQL> commit;

提交完成。

--2.插入异常范围的数据

SQL> insert into testrp values(81,'81');
insert into testrp values(81,'81')
*
ERROR 位于第 1 行:
ORA-14400: 插入的分区关键字未映射到任何分区

SQL> insert into testrp values(null,null);
insert into testrp values(null,null)
*
ERROR 位于第 1 行:
ORA-14400: 插入的分区关键字未映射到任何分区

--3.增加一个maxvalue分区，null值插入到了该分区
SQL> alter table testrp add partition testrp3 values less than (maxvalue) tablespace tp_p3;

表已更改。

SQL> insert into testrp values(81,'81');

已创建 1 行。

SQL> commit;

提交完成。

SQL> insert into testrp values(null,null);

已创建 1 行。

SQL> commit;

提交完成。

--4.可以查询总数据和指定分区的数据（查询时指定分区，能大大提高查询效率）

SQL> select * from testrp;

         A B
---------- ----------
         1 1
        50 50
        51 51
        80 80
        81 81

已选择6行。

SQL> select * from testrp partition(testrp1);

         A B
---------- ----------
         1 1
        50 50

SQL> select * from testrp partition(testrp2);

         A B
---------- ----------
        51 51
        80 80

SQL> select * from testrp partition(testrp3);

A B
---------- ----------
81 81

--5.更新操作也可指定分区，能提高查询效率。

SQL> update testrp partition(testrp1) set a = 11 where a is null;

已更新0行。

SQL> update testrp partition(testrp3) set b = '81_new' where a = 81;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from testrp partition(testrp3);

A B
---------- ----------
81 81_new

--但不可以更新分区键值，所以分区键值的选择要慎重。

--如果确实需要更新分区键值，可以先删除，再插入。
SQL> update testrp partition(testrp3) set a = 2 where a is null;
update testrp partition(testrp3) set a = 2 where a is null
*
ERROR 位于第 1 行:
ORA-14402: 更新分区关键字列将导致分区的更改

--6.删除操作也是一样的

SQL> delete from testrp partition(testrp3);

已删除2行。

SQL> select * from testrp;

         A B
---------- ----------
         1 1
        50 50
        51 51
        80 80

SQL> rollback;

回退已完成。

--7.我们看一下对于存在数据的分区表，做分区合并、分裂、删除等操作对于数据的影响。

--7.1合并：被合并的分区已经不存在，该分区中的数据被迁移到了合并后分区中。
SQL> select * from testrp;

         A B
---------- ----------
         1 1
        50 50
        51 51
        80 80
        81 81_new

已选择6行。

SQL> select * from testrp partition(testrp1);

         A B
---------- ----------
         1 1
        50 50

SQL> select * from testrp partition(testrp2);

         A B
---------- ----------
        51 51
        80 80

SQL> select * from testrp partition(testrp3);

A B
---------- ----------
81 81_new

SQL> alter table testrp merge partitions testrp2, testrp3 into partition testrp2;
alter table testrp merge partitions testrp2, testrp3 into partition testrp2
*
ERROR 位于第 1 行:
ORA-14275: 不能将下界分区作为结果分区重用

SQL> alter table testrp merge partitions testrp2, testrp3 into partition testrp3;

表已更改。

SQL> select * from testrp;

         A B
---------- ----------
         1 1
        50 50
        51 51
        80 80
        81 81_new

已选择6行。

SQL> select * from testrp partition(testrp1);

         A B
---------- ----------
         1 1
        50 50

SQL> select * from testrp partition(testrp2);
select * from testrp partition(testrp2)
*
ERROR 位于第 1 行:
ORA-02149: 指定的分区不存在

SQL> select * from testrp partition(testrp3);

         A B
---------- ----------
        51 51
        80 80
        81 81_new

--7.2分裂：分裂后，该分区中的数据按照分区键值分配到新分区中。

SQL> alter table testrp split partition testrp3 at(81) into (
2 partition testrp2 tablespace tp_p2,
3 partition testrp3 tablespace tp_p3
4 );

表已更改。

SQL> select * from testrp partition(testrp2);

         A B
---------- ----------
        51 51
        80 80

SQL> select * from testrp partition(testrp3);

A B
---------- ----------
81 81_new

--7.3删除：删除分区后，该分区中的数据被一并删除。慎重使用。

SQL> alter table testrp drop partition testrp3;

表已更改。

SQL> select * from testrp;

         A B
---------- ----------
         1 1
        50 50
        51 51
        80 80

SQL> select * from testrp partition(testrp1);

         A B
---------- ----------
         1 1
        50 50

SQL> select * from testrp partition(testrp2);

         A B
---------- ----------
        51 51
        80 80

SQL> select * from testrp partition(testrp3);
select * from testrp partition(testrp3)
*
ERROR 位于第 1 行:
ORA-02149: 指定的分区不存在

--7.4重命名：对数据没有影响。

SQL> alter table testrp rename partition testrp2 to testrp02

表已更改。

SQL> select * from testrp;

         A B
---------- ----------
         1 1
        50 50
        51 51
        80 80

SQL> select * from testrp partition(testrp1);

         A B
---------- ----------
         1 1
        50 50

SQL> select * from testrp partition(testrp2);
select * from testrp partition(testrp2)
*
ERROR 位于第 1 行:
ORA-02149: 指定的分区不存在

SQL> select * from testrp partition(testrp02);

         A B
---------- ----------
        51 51
        80 80

--8.可以截短指定分区

SQL> alter table testrp truncate partition testrp02;

表已截掉。

SQL> select * from testrp;

         A B
---------- ----------
         1 1
        50 50

SQL> select * from testrp partition(testrp1);

         A B
---------- ----------
         1 1
        50 50

SQL> select * from testrp partition(testrp02);

未选定行

--9.交换分区测试

--9.1 用法

SQL> insert into testrp values(51, '51');

已创建 1 行。

SQL> insert into testrp values(80, '80');

已创建 1 行。

SQL> commit;

提交完成。

SQL> create table testrp_ex as select * from testrp where 1 = 0;

表已创建。

SQL> insert into testrp_ex values(70, '70');

已创建 1 行。

SQL> commit;

提交完成。

SQL> alter table testrp exchange partition testrp02 with table testrp_ex;

表已更改。

SQL> select * from testrp_ex;

         A B
---------- ----------
        51 51
        80 80

SQL> select * from testrp partition(testrp02);

A B
---------- ----------
70 70

--9.2 我们把数据交换回去，再测试一下交换分区的限制

--9.2.1被交换的表里的数据必须在交换分区的范围内

SQL> alter table testrp exchange partition testrp02 with table testrp_ex;

表已更改。

SQL> insert into testrp_ex values(30,'30');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from testrp_ex;

         A B
---------- ----------
        70 70
        30 30

SQL> alter table testrp exchange partition testrp02 with table testrp_ex;
alter table testrp exchange partition testrp02 with table testrp_ex
*
ERROR 位于第 1 行:
ORA-14099: 未对指定分区限定表中的所有行

SQL> delete from testrp_ex where a = 30;

已删除 1 行。

SQL> commit;

提交完成。

--9.2.2 要交换的表和分区列数必须相同，但列名可以不同

SQL> alter table testrp_ex add c varchar2(10);

表已更改。

SQL> alter table testrp exchange partition testrp02 with table testrp_ex;
alter table testrp exchange partition testrp02 with table testrp_ex
*
ERROR 位于第 1 行:
ORA-14096: ALTER TABLE EXCHANGE PARTITION 中的表必须具有相同的列数

SQL> desc testrp
名称                                      是否为空? 类型
----------------------------------------- -------- --------------------------
A                                                  NUMBER(10)
B                                                  VARCHAR2(10)

SQL> drop table testrp_ex;

表已丢弃。

SQL> create table testrp_ex ( aa number(10), bb varchar2(10));

表已创建。

SQL> insert into testrp_ex values(70,'70');

已创建 1 行。

SQL> commit;

提交完成。

SQL> alter table testrp exchange partition testrp02 with table testrp_ex;

表已更改。

SQL> select * from testrp_ex;

        AA BB
---------- ----------
        51 51
        80 80

SQL> select * from testrp partition(testrp02);

A B
---------- ----------
70 70

SQL>

发表于： 2007-12-20，修改于： 2007-12-20 14:02 已浏览211次，有评论1条推荐投诉

网友评论

yuechaotian

时间：2007-12-23 20:19:28 IP地址：121.22.40.★

上次测试中的这段话是错误的：

--但不可以更新分区键值，所以分区键值的选择要慎重。

--如果确实需要更新分区键值，可以先删除，再插入。



这是对于oracle8.0来说的，而对于8i及以后版本，oracle提供了该功能。下面我们测试一下：



--1.再复习一下初始环境



SQL> select *from testrp;



         A B

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

         1 1

        50 50

        51 51

        80 80



SQL> select partition_name from user_tab_partitions where table_name = 'TESTRP';



PARTITION_NAME

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

TESTRP2

TESTRP1



SQL> select * from testrp partition(testrp1);



         A B

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

         1 1

        50 50



SQL> select * from testrp partition(testrp2);



         A B

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

        51 51

        80 80



--2.更新分区键值，更新后仍在该分区中：可以执行

SQL> update testrp set a = 11 where a = 1;



已更新 1 行。



SQL> commit;



提交完成。



SQL> select a, b, rowid from testrp partition(testrp1);



         A B          ROWID

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

        11 1          AAAHmAAANAAAAAMAAA

        50 50         AAAHmAAANAAAAAMAAB



--3.更新分区键值，更新后跨分区：此时不能执行

SQL> update testrp set a = 60 where a = 11;

update testrp set a = 60 where a = 11

       *

ERROR 位于第 1 行:

ORA-14402: 更新分区关键字列将导致分区的更改



--4. 8i开始，提供了允许行移动的功能：更新后rowid发生了变化

SQL> alter table testrp enable row movement;



表已更改。



SQL> update testrp set a = 60 where a = 11;



已更新 1 行。



SQL> commit;



提交完成。



SQL> select a, b, rowid from testrp partition(testrp1);



         A B          ROWID

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

        50 50         AAAHmAAANAAAAAMAAB



SQL> select a, b, rowid from testrp partition(testrp2);



         A B          ROWID

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

        51 51         AAAHmiAAOAAAAAMAAA

        80 80         AAAHmiAAOAAAAAMAAB

        60 1          AAAHmiAAOAAAAAQAAA



--rowid发生了变化，其实也就表示该行数据的存储位置发生了变化

SQL> set serveroutput on

SQL> exec my_tool.show_rowid('AAAHmAAANAAAAAMAAA')

Rowid Type..............................1

Object Number...........................31104

Relative Fno............................13

Block Number............................12

Row Number..............................0



PL/SQL 过程已成功完成。



SQL> exec my_tool.show_rowid('AAAHmiAAOAAAAAQAAA')

Rowid Type..............................1

Object Number...........................31138

Relative Fno............................14

Block Number............................16

Row Number..............................0



PL/SQL 过程已成功完成。



SQL> alter table testrp disable row movement;



表已更改。



总结：该操作其实也是个删除再插入的过程。不过oracle是把它作为一个update来执行的，执行该语句时，不会触发delete和insert的触发器，而是触发update触发器（如果存在触发器的话）。

行移动的开销比正常的update昂贵得多。因此，如果构建的系统会频繁修改分区键值，而且这种修改会导致行移动，这实在是一个糟糕的设计决策。

阅读(2442) | 评论(0) | 转发(0) |

上一篇：转:分区表与分区索引（一）：范围分区表的操作

下一篇：分区表

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6