Primary Key vs Query

From: Norman Mann <nm_at_prismtechnologies.com>
Date: Thu, 20 Jan 2000 09:18:57 -0000
Message-ID: <3886d0aa.0@nnrp1.news.uk.psi.net>

Maybe a stupid question but I thought I would ask anyway.

How come a query to find a possible primary key violation takes so much longer than
the primary key constraint takes to inform you that it is violated?

Example.

I have a table t1.
it has n columns two of which are a and b. A query joining t1 with itself takes a very long time as its a very big table.
There predicate is basically

Select tab.a, tab.b
From from t1 tab, t1 tab1
where tab.a = tab1a
and tab.b = tab1.b
and tab.c != tab.c /*To elimante the same rowid */

As mentioned this takes a very long time.

But "ALTER TABLE t1 CONSTRAINT t1_pk PRIMARY KEY (a, b)" is nearly instantaneous.

Is there any way to get oracle to return any rowids violating this?

Any help appreciated

Norman Received on Thu Jan 20 2000 - 03:18:57 CST