Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!g23g2000yqh.googlegroups.com!not-for-mail From: "codefragment@googlemail.com" Newsgroups: comp.databases.oracle.server Subject: Re: Temporary table use and the redo log Date: Wed, 9 Sep 2009 06:53:00 -0700 (PDT) Organization: http://groups.google.com Lines: 41 Message-ID: References: <4b3fdc8a-6ad1-4275-8d5a-200ecf45bdf0@a21g2000yqc.googlegroups.com> <8f7154be-0b5b-41ed-a773-0ad38cf06583@j39g2000yqh.googlegroups.com> <3e8a48c3-5386-42fc-9f5e-7296ae9e3923@m38g2000yqd.googlegroups.com> NNTP-Posting-Host: 81.137.172.146 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 X-Trace: posting.google.com 1252504380 16490 127.0.0.1 (9 Sep 2009 13:53:00 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 9 Sep 2009 13:53:00 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: g23g2000yqh.googlegroups.com; posting-host=81.137.172.146; posting-account=pxdg5QoAAABxfENvItfA06OUPHUwuomY User-Agent: G2/1.0 X-HTTP-Via: 1.1 cgate:8080 (squid/2.7.STABLE5) X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On 9 Sep, 13:28, "codefragm...@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