Does NOLOGGING work ?

[nealh]

According the documentation, create table ... nologging... should be the same as using the /*+ append */ hint on an insert clause.

However, if I use /*+ append */ to insert 5 million rows to a table, it takes just 13 seconds. Creating the table with nologging and then inserting without any hints takes 1 minute 30 seconds. This is the same as the time taken to insert normally into the table.

Unfortunately, I don't always have control over the format of insert statements, so it isn't possible for me to amend them all to put in a hint. Is there something else I need to get nologging working ?

[pipo]

1) do you specify CACHE when creating your table ???
if so, NOLOGGING cannot be used

2) you can append in LOGGING or in NOLOGGING mode, there is no link between the 2 concepts, the action of appending is just done in LOGGING or NOLOGGING mode, depending on the default behaviour of your tablespace :
select tablespace_name, logging
from dba_tablespaces;

[chrisrlong]

Actually, there is a link between the 2. NOLOGGING mode will do *nothing* for your basic inserts *without* the +Append hint.

Here is a good thread on the topic:

http://www.dbasupport.com/forums/sho...9&pagenumber=2

HTH,

- Chris

[nealh]

On that basis, the best performance should be with an append hint and nologging. However, I don't get any discernable difference between append used with logging and append used without logging. So the possible explanations are:

a) [no]logging doesn't do anything at all
b) insert /*+ append */ always uses nologging
c) the performance benefits of nologging are negligible