Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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 ?
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.

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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

618 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