Re: How to bypass referential integrity

The problem is that once the queries hit Oracle they all look like the same session for the same user. I'm going to try what you suggest to see if there is any way to get it to work. Thanks for the idea.

Van

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3B1FC041.1E846D75_at_yahoo.com...
> 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..."
>
>
Received on Thu Jun 07 2001 - 16:13:14 CDT