-- =======================================================================
-- 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;
/