Re: Create table with LOGGING option

Even if your table was created nologging, or if it is subsequently altered to be nologging, then ALL normal DML (inserts, updates and deletes) will STILL generate the normal quantity of redo. The Nologging attribute is only respected by certain very specific forms of DML (such as a direct load insert -ie, insert into X select * from Y) or a direct SQL Load, and some forms of DDL (Create index on blah.... for example can indeed be set to nologging, and the index entry insertions will not be recorded in the redo logs).

You can *never* switch off logging for normal DML (not unless you start using some extremely iffy hidden parameters, anyway -and then that's at a system-wide level, not table by table).

Frankly, I've never really understood why anyone would bother nologging the creation of a table itself, since table *creation* is hardly likely to take a billion and one years to complete (a subsequent population of said table by a huge SQL Load, say, is an entirely different matter).

Regards
HJR

--
=============================!!=============================
The views expressed are my own only, and definitely NOT those of Oracle
Corporation
=============================!!=============================


"Tom" <cmc3232_at_yahoo.com> wrote in message
news:SREL6.30765$yw.796546_at_news20.bellglobal.com...


> Hi,

>

> I have some confusion on the LOGGING option when CREATE TABLE (or index).

> The book said LOGGING will generate redo for the table creation and this

> will recover the newly created table in case of system recovery needed.

>

> It seems to me that it is saying "a redo" will be created for the TABLE

> CREATE command.

>

> My question: does this option also means any DML activity upon the table

> will generate redo entry, or can I say that NOLOGGING will cause no redo

> entry generation for any DML for the table.

>

> Is this option just a DDL, or both DDL and DML issue?

>

> Thanks

> Tom

>

>