Re: Insert without redo log

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:982225998.12720.0.nnrp-08.9e984b29_at_news.demon.co.uk...
>
> Inserts into temporary table generate rollback,
> and the rollback is covered by redo, so an insert
> into a temporary table does results in redo -
> albeit less than that required for a normal table.

Mmmm... hadn't thought about that. The manuals state (in their inimitable bullet point fashion!) "No redo generated for DML" for temporary tables.... I could have sworn they also said no rollback, but I must just be imagining it. I think I should have just stuck with the short answer: "No". Has a certain conciseness about it!!

>
> You can do direct mode inserts to a GTT,
> but the table has to be 'on commit preserve'
> because you need to issue a COMMIT before
> you can select the rows back (standard
> feature of direct inserts).
>

Yup. Agreed.

> Just, to emphasise you point about GTTs
> being private, you can't (usefully) SQL*Load into
> a GTT, as the data disappears the moment
> the SQL*Load session ends.
>

Bong! I *was* just thought experimenting! Absolutely right, and accordingly a load and a direct insert won't do the deed. I suppose I could have worked this out for myself if I'd just thought harder!!

Thanks Jonathan. I think you're saying that the short answer is the final one.

Regards
HJR
>
> --
> 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
>
>
>
> Howard J. Rogers wrote in message ...
> >
> >Also in 8i, there is now the concept of a global temporary table, and
 DMLs
> >on them do not generate redo under any circumstances. It is possible you
> >could therefore contemplate loading into a temporary table first,then
 using
> >an 'insert into blah select * from temptable' to get the data into the
 main
> >table -that's a direct insert, and would respect a nologging attribute.
> >
> >I have not, however, ever tried doing a direct insert using a temporary
> >table as the source, so I can't guarantee it would work (and in any case
 it
> >needs 8i). If you *do* have 8i, and want to try it out, remember that
> >temporary tables are private to a session, and retain data only after a
> >commit if you create them with the 'on commit preserve rows' construct.
> >They are also instantly cleared out when the session ends.
> >
>
>
>
Received on Thu Feb 15 2001 - 06:46:00 CST