Re: re creating a primary key on a table makes requests faster?

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 17 Dec 2007 22:06:43 +0100
Message-ID: <5so6n4F19tq2sU1@mid.individual.net>

On 17.12.2007 21:24, allen.darrin_at_gmail.com wrote:
> On Dec 17, 7:25 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:

>> On 17 Dez., 11:26, "Wilfrid" <grill..._at_yahoo.com> wrote:
>>
>>> Would their be any reason why removing a primary key and recreating it on a
>>> reasonibly large table (~160000 records and increasing at steady rate, with
>>> int, nvarchar, bit and datetime columns), would have an impact on select and
>>> insert performance?
>>> Is there some kind of fragmentation on a table that could explain this?
>>> thanks in advance for your insight
>> Richard Foote wrote quite a bit about this:http://richardfoote.wordpress.com/2007/12/11/index-internals-rebuildi...http://www.miracleas.dk/images/upload/Docs/Richard%20Foote.pdf
>>

> I think when you drop and then add the primary key it causes the data
> in the table to reorginize which is similar to a performance tuning
> change.
>
> Meaning if you had fragmented data you organize it.

Nope, that was SQL Server. You might get a similar effect with IOT's in Oracle but I don't believe you can simply drop the index and recreate it because the index *is* the table. In any case the operation is far more costly than an index rebuild. Anyway, it's not necessary most of the time - please read Richard's excellent documentation.

Regards

robert Received on Mon Dec 17 2007 - 15:06:43 CST