Create table using non Partition Table to convert into Partition table:
create table log_report_summary
PARTITION BY RANGE (added_on)
SUBPARTITION BY HASH (client_ID)
SUBPARTITIONS 15
(
PARTITION MAY2007
VALUES LESS THAN (TO_DATE('01-JUN-2007' ,'DD-MON-YYYY')),
PARTITION JUN2007
VALUES LESS THAN (TO_DATE('01-JUL-2007','DD-MON-YYYY')),
PARTITION JUL2007
VALUES LESS THAN (TO_DATE('01-AUG-2007','DD-MON-YYYY')),
PARTITION AUG2007
VALUES LESS THAN (TO_DATE('01-SEP-2007','DD-MON-YYYY')),
PARTITION SEP2007
VALUES LESS THAN (TO_DATE('01-OCT-2007','DD-MON-YYYY')),
PARTITION OCT2007
VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY')),
PARTITION NOV2007
VALUES LESS THAN (TO_DATE('01-DEC-2008','DD-MON-YYYY')),
PARTITION DEC2007
VALUES LESS THAN (MAXVALUE)
)
AS
select rownum ID, t.* from
(select c1,c2,Remote_add,c3,
client_id,type,level_id,pg_no,trunc(added_on)added_on ,count(*) count
from LOG_REPORT_2007
group by c1,c2,Remote_add,c3,client_id,type,level_id,pg_no,trunc(added_on) )t;
Adding Partition
ALTER TABLE <table_name>
ADD PARTITION <new_partition_name> VALUES LESS THAN (MAXVALUE)
LOB (<column_name>) STORE AS (TABLESPACE <tablespace_name);
ALTER TABLE DILIP_LOG
ADD PARTITION JAN_APR2007 VALUES LESS THAN (MAXVALUE)
STORE AS (TABLESPACE DIL1);
PARTITION BY RANGE (added_on)
SUBPARTITION BY HASH (client_ID)
SUBPARTITIONS 15
(
PARTITION MAY2007
VALUES LESS THAN (TO_DATE('01-JUN-2007' ,'DD-MON-YYYY')),
PARTITION JUN2007
VALUES LESS THAN (TO_DATE('01-JUL-2007','DD-MON-YYYY')),
PARTITION JUL2007
VALUES LESS THAN (TO_DATE('01-AUG-2007','DD-MON-YYYY')),
PARTITION AUG2007
VALUES LESS THAN (TO_DATE('01-SEP-2007','DD-MON-YYYY')),
PARTITION SEP2007
VALUES LESS THAN (TO_DATE('01-OCT-2007','DD-MON-YYYY')),
PARTITION OCT2007
VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY')),
PARTITION NOV2007
VALUES LESS THAN (TO_DATE('01-DEC-2008','DD-MON-YYYY')),
PARTITION DEC2007
VALUES LESS THAN (MAXVALUE)
)
AS
select rownum ID, t.* from
(select c1,c2,Remote_add,c3,
client_id,type,level_id,pg_no,trunc(added_on)added_on ,count(*) count
from LOG_REPORT_2007
group by c1,c2,Remote_add,c3,client_id,type,level_id,pg_no,trunc(added_on) )t;
Adding Partition
ALTER TABLE <table_name>
ADD PARTITION <new_partition_name> VALUES LESS THAN (MAXVALUE)
LOB (<column_name>) STORE AS (TABLESPACE <tablespace_name);
ALTER TABLE DILIP_LOG
ADD PARTITION JAN_APR2007 VALUES LESS THAN (MAXVALUE)
STORE AS (TABLESPACE DIL1);
No comments:
Post a Comment