Re: How to bypass referential integrity

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_at_jlcomp.demon.co.uk> wrote in message news:991812245.16375.0.nnrp-13.9e984b29_at_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_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
> >
> >
>
>
Received on Wed Jun 06 2001 - 18:24:42 CDT