From: "Van Messner" <vmessner@bestweb.net>
Newsgroups: comp.databases.oracle.server
References: <2_cT6.525$7d.60964@newshog.newsread.com> <991812245.16375.0.nnrp-13.9e984b29@news.demon.co.uk>
Subject: Re: How to bypass referential integrity
Lines: 88
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <_YyT6.46424$gA.2013008@monger.newsread.com>
Date: Wed, 06 Jun 2001 23:24:42 GMT
NNTP-Posting-Host: 63.208.84.2
X-Complaints-To: Abuse Role <abuse@bestweb.net>, We Care <abuse@newsread.com>
X-Trace: monger.newsread.com 991869882 63.208.84.2 (Wed, 06 Jun 2001 19:24:42 EDT)
NNTP-Posting-Date: Wed, 06 Jun 2001 19:24:42 EDT
Organization: BestWeb (bestweb.net)


Thanks you for the good advice.  I did post it to Metalink, without much
feedback so far, although I believe anyone could replicate the problem.
There is a similar known bug with reverse indexes, nut no mention of the one
I found, or I should say ran into.

Also, if I might presume to ask you a question.  Session-based global
temporary tables can do some very nice things in some of our apps.
Unfortunately, everyone who comes in through ColdFusion is hitting the
database as a single session, which makes the temp tables a lot less useful.
ColdFusion doesn't;t have any way around this.  Any ideas?

Van


"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> wrote in message
news:991812245.16375.0.nnrp-13.9e984b29@news.demon.co.uk...
>
> 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@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
> >
> >
>
>