Oracle分区表创建

一、分区表的创建
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(分区名)

### 创建分区表Oracle数据库中的方法 #### 使用范围分区 要基于某个列的值区间来划分数据,可以采用如下SQL语句: ```sql CREATE TABLE sales_range ( prod_id NUMBER(6), cust_id NUMBER, time_id DATE, channel_id CHAR(1), promo_id NUMBER(6), quantity_sold NUMBER(3), amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) ( PARTITION sales_jan VALUES LESS THAN (TO_DATE('02/01/2020', 'MM/DD/YYYY')), PARTITION sales_feb VALUES LESS THAN (TO_DATE('03/01/2020', 'MM/DD/YYYY')), PARTITION sales_mar VALUES LESS THAN (TO_DATE('04/01/2020', 'MM/DD/YYYY')) ); ``` 此命令定义了一个名为`sales_range`的新表格,并按照日期字段`time_id`进行了范围分区[^1]。 #### 列列表分区 当希望依据特定离散值集合来进行分割时,则可利用列表分区方式: ```sql CREATE TABLE customers_list ( customer_id NUMERIC(6), cust_first_name VARCHAR2(20), cust_last_name VARCHAR2(20), cust_state CHAR(2), credit_limit NUMBER ) PARTITION BY LIST (cust_state)( PARTITION east_region VALUES ('NY','NJ'), PARTITION west_region VALUES ('CA','WA') ); ``` 这段脚本创建了一张客户信息表并按州名进行列表分区。 #### 哈希分区 对于那些想要均匀分布记录而不考虑具体键值的情况来说,哈希分区是一个不错的选择: ```sql CREATE TABLE orders_hash ( order_id NUMBER(12), order_date DATE, order_mode VARCHAR2(8), customer_id NUMBER(6), order_status NUMBER(2), order_total NUMBER(8,2), sales_rep_id NUMBER(6) ) PARTITION BY HASH (order_id) PARTITIONS 4; ``` 这里展示了怎样通过指定列作为输入参数给定哈希函数从而实现自动分配到四个不同分区内存储订单详情的数据结构设计思路。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值