oracle创建时间分区

 

例如我们要对一张表按照时间创建分区：

 

create table cmp
   (
        transaction_id number primary key,
        item_id number(8) not null,
       item_description varchar2(300),
       transaction_date date not null  
  )
    partition by range (transaction_date)
    (
   partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) tablespace dinya_space01,
   partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace dinya_space02,
  partition part_03 values less than(maxvalue) tablespace dinya_space03
  );

  这样我们就分别建了以交易序号和交易日期来分区的分区表。每次插入数据的时候，系统将根据指定的字段的值来自动将记录存储到制定的分区（表空间）中。

当然，我们还可以根据需求，使用两个字段的范围分布来分区，如partition by range ( transaction_id ,transaction_date),分区条件中的值也做相应的改变，请读者自行测试。

按天创建分区：

create table anoscfg.test_123
(
begin_time	date
)
partition by RANGE  (BEGIN_TIME)
(
  partition p1 values less than (to_date('2012-05-13', 'yyyy-mm-dd')),
  partition p2 values less than (to_date('2012-05-14', 'yyyy-mm-dd')),
  partition p3 values less than (to_date('2012-05-15', 'yyyy-mm-dd')),
  partition p4 values less than (to_date('2012-05-16', 'yyyy-mm-dd')),
  partition p5 values less than (to_date('2012-05-17', 'yyyy-mm-dd'))
);

 

insert into anoscfg.test_123(BEGIN_TIME) values(to_date('2012-05-12', 'yyyy-mm-dd'));

insert into anoscfg.test_123(BEGIN_TIME) values(to_date('2012-05-13', 'yyyy-mm-dd'));
insert into anoscfg.test_123(BEGIN_TIME) values(to_date('2012-05-14', 'yyyy-mm-dd'));
insert into anoscfg.test_123(BEGIN_TIME) values(to_date('2012-05-15', 'yyyy-mm-dd'));
insert into anoscfg.test_123(BEGIN_TIME) values(to_date('2012-05-16', 'yyyy-mm-dd'));

 查询分区：

select * from test_123 partition(p4);