Hi,
I can only speak for Oracle. I don't know about other databases. But since
most databases implement indexes based on B-trees or something similar, I
guess this will hold for most, if not all, of them.
If you update an indexed column, the index entry is deleted and a new one
inserted. This is how B-trees and derived algorithms work. That means that a
generic update statement that updates all columns will be slower if there
are indexed columns.
Brgds,
Mark
----- Original Message -----
From: "Dong Wang" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, September 18, 2001 7:14 PM
Subject: performance comparison of two update statements
> Hi,
>
> When designing some library tools using dbi/dbd to update a table in an
> Oracle db, we are facing the following scenario:
>
> Let's say there is a table created as:
>
> SQL> create table test1 (c1 varchar2(10) primary key, c2 varchar2(10), c3
> varchar2(10));
> SQL> create index test1_ix1 on test1(c2);
> SQL> insert into test1 values ('a', 'b', 'c');
> SQL> commit;
>
> Now compare the following two update statements:
>
> statement1: update test1 set c3 = 'd' where c1 = 'a';
> statement2: update test1 set c2 = 'b', c3 = 'd' where c1 = 'a';
>
> They both result in the same final result, but does statement2 involves
some
> extra work on the index created on c2 so statement1 is more efficient?
From
> the programmers point of view, it is easier to write some generic update
> statements without know exactly which column is being actually updated.
>
> Thanks.
>
>
> > Dong Wang
> > Grand Central Networks
> > [EMAIL PROTECTED]
> >
>
