Re: Primary Key vs Query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 20 Jan 2000 11:50:54 -0500
Message-ID: <jree8soomus2653f9bs5vq6insqa934jcs@4ax.com>

A copy of this was sent to "Norman Mann" <nm_at_prismtechnologies.com> (if that email address didn't require changing) On Thu, 20 Jan 2000 09:18:57 -0000, you wrote:

>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.
>

because the index on (a,b) does not exist hence this self join will cause a FULL scan of T1 for each row in T1. If t1 has 100 rows -- t1 will be full scanned 101 times.

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

it is creating a unique index and bombs as soon as it finds a bad one. it hits the dup 'fast' and bails out. If it was the last row in the table (last being a relative term here) it would take longer.

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

see the EXCEPTIONS into clause of the constraint clause.

for example:

ops$tkyte_at_8i> create table exceptions(row_id rowid,

  2                          owner varchar2(30),
  3                          table_name varchar2(30),
  4                          constraint varchar2(30));

Table created.

ops$tkyte_at_8i> create table t ( x int ); Table created.

ops$tkyte_at_8i> insert into t values ( 1 ); 1 row created.

ops$tkyte_at_8i> /
1 row created.

ops$tkyte_at_8i> alter table t add constraint t_pk primary key(x) exceptions into exceptions;
alter table t add constraint t_pk primary key(x) exceptions into exceptions *
ERROR at line 1:
ORA-02437: cannot validate (OPS$TKYTE.T_PK) - primary key violated

ops$tkyte_at_8i> select * from exceptions;

ROW_ID             OWNER                          TABLE_NAME
------------------ ------------------------------ ------------------------------

CONSTRAINT

---

AAARsAAADAAACRKAAA OPS$TKYTE                      T

T_PK

AAARsAAADAAACRKAAB OPS$TKYTE                      T

T_PK

>Any help appreciated
>
>Norman
>
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jan 20 2000 - 10:50:54 CST