| What is the effect of index for DELETE operation [message #632456] |
Sat, 31 January 2015 22:47  |
 |
rak.plsql
Messages: 3
Registered: January 2015
|
Junior Member |
|
|
Hi Experts,
DELETE FROM customer_history
WHERE cust_type='SNB';
The "customer_history" table is having 255045688 records.
The table is having some indexes.
CREATE TABLE Customer_hstory (
Cust_ID NUMBER(3) NOT NULL,
Cust_Name VARCHAR2(30) NOT NULL,
cust_reg VARCHAR2(20) ,
City VARCHAR2(20) ,
cust_type CHAR(4) ,
Zip VARCHAR2(10),
reg_code VARCHAR2(12),
PRIMARY KEY (Cust_ID)
);
CREATE INDEX idx_cust_name ON Customer_hstory(cust_name);
CREATE INDEX idx_cust_type ON Customer_hstory(cust_type);
CREATE INDEX idx_reg_code ON Customer_hstory(reg_code);
As per my knowledge when we are performing DML operations on a table the index blocks has to update
so the DML operations slow down.
So before starting DELETE I have to drop all indexes on the table
and recreate once DELETE is completed?
Else I have to drop some spicified indexes?
Else no need to drop indexes?
What is the best way to DELETE if the table is having indexes.
Please help me.
Thanks.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: What is the effect of index for DELETE operation [message #632480 is a reply to message #632476] |
Sun, 01 February 2015 10:32  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
In short, for a performances point of view, the answer is "it depends", it may be better, it may be worse, it may be even.
Now think global, think about locks.
[Updated on: Sun, 01 February 2015 10:33] Report message to a moderator |
|
|
|
| Re: What is the effect of index for DELETE operation [message #632624 is a reply to message #632480] |
Tue, 03 February 2015 13:22 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
You have to stop and think a bit about what is happening when you do the delete in order to answer your own question.
At a high level:
1. find the row to delete
2. lock the row
3. change each index (you have three)
4. change the table block
5. rinse and repeat
So...
If you have 10,000 rows and you want to delete 1 row, should you drop all indexes and after, recreate them? No way!
If you have 10 rows and you want to delete all 10 should you drop all indexes and after, recreate them? No way!
If you have 10,000 rows and you want to delete all 10,000 should you drop all indexes and after, recreate them? Yes in almost all such cases!
If you have 10,000 rows and you want to delete 1000 should you drop all indexes and after, recreate them? Maybe. Probably, but testing is indicated.
So as you see, one of the main issues with "to drop or not to drop" depends upon what percentage of rows you are deleting, and how many rows you are deleting.
How many rows are in the table in total?
How many rows are you deleting?
Can you figure out how to create your own tests to see where the cutoff point is for "to drop or not to drop", for your specific tables and indexes and delete statement?
We should not also that this is just covering the performance question of the delete and a tables indexes. It does not speak at all to other considerations like is this table part of some active system and so how will these deletes affect other processes if you choose to drop/recreate indexes, and how will these drop/recreate steps affect the working of constraints between this table and others, and other such questions.
Kevin
[Updated on: Tue, 03 February 2015 13:25] Report message to a moderator |
|
|
|
| Re: What is the effect of index for DELETE operation [message #632631 is a reply to message #632624] |
Tue, 03 February 2015 14:13 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Kevin,
Somehow everyone forgot Oracle databases are usually not for personal use and dropping index could affect every session. So "to drop or not to drop" firstly depends on what will happen to other sessions and only then upon what percentage of rows you are deleting, and how many rows you are deleting.
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
