Indexes - Delete operation

Posted on 2013-12-10
Medium Priority
Last Modified: 2014-01-14
A table has millions of records. We are not doing any inserts or updates. We are deleting millions of rows. How will this affect the index?
Question by:d27m11y
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Author Comment

ID: 39708934
Does delete operation depend on indexes on the table when the table has too many rows, please suggest
LVL 23

Assisted Solution

David earned 375 total points
ID: 39709034
Well, for each indexed entry, you will have I/O to retrieve each entry and flag it as deleted, followed by an index update -- in addition to the row deletion.

You might provide specifics, such as the actual statement and what's happened so far.  Also, what kind of indexes exist; what percentage of rows are being removed (i.e., what's left after the DML); is the table being used during this deletion? is the instance in archievelog mode or not, and RDBMS version.

Depending upon the specifics, one might disable the indexes (and triggers), then drop rows.  But for the more recent versions, the prevailing advice may be to rename the source table, then execute a CREATE TABLE AS SELECT (CTAS) to rebuild the target table with the remaining rows.  Obviously, rebuild the index(es) when done.

The DELETE operation performance will be most affected by the RDBMS Optimizer, based upon the (up to date??) table statistics, table organization, WHERE qualifiers, and which datatypes are involved.

Author Comment

ID: 39709078
well, this was question asked in a test and I was not sure how to answer briefly and upto the point.  Can you provide any example please ?
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 23

Expert Comment

ID: 39709218
Unless the test was specifically on Oracle databases, you should ping a zone moderator and request additional zones be added.  The other caveat is that we cannot be held responsible for wrong answers.  A delete in Oracle's SQL may have a different impact from that of MySQL, for example.

"How does it affect the index" is ambiguous, and perhaps I interpret you too literally.  Literally, the index is affected by having I/O to locate the key in a data block, and to update its header as deleted.  See my first sentence in my first reply.  Another interpretation might be, when does a DELETE statement switch from an indexed retrieval to a full table scan.  It depends :) but my opinion is in the ballpark of 5-10%.

By any chance do you have the exact question?

Author Comment

ID: 39709417
This was the exact question I was asked in an Oracle based test, was not sure how to answer.
LVL 15

Accepted Solution

Franck Pachot earned 375 total points
ID: 39709485

I suppose the person who asks expects that you say that the index will still have the same size and that you need to rebuild it or shrink it in order to reclaim space.

But the right answer in my opinion is: don't do that. The delete will be very long, generating lot of undo and redo. If it does not need to be transactional and and can be done offline, then better to create a new table with only the rows you  want to keep.


Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question