ORACLE表分區

--按表中資料量分區 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);