CREATE TABLE FACT_TEST
(
  DATE_KEY                  NUMBER,
  col1              NUMBER,
  col2                  NUMBER,
  col3      NUMBER,
  col4  NUMBER
)
PARTITION BY RANGE (DATE_KEY)
SUBPARTITION BY HASH (col1,col2)
PARTITION UNASSIGNED VALUES LESS THAN (MAXVALUE)
    NOLOGGING
    COMPRESS
    SUBPARTITIONS 16
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
/


alter table FACT_TEST add constraint pk_test primary key (date_key,col1,col2);


begin
        dbms_stats.gather_table_stats(
                user,
                'FACT_TEST',
                cascade => true,
                estimate_percent => null,
                method_opt => 'for all columns size 250'
        );
end;
/

declare 
l_part number;
l_date varchar2(20);
l_sql_stmt varchar2(200);
l_part_limit number;
l_insert_stmt varchar2(2000);
l_start_time pls_integer:=NULL;
l_elapsed_time pls_integer:=NULL;
begin 

for i in 1 .. 10 
loop 
   l_date := '20100404';
   l_part := to_number(to_char(to_date(l_date, 'yyyymmdd') + i, 'yyyymmdd'));
   l_part_limit := to_number(to_char(to_date(l_date, 'yyyymmdd') + i+1, 'yyyymmdd'));
   
   dbms_output.put_line('output==='||l_part);
   l_sql_stmt := 'ALTER TABLE fact_test   SPLIT PARTITION UNASSIGNED at ('||l_part ||' )   INTO (PARTITION D_'||l_part_limit||' , PARTITION UNASSIGNED) ';
   --dbms_output.put_line('This is stmt =='||l_sql_stmt);
   execute immediate l_sql_stmt ;
  
   l_start_time := dbms_utility.get_time;
   
   l_insert_stmt := 'Insert /*+append */ into FACT_TEST (<COLUMN_LIST>)
                     select '||l_part ||' as  DATE_KEY, <COLUMN_LIST>
                     from FACT_TEST1 
                     where date_key = 20091216';
   --dbms_output.put_line('insert stmt =='||l_insert_stmt);
   execute immediate l_insert_stmt;
   
   commit;     
   
   l_elapsed_time := dbms_utility.get_time - l_start_time; 
   dbms_output.put_line('inserted in partiton '||l_part ||' time taken =='||l_elapsed_time/100 ||'secs');


end loop;

end;
   
/