I have a table that contains 546471 rows out of which I need to delete 386201 rows based on date column.
Now I tried two ways where it is taking huge time to remove the rows.
First the table has a primary key c_key1 and c_dt is a date column based on which I need to delete.
The delete statement is :
DELETE FROM MY_TABLE
WHERE C_DT > TO_DATE('2019-01-10 03:39', 'YYYY-MM-DD HH:MI') ;
The date column is not having any indexes and even after creating the index it takes more then 15 mins to execute the delete statement.
So adding the index on c_dt , tired using forall mapping the primary key i.e. c_key1 column.
Here is the sample code which is taking again huge time:
TYPE t_chk_tab IS TABLE OF my_table.C_KEY1%TYPE;
l_chk_tab t_chk_tab := t_chk_tab();
-- Populate collections.
FOR i IN (
select C_KEY1 from my_table
WHERE C_DT > TO_DATE('2019-01-10 03:39', 'YYYY-MM-DD HH:MI') ) LOOP
l_chk_tab(l_chk_tab.last) := i.C_KEY1;
l_start := DBMS_UTILITY.get_time;
-- Time bulk delete.
FORALL i IN l_chk_tab.first .. l_chk_tab.last
DELETE FROM my_table
WHERE C_KEY1 = l_chk_tab(i);
DBMS_OUTPUT.put_line('Bulk Deletes : ' ||
(DBMS_UTILITY.get_time - l_start));
Any suggestion on to make it faster, the only issue is I cannot move the data to another table and delete the rows and rename it. As the same table is been used in multiple application for the delete operation all the application will not be using till weekend but still cannot drop the original table