Re: Temporary table use and the redo log
Date: Wed, 9 Sep 2009 06:53:00 -0700 (PDT)
Message-ID: <ef0bb50c-6bef-4772-b89f-cb82b71eff79_at_g23g2000yqh.googlegroups.com>
On 9 Sep, 13:28, "codefragm..._at_googlemail.com" <codefragm..._at_googlemail.com> wrote:
> > - This also means that you can't access an object modified by direct-
> > path inserts within the same transaction which needs to be considered
> > for your process logic. You need to commit to access the inserted the
> > rows which means a "on commit delete rows" temporary table doesn't
> > make sense with a direct-path insert operation, since after the commit
> > the data will be gone.
>
> Thanks for the reply.
> I gather you can have Autonomous Transactions within a procedure, so I
> could:
> - start a transaction,
> - load the temporary table up,
> - do my selects
> - commit the transaction,
>
> then carry on with the rest of the procedure?
>
> Any views on that?
b.t.w. this seems to work for me
- Proc1 calls Proc2
- Proc2 does the autonomous transaction with the +append hint.
It seems fast and theres no redo archive logs getting created :-)
So what I'm worried about now are side effects.
(1) Proc2 is purely inserting, Proc1 is purely a select, I can't see
theres any chance of locking here
(2) Global Temporary tables, so can't lock with other sessions
(3) A session gets created, a few calls are made (but only one call to
Proc1) and is then discarded (rightly or wrongly)
(4) Any issues with writing over the high water mark and repeatedly
losing that storage to the temporary table
I guess one issue is that I'm a complete novice and haven't seen this technique mentioned anywhere
If this works we get time to look at this issue properly and remove the tables which would be nice Received on Wed Sep 09 2009 - 08:53:00 CDT