Oracle分区表创建

最新推荐文章于 2024-01-03 00:30:40 发布

莫凡的博客

最新推荐文章于 2024-01-03 00:30:40 发布

阅读量752

点赞数

文章标签：分区表 oracle

本文链接：https://blog.csdn.net/mozf881/article/details/50189065

版权

数据库专栏收录该内容

0 篇文章 0 订阅

订阅专栏

一、分区表的创建
1、创建范围分区

create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
           partition by range (deal_date)
           (
           partition p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
           partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
           partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
           partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
           partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
           partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
           partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),
           partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),
           partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),
           partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),
           partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),
           partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),
           partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),
           partition p_201402 values less than (TO_DATE('2014-03-01', 'YYYY-MM-DD')),
           partition p_max values less than (maxvalue)
           )
           ;

2、创建列表分区

create table list_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
           partition by list (area_code)
           (
           partition p_591 values  (591),
           partition p_592 values  (592),
           partition p_593 values  (593),
           partition p_594 values  (594),
           partition p_595 values  (595),
           partition p_596 values  (596),
           partition p_597 values  (597),
           partition p_598 values  (598),
           partition p_599 values  (599),
           partition p_other values  (DEFAULT)
           )
           ;

3、创建HASH分区

create table hash_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
            partition by hash (deal_date)
            PARTITIONS 12
            ;```



4、联合字段范围分区

create table range_part_mult_col_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (area_code,deal_date)
(
partition p_591_201312 values less than (591,TO_DATE(‘2014-01-01’, ‘YYYY-MM-DD’)),
partition p_591_201401 values less than (591,TO_DATE(‘2014-02-01’, ‘YYYY-MM-DD’)),
partition p_591_201402 values less than (591,TO_DATE(‘2014-03-01’, ‘YYYY-MM-DD’)),
partition p_591_max values less than (591,maxvalue),
partition p_592_201312 values less than (592,TO_DATE(‘2014-01-01’, ‘YYYY-MM-DD’)),
partition p_592_201401 values less than (592,TO_DATE(‘2014-02-01’, ‘YYYY-MM-DD’)),
partition p_592_201402 values less than (592,TO_DATE(‘2014-03-01’, ‘YYYY-MM-DD’)),
partition p_592_max values less than (592,maxvalue),
partition p_593_201401 values less than (593,TO_DATE(‘2014-02-01’, ‘YYYY-MM-DD’)),
partition p_593_201402 values less than (593,TO_DATE(‘2014-03-01’, ‘YYYY-MM-DD’)),
partition p_593_max values less than (593,maxvalue)
)
;

“`

二、注意事项
1、分区表默认不允许跨分区移到的，如果更新表导致记录会跨分区移到，将会报如下的错误
ORA-14402: 更新分区关键字列将导致分区的更改
避免该错误，执行：
alter table part_tab_rowid enable row movement;

2、记录跨分区移到后，rowid将会改变

3、只查询某一个分区数据
select * from 表名 partition(分区名)