Re: unique contraint without index

On Wed, 17 Jan 2007 16:30:33 +0100, "What's in a namespace" <whatsin_at_xs4all.nl> wrote:

>
>"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> schreef in bericht
>news:45adf219.2564359_at_news.hetnet.nl...
>> On Tue, 16 Jan 2007 15:09:04 -0800, DA Morgan <damorgan_at_psoug.org>
>> wrote:
>>
>>>Jaap W. van Dijk wrote:
>>>> On 16 Jan 2007 06:06:39 -0800, "sybrandb" <sybrandb_at_gmail.com> wrote:
>>>>
>>>>
>>>>> A primary key without any index just doesn't make sense.
>>>>> Nor does your nick.
>>>>>
>>>> Yes it does, and Oracle thinks so too (DISABLE NOVALIDATE).
>>>>
>>>> Jaap.
>>>
>>>That is a special case only valid when using a non-deferrable
>>>constraint and/or when the constraint is built using a unique
>>>index (rather than a non-unique) index.
>>>
>>>SQL> ALTER TABLE t
>>> 2 ADD CONSTRAINT pk_t
>>> 3 PRIMARY KEY (testcol)
>>> 4 DEFERRABLE INITIALLY DEFERRED
>>> 5 USING INDEX;
>>>
>>>SQL> ALTER TABLE t
>>> 2 MODIFY CONSTRAINT pk_t
>>> 3 DISABLE NOVALIDATE;
>>>
>>>Table altered.
>>>
>>>SQL> select constraint_name from user_constraints where table_name = 'T';
>>>
>>>CONSTRAINT_NAME
>>>------------------------------
>>>PK_T
>>>
>>>SQL> select index_name from user_indexes where table_name = 'T';
>>>
>>>INDEX_NAME
>>>------------------------------
>>>PK_T
>>>
>>>SQL>
>>>--
>>>Daniel A. Morgan
>>>University of Washington
>>>damorgan_at_x.washington.edu
>>>(replace x with u to respond)
>>>Puget Sound Oracle Users Group
>>>www.psoug.org
>>
>> You are right, but Sybrand made an unqualified remark:
>>
>> "A primary key without any index just doesn't make sense"
>>
>> and there are situations when this can make sense (from the SQL
>> Reference):
>>
>> "This feature [DISABLE VALIDATE, Jaap] is most useful in data
>> warehousing situations, because it lets you load large amounts of data
>> while also saving space by not having an index."
>>
>> To which I can add: and saving time by not having to write the index
>> segment. You can not change the data now of course, but this is
>> usually acceptable in a datawarehouse, where you only want to query
>> the data for reporting after you have loaded it.
>>
>> Jaap.
>
>In which case, to my opinion, the constraint itself is of no value at all.
>If you don't update, why set a constraint?
>
>Shakespeare
>
>

Apart from being used to preserve data integrity, the constraint can also be used by the optimizer to determine the access path. With the constraint in place the optimizer knows that a column or combination of columns has unique values, which can be significant when these columns are used in a WHERE-clause that joins this table to other tables. I guess that is why Oracle has added this feature. But maybe someone else knows other reasons.

Jaap. Received on Wed Jan 17 2007 - 15:25:45 CST