SQL & PL/SQL

Implementation of a Fast Delete Operation as against Bulk Delete and normal Delete based on Conditions
------------------------------------------------------------------------------------------------------
-- Useful for Datawarehouse Environenments and Data Marts Population as
-- Data Mart will be populated baed on a Business Date , then it also has to delete
-- for that Date and then Insert Data based on Dimensions .
--The following pocedure can be implemented and customised based on your needs
create or replace procedure sp_fast_delete
(p_table_name in varchar2) -- if required pass this parameter or you can also code tablenames but this is a generic code which has similar
-- business_date column across all Datamart tables and ie Date_key is the Primary key in Data_mart tables
is
v_date date;
v_dateyyyymmdd number;
v_count number;
v_counter number :=0;
v_sqlrow_count number;
sql_string varchar2(1000);
del_string Varchar2(1000);
begin
v_date := f_get_business_date(); --- Function which returns the Business Date based on some Control Table.
v_dateyyyymmdd :=to_number(to_char(v_date,'yyyymmdd'));
sql_string := ' Select count(*) from '||p_table_name||' where date_key ='||v_dateyyyymmdd;
execute immediate sql_string into v_count;
if v_count >0 then
while v_counter <=v_count
loop
del_string := 'Delete from '||p_table_name||' where date_key = '||v_dateyyyymmdd||' and rownum <=10000';
-- Controlling IO for only 10000 rows each --time
execute immediate del_string;
v_sqlrow_count := sql%rowcount;
v_counter :=v_counter + v_sqlrow_count;
if mod(v_counter,10000)=0 or v_counter=v_count then
commit;
exit when v_counter=v_count;
end if;
end loop;
else
Dbms_output.put_line( ' No Rows to Delete in '||p_table_name);
end if;
end;


I shall very soon post a generic code for Fast Inserts

Enjoy using this code.