分区表-RANGE

1、创建普通表

create table p_temp(id number,init_date date);

 2、往普通表中插入数据

set serveroutput on

declare
    init_date date := to_date('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
begin
        
    for i in 0..364 loop
    
    insert into p_temp values(i + 1 , init_date + i);
    
    end loop;

    commit;
    
end;

/

 3、

创建分区表

分区表的管理与维护

create table p_tab(id number,trn_date date) partition by range(trn_date)
(
     partition p_1                   values less than(to_date('2014-02-01','YYYY-MM-DD')),
     partition p_2                   values less than(to_date('2014-03-01','YYYY-MM-DD')),
     partition p_3                   values less than(to_date('2014-04-01','YYYY-MM-DD')),
     partition p_4                   values less than(to_date('2014-05-01','YYYY-MM-DD')),
     partition p_5                   values less than(to_date('2014-06-01','YYYY-MM-DD')),
     partition p_6                   values less than(to_date('2014-07-01','YYYY-MM-DD')),
     partition p_7                   values less than(to_date('2014-08-01','YYYY-MM-DD')),
     partition p_8                   values less than(to_date('2014-09-01','YYYY-MM-DD')),
     partition p_9                   values less than(to_date('2014-10-01','YYYY-MM-DD')),
     partition p_10                  values less than(to_date('2014-11-01','YYYY-MM-DD')),
     partition p_11                  values less than(to_date('2014-12-01','YYYY-MM-DD')),
     partition p_12                  values less than(to_date('2015-01-01','YYYY-MM-DD')),
     partition p_maxvalue             values less than(maxvalue)
);

 4、向分区表中插入数据

SQL> insert into p_tab select * from p_temp;

365 rows created.

SQL> commit;

Commit complete.

 5、查询某个分区表中的数据

select * from p_tab partition(p_1);
select * from p_tab partition(p_2);
select * from p_tab partition(p_3);
select * from p_tab partition(p_4);
select * from p_tab partition(p_5);
select * from p_tab partition(p_6);
select * from p_tab partition(p_7);
select * from p_tab partition(p_8);
select * from p_tab partition(p_9);
select * from p_tab partition(p_10);
select * from p_tab partition(p_11);
select * from p_tab partition(p_12);
select * from p_tab partition(p_maxvalue);

 6、查看分区信息

col table_name for a30
col partition_name for a30
select table_name,partition_name from user_tab_partitions where table_name = 'P_TAB';

 7、删除普通表、分区表

drop table p_temp purge;
drop table p_tab purge;