Re: unique contraint without index

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. Received on Wed Jan 17 2007 - 04:01:40 CST