Re: Update Column in Where Clause (Index it?)

A copy of this was sent to GHouck <hksys_at_teleport.com> (if that email address didn't require changing) On Fri, 03 Dec 1999 16:15:24 -0800, you wrote:

>I tried looking for information about this situation, and am
>sure it has been covered; however, I was wondering if someone
>had some wisdom or guidelines or rules about:
>
> whether a column that is to be UPDATED, and is in the
> WHERE clause, should be indexed?
>
>That is, does the existence of the index slow up the UPDATE
>more than the lack of an index would slow the SELECT? In
>particular, what if the number of distinct values in the
>column are few? Or, is it so situation-dependent and
>data-dependent that no hard and fast rules exist?
>
>There may also be additional column(s) in the WHERE clause.
>

it depends.

if T is a 1 million record table. C is a column in that table and is unique. C is the column to be updated. Then "update T set c = newvalue where c = somevalue" would best be done if c was indexed.

if T is a 1 million record table. C is a column of that table and is not unique and has only a couple of discrete values (eg: there are 10's of thousands of records with the same value for C), C should not be indexed.

and so on. YMMV. It depends. Need to know more about the other columns in the predicate (are they very selective or not? are they indexed or not?). How many rows do you plan to actually update? (few rows -- and remember FEW is a relative term and you could index without any impact. lots of rows -- and remember LOTS is relative as well and you should think twice about indexing)...

>For example:
>
> update mytable set mycolumn=<newval> where mycolumn=<oldval>
>
>Thanks,
>
>Geoff Houck
>systems hk
>hksys_at_teleport.com
>http://www.teleport.com/~hksys

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Dec 04 1999 - 07:54:35 CST