分区表学习笔记(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 server 2005 切换分区表

如转载,请注明出处:http://blog.csdn.net/robinson_0612/archive/2009/11/10/4794371.aspx

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

通过shell脚本得到数据字典的信息 (r2笔记72天)

在平时的工作中,可能需要查询一些数据字典的信息,比如数据字典对应的基表信息,可以得到更多数据库内部的一些详细信息。 比如user_objects这个数据字典视图...

2687
来自专栏清风

Oracle OCI 编译 原

412
来自专栏乐沙弥的世界

使用exchange方式切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

531
来自专栏你不就像风一样

Mysql字段名与保留字冲突导致的异常解决

512
来自专栏乐沙弥的世界

Oracle字符集与字符类型存储空间占用

532
来自专栏Jerry的SAP技术分享

如何查看某个用户指定时间段的ABAP开发记录

输入用户名和想查询的时间段: [1240] 执行得到结果。双击可查看具体代码: [1240] 工具源代码: REPORT tool_dev_history. ...

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

一条sql语句的改进探索(r5笔记第70天)

昨天同事找我,让我帮忙看两个sql问题,第一个问题是一个sql语句执行频率极高,但是目前的执行速度还是比较慢,希望我看看能不能调优一下。 另外一个问题是一个查询...

2808
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

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

物化视图相关的性能改进 (r7笔记第58天)

今天早上开发的一个同事找到我说他早上做了一个统计查询,但是感觉速度很慢,已经过了一个小时了还没有反应。想让我看看是什么情况。 我通过v$session查到有一个...

3255

扫描关注云+社区