Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Optimize Select on update column

Re: Optimize Select on update column

From: Tony <andrewst_at_onetel.net.uk>
Date: 13 Feb 2004 02:19:49 -0800
Message-ID: <c0e3f26e.0402130219.667b9b18@posting.google.com>


Eye-Gee.Kua_at_shell.com (egkua) wrote in message news:<46a2c097.0402121715.390e1cf2_at_posting.google.com>...
> Tony,
>
> There will be thousands of records where status = 'N'.

"Thousands" out of a "gig" of records is a very LOW number - suggests an index to me.

> Indexing on update column will give bad performance.

You keep saying this, but I don't know quite what you mean by it. Do you mean that UPDATES will be a bit slower because they have to update the index? If so, then that is undeniably true but hardly a serious reason to NEVER index such a column! A sense of proportion is in order...

> If there are gig on the non_unique_key_code column, would you think
> that it is a good idea to have it indexed on the two columns?

It depends, of course. If there are 1 billion rows in the table (is that what you mean by "gig on the column"?), and 1 million different non_unique_key values (avg 1000 rows per non_unique_key value), then an index on non_unique_key is likely to be helpful. If further to that, out of each 1000 rows typically 950 have status = 'Y' and only 50 have status = 'N', then adding status to the index will make it even more useful - you will read 50 records instead of 1000. On the other hand, if there are only a small number of distinct non_unique_key_values, then the index may be of no help at all.

Experiment! Received on Fri Feb 13 2004 - 04:19:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US