Home » RDBMS Server » Performance Tuning » How about performance using index on the column which is using in WHERE and SET clause. (Oracle 11g)
How about performance using index on the column which is using in WHERE and SET clause. [message #616553] Wed, 18 June 2014 04:38 Go to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi Experts,

UPDATE big_table
SET cmm_id=10,
    rms_id=50,
    flag_id=flag_id+5
WHERE flag_id < 10;


The big_table is having 5000000 records.
This predicate filter WHERE flag_id < 10 filters 8000 records and updating same 8000 records.
If I create index on FLAG_ID performance of the update statement will be improved as I am updating the FLAG_ID column also.

As per INDEX concept INDEX affected performance not good for DML operations such as INSERT,UPDATE,DELETE.

Please confirm if I create index on FLAG_ID column overall performance of UPDATE
will be good or bad compared to without index on FLAG_ID column?

Please help me.


Thanks.
Re: How about performance using index on the column which is using in WHERE and SET clause. [message #616557 is a reply to message #616553] Wed, 18 June 2014 05:06 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The only way to confirm that is to test it.
Re: How about performance using index on the column which is using in WHERE and SET clause. [message #616568 is a reply to message #616553] Wed, 18 June 2014 06:51 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ramya_162 wrote on Wed, 18 June 2014 15:08
As per INDEX concept INDEX affected performance not good for DML operations such as INSERT,UPDATE,DELETE.


You are partially correct. That depends whether the index update happens prior or later the update.

A quote by Jonathan Lewis from "Tuning Updates" :
Quote:

In general it seems that an indexed mechanism is likely to be more efficient than the tablescan even when a tablescan would be more efficient for the equivalent query.


There are two types of access methods for an update,
1. Using index to find the data to be updated.
2. Using tablescan to find the data to be updated.

In the former, index update is delayed/postponed till end of update. And then, it does a bulk update on indexes after sorting them with row_id. In the latter, Oracle updates each index entry, thus removing any sorting cost, however, adding up the undo and redo overhead.

You can read more here Tuning Updates

P.S : The only way IMO is to test it and be safe.
Re: How about performance using index on the column which is using in WHERE and SET clause. [message #618464 is a reply to message #616568] Sat, 12 July 2014 02:03 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
you will most likely benefit from the index. Although as was pointed out, you should be testing it if the specific scenario is important to you. Here are some thoughts.

1. you are fetching much less than 2% of the rows in the table (8000/50000000)=0.2%. that means an index lookup will likely get you the rows faster than a table scan. so you will benefit from the lookup speed.

2. Oracle has an optimization for updating index entries such that if the new value = the old value, the index entry is not modified. However, your query is such that the new value will always be different from the old value (excepting for null). So you will be updating index blocks for every row.

Good luck with your test. I expect the index variant to be faster.

Kevin
Previous Topic: AWR and ASH report analyze
Next Topic: Query Performance
Goto Forum:
  


Current Time: Thu Mar 28 16:52:29 CDT 2024