Deleting huge rows from Oracle 11gR2.

Hello Experts,

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 :

WHERE C_DT > TO_DATE('2019-01-10 03:39', 'YYYY-MM-DD HH:MI') ;

Open in new window

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();
  l_start     NUMBER;

  -- 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));


Open in new window

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
LVL 17
Swadhin Ray Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alex [***Alex140181***]Software DeveloperCommented:
1. Does this have to be done in PL/SQL?! You can do it in pure SQL (which you should prefer in this case)!

2. If you have to use PL/SQL: no need to fill your array in row-by-row processing! Just do an "select * bulk collect into..." using the appropriate rowtype array (tab)

3. Are there any triggers active on that table?

4. Can you post the execution plan(s) please?
ste5anSenior DeveloperCommented:
A table with 546471 rows is pretty small. Thus your normal DELETE is the correct approach. Here you need to look into the details..

My guesses:
- Do you have a trigger ON DELETE?
- Do you have cascading foreign keys?
- Do you have a general load/performance problem?
- Do you have a bad I/O (path speed, volume speed, volume corruption)?
Kent OlsenDBACommented:
A couple of good suggestions there.

Just making sure, but both filter clauses look like they intend to delete rows with future dates (after 3:39 yesterday morning).  Is that correct?

15 minutes might be an inconvenience, but it suggests that there's a lot of going on in the delete.  To me, the biggest issue would be that data and index pages get locked during those 15 minutes and that impacts other processes.

You can mitigate that by deleting rows in smaller chunks.  Fewer locks would be held simultaneously, and the locks would be held for shorter durations.  You can test that with this query:

SELECT to_char(c_dt, 'YYYY-MM-DD'), count(*)
FROM my_table
WHERE c_dt  > TO_DATE('2019-01-10 03:39', 'YYYY-MM-DD HH:MI') 
GROUP BY to_char (c_dt, 'YYYY-MM-DD')

Open in new window

If the query returns a few rows with large counts, a bit of refinement will be needed.  Otherwise, you can build a series of deletes based on the dates returned.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Deletion of more then 300,000 rows is a massive work. So do not be surprised by the time. Also no indexes will help you. The optimizer will ignore them by this amount of affected rows.
To speed up the deletion investigate the size of the REDO logs and the REDO buffer in the SGA. If needed increase their size and the number of the REDO LOG groups.
Next point is to check if there are locked rows used by the applications. This is very common in the busy hours. So the best time for the deletion is the time your instance is not used intensively by other applications.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
As others have suggested, just using a SQL delete is usually the fastest option.  Adding PL\SQL usually makes tasks like this slower.

And an index will not help you here, not because of the number of rows but because the percentage of the rows you are deleting is over 50% of the rows in the table.  Usually if the percentage of the rows in the table that you need to select (or update or delete) is over about 15% of the total, an index will not be useful.

If the problem is record locks from other activity in the table, then a PL\SQL-based approach that deletes a small number of rows at a time, then commits those then deletes another small group, etc. may be useful.
Swadhin Ray Author Commented:
Thanks a lot experts. The buffer size was low this was doing a overall performance issue. The main challenge is in many cases there access to data dictionary views are not been provided and this really kills time to follow up with folks and get the right details. I sometimes feel like I am blind and watching netflix only sound effect:-) .
Alex [***Alex140181***]Software DeveloperCommented:
Deletion of more then 300,000 rows is a massive work.
Sorry, but this is absolute nonsense!! If this number was factor 100 ok, but this is just NOT BIG!
Obviously schwertner has not dealt with "big data", otherwise I cannot fully understand such misleading comments!
Swadhin Ray Author Commented:
Buffer size and open cursor were low and I agree with you that the amount of data is not huge.
@  Alex [***Alex140181***]

Deleting 300K rows can be done on different hardware and on different environments.
As you can see the Instance has small REDO buffer.
So the SGA can be big, can be slow.
There can be many indexes that  will slow down the deletion.
Also the recording of the deleted data in the REDO logs takes time.
So do not underestimate the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.