--按表中数据量分区 less than 小于 最大值如果3，这个分区只能存入两条数据--
--30000000--
--60000000--
create table dtf_test
(
dtf_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
dtf_date date not null
)
partition by range (dtf_id)
(
partition part_01 values less than(3) tablespace dinya_space01,
partition part_02 values less than(6) tablespace dinya_space02,
partition part_03 values less than(maxvalue) tablespace dinya_space03
);
INSERT INTO dtf_test values(1,12,'BOOKS',sysdate);
INSERT INTO dtf_test values(2,12, 'BOOKS',sysdate+30);
INSERT INTO dtf_test values(3,12, 'BOOKS',sysdate+10);
INSERT INTO dtf_test values(4,12, 'BOOKS',sysdate+20);
SELECT * FROM dtf_test;
select * from dtf_test partition(part_01);
select * from dtf_test partition(part_02);
SELECT * FROM dtf_test partition(part_03);
--日期分区 less than 小于--
create table dtf_date_test
(
dtf_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
dtf_date date not null
)
partition by range (dtf_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
);
insert into dtf_date_test values(3,12, 'BOOKS',to_date('2006-05-30','yyyy-mm-dd'));
insert into dtf_date_test values(4,12, 'BOOKS',to_date('2007-06-23','yyyy-mm-dd'));
insert into dtf_date_test values(5,12, 'BOOKS',to_date('2011-02-26','yyyy-mm-dd'));
insert into dtf_date_test values(6,12, 'BOOKS',to_date('2011-04-30','yyyy-mm-dd'));
insert into dtf_date_test values(7,12, 'BOOKS',to_date('2005-05-30','yyyy-mm-dd'));
SELECT * FROM dtf_date_test;
SELECT * FROM dtf_date_test partition(part_01);
SELECT * FROM dtf_date_test partition(part_02);
SELECT * FROM dtf_date_test partition(part_03);
--按星期分区--
create table date_test
(
date_id number primary key,
wd number(8),
item_description varchar2(300),
date_time date not null
)
partition by range (wd)
(
PARTITION Mon VALUES LESS THAN(2) TABLESPACE mon_space01,
PARTITION Tue VALUES LESS THAN(3) TABLESPACE tue_space02,
PARTITION Wed VALUES LESS THAN(4) TABLESPACE wed_space03,
PARTITION Thu VALUES LESS THAN(5) TABLESPACE thu_space04,
PARTITION Fri VALUES LESS THAN(6) TABLESPACE fri_space05,
PARTITION Sat VALUES LESS THAN(7) TABLESPACE sat_space06,
PARTITION Sun VALUES LESS THAN(8) TABLESPACE sun_space07
);
insert into date_test VALUES(01,1, 'dtf',to_date('2006-05-30','yyyy-mm-dd'));
insert into date_test values(02,2, 'dtf',to_date('2007-06-23','yyyy-mm-dd'));
insert into date_test values(03,3, 'dtf',to_date('2011-02-26','yyyy-mm-dd'));
insert into date_test values(04,4, 'dtf',to_date('2011-04-30','yyyy-mm-dd'));
insert into date_test values(05,5, 'dtf',to_date('2005-05-30','yyyy-mm-dd'));
insert into date_test values(06,6, 'dtf',to_date('2006-05-30','yyyy-mm-dd'));
insert into date_test values(07,7, 'dtf',to_date('2007-06-23','yyyy-mm-dd'));
SELECT * FROM date_test;
SELECT * FROM date_test partition(Mon);
SELECT * FROM date_test partition(Tue);
SELECT * FROM date_test partition(Wed);
SELECT * FROM date_test partition(Thu);
SELECT * FROM date_test partition(Fri);
SELECT * FROM date_test partition(Sat);
SELECT * FROM date_test partition(Sun);