-- =======================================================================
--	This script substantiates how partition drop can be used instead of massive deletes
-- =======================================================================
set serveroutput on size 1000000
prompt Test case : partition drop vs delete : Compare redo size
set feedback off
drop table ilp_activity_np;
create table ilp_activity_np
(
activity_date  date not null,
item  number not null, 
period  number not null, 
location number not null, 
item_type number not null,
sales_units number (*,8), 
sales_price number(*,8), 
ap_units number (*,8), 
ap_price number(*,8), 
fc_units number(*,8),
fc_price number (*,8)
) 
nologging
storage (initial 10M next 10M pctincrease 0)
;
drop table ilp_activity_part;
create table ilp_activity_part
(
activity_date  date not null,
item  number not null, 
period  number not null, 
location number not null, 
item_type number not null,
sales_units number (*,8), 
sales_price number(*,8), 
ap_units number (*,8), 
ap_price number(*,8), 
fc_units number(*,8),
fc_price number (*,8)
) 
nologging
storage (initial 10M next 10M pctincrease 0)
partition by range (activity_date)
(
partition  part_jan  values less than ( to_date ( '02-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_feb  values less than ( to_date ( '03-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_mar  values less than ( to_date ( '04-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_apr  values less than ( to_date ( '05-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_may  values less than ( to_date ( '06-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_jun  values less than ( to_date ( '07-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_jul  values less than ( to_date ( '08-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_aug  values less than ( to_date ( '09-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_sep  values less than ( to_date ( '10-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_oct  values less than ( to_date ( '11-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_nov  values less than ( to_date ( '12-01-2005 00:00:00','MM-DD-YYYY HH24:MI:SS')),
partition  part_max  values less than (maxvalue)
)
;
set feedback on
declare
  i number;
  l_begin_redo_size number;
  l_end_redo_size number;
  l_generated_redo number;

  l_begin_time number;
  l_end_time number;
  l_elapsed_time number;

begin

for i in 1 .. 12  loop -- 10 items
		insert into ilp_activity_np
 		select  
		add_months('01-DEC-2004',i)  activity_date,
		i*1000000 item,  
	 	mod(rownum, 52) period, -- 52 weeks 
	 	rownum location,  -- 8000 locations
	 	mod(rownum, 5) item_type, -- 5 types
	 	trunc(dbms_random.value (1,100),8) sales_units,
	 	trunc(dbms_random.value (1,100),8) sales_price,
	 	trunc(dbms_random.value (1,100),8) ap_units,
	 	trunc(dbms_random.value (1,100),8) ap_price,
	 	trunc(dbms_random.value (1,100),8) fc_units,
	 	trunc(dbms_random.value (1,100),8) fc_price
		from  dba_objects where rownum <8001
		;
		insert into ilp_activity_part
 		select  
		add_months('01-DEC-2004',i)  activity_date,
		i*1000000 item,  
	 	mod(rownum, 52) period, -- 52 weeks 
	 	rownum location,  -- 8000 locations
	 	mod(rownum, 5) item_type, -- 5 types
	 	trunc(dbms_random.value (1,100),8) sales_units,
	 	trunc(dbms_random.value (1,100),8) sales_price,
	 	trunc(dbms_random.value (1,100),8) ap_units,
	 	trunc(dbms_random.value (1,100),8) ap_price,
	 	trunc(dbms_random.value (1,100),8) fc_units,
	 	trunc(dbms_random.value (1,100),8) fc_price
		from  dba_objects where rownum <8001
		;
	commit;
end loop;
select  get_my_statistics('redo size') into  l_begin_redo_size from dual;
l_begin_time := dbms_utility.get_time;

    delete from ilp_activity_np 
	where activity_date < to_date ('01-FEB-2005','DD-MON-YYYY')
	;
    commit;	
select  get_my_statistics('redo size') into  l_end_redo_size from dual;
l_end_time := dbms_utility.get_time;

dbms_output.put_line ('Total redo generated for delete ==>'||to_char( l_end_redo_size - l_begin_redo_size));
dbms_output.put_line ('Elapsed time in seconds ==>'||to_char(trunc(( l_end_time-l_begin_time)/100, 2 )));

select  get_my_statistics('redo size') into  l_begin_redo_size from dual;
l_begin_time := dbms_utility.get_time;

   EXECUTE IMMEDIATE 'alter table ilp_activity_part drop partition part_jan';
    commit;	
select  get_my_statistics('redo size') into  l_end_redo_size from dual;
l_end_time := dbms_utility.get_time;

dbms_output.put_line ('Total redo generated for partition drop ==>'||to_char( l_end_redo_size - l_begin_redo_size));
dbms_output.put_line ('Elapsed time in seconds ==>'||to_char(trunc(( l_end_time-l_begin_time)/100, 2 )));
end;
/

hostgator