Sunday 2 February 2014

Partition table create from non partition table CTAS

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);

No comments:

Post a Comment