Solved

Indexes - Delete operation

Posted on 2013-12-10
6
252 Views
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?
0
Comment
Question by:d27m11y
  • 3
  • 2
6 Comments
 

Author Comment

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

Assisted Solution

by:David
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.
0
 

Author Comment

by:d27m11y
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 ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:David
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?
0
 

Author Comment

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

Accepted Solution

by:
Franck Pachot earned 125 total points
ID: 39709485
Hi,

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.

Regards,
Franck.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now