Re: How to bypass referential integrity

That sounds like a bug - have you called it in to Oracle ? I've checked metalink, and there is a bug relating to 8.1.6 ( 1476622 pointing to invisible base bug 1311756) that indicates problems with referential integrity when using a compressed IOT as the child table.

BTW - does your INT table have 3 indexes ? Your post reads as if you have:

    (akey)
    (bkey)
    (akey,bkey)

If so, the (akey) index is redundant.

Also BTW - depending on size, space, and common direction of joins, you might consider

    (akey, bkey)
    (bkey, akey)
as the two indexes.
OR - if you turn the INT table into an IOT keyed

on (akey, bkey), you need only create a secondary
index on (bkey).  (Except for that bug mentioned
above, darn it).

The optimum option is highly application dependent, of course.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Van Messner wrote in message <2_cT6.525$7d.60964_at_newshog.newsread.com>...


>Here's the setup.

>Table A has an primary key AKEY

>Table B has a primary key BKEY

>Table INT is an intersection between A and B.  INT has a column called AKEY

>which is foreign keyed back to the same column in A.  INT has a column

>called BKEY which is foreign keyed back to the same column in B.  In table

>INT the primary key is the columns AKEY and BKEY.  There are regular b-tree

>indexes on AKEY and on BKEY in INT.  A standard setup.

>

>If you put data into A, B and INT, then deleting data from A or B when

 child


>rows exist in INT causes an Oracle error.

>

>BUT now compress the index on AKEY in INT and compress the index on BKEY in

>INT.  You can then delete rows in either A or B or both where child rows

>exist in INT, leaving orphan rows behind in INT and Oracle gives no error

>message.

>

>Van

>

>