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 ?
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 86
Loading flat file data in tables 2 98
Pivoting oracle table 9 90
Oracle SQL Developer - SubString 2 50
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

737 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