Indexes - Delete operation

Posted on 2013-12-10
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 125 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 ?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 125 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

729 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