Re: How to bypass referential integrity

Van Messner wrote:

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

I don't know much about cold fusion but if people are sharing a single session, is it possible to have something along the lines of :

when populating the table you include a sequence id (how you access/identify/associate that with a particular "pseudo-session" in cold fusion is beyond my scope), and delete the data immediately after opening a cursor - sort of:

begin
  insert into temporary_tab select <some stuff> (inc unique sequence id)  open a_ref_cursor for select * from temporary_tab;  delete from temporary_table where sequence_id = my_sequence_id;  return a_ref_cursor to <cold fusion>
end;

It'll pound the rollbacks a little more...

hth
connor

--
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."