Skip to Main Content
  • Questions
  • Rebuild global index NOLOGGING vs LOGGING : Generation of the same amount of REDO

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sébastien.

Asked: December 15, 2020 - 8:23 pm UTC

Last updated: December 17, 2020 - 4:22 am UTC

Version: 11g 12c 19c

Viewed 1000+ times

You Asked

Hello,

I'm busy with rebuild of global indexes on partitioned table and, to make the job faster, I'm using the clause PARALLEL + NOLOGGING with the rebuild.

My code is :

ALTER INDEX my_schema.my_global_index REBUILD PARALLEL 20 NOLOGGING;


The parallelism works.
With the clause NOLOGGING, I expected that my rebuild generated a minimum of redo, ie only redo related to dictionary changes.
But in practice, it seems that NOLOGGING has no effect on the generation of redo. That generates the same amount of redo with index NOLOGGING vs LOGGING.
The amount of redo is similar to the size of the index.

Statistic redo size of my session after rebuild NOLOGGING or LOGGING:

SELECT sn.name, s.VALUE / 1024 / 1024 redo_size_mb
FROM   v$statname sn, v$mystat s, v$session se
WHERE  se.sid = s.sid AND s.statistic# = sn.statistic# AND sn.name = 'redo size';


NAME REDO_SIZE_MB
------------------------
redo size 577

The index size :

SELECT bytes / 1024 / 1024 index_size_mb
FROM   dba_segments
WHERE  owner = 'MY_SCHEMA' AND segment_name = 'MY_GLOBAL_INDEX'


INDEX_SIZE_MB
-------------
572


Have you an idea of which factor have an influence on that generation of redo in "mode" NOLOGGING?
Is there a way to avoid that generation ?

thank you very much,
Sébastien Tromme.

and Chris said...

Do you have force logging enabled on the database?

select force_logging from v$database;

FORCE_LOGGING   
YES  


If so, this takes priority over the nologging clause in your statement and the database will still log it. A similar rule applies if the tablespace is force logged.

For full details of the priority order, see https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-the-redo-log.html#GUID-27E9B63B-03E4-43E5-BA59-AB0EDDDE5CE6

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Indeed force_logging = YES

Sébastien Tromme, December 16, 2020 - 9:02 pm UTC

Hello,
Thank you very much.
Indeed my database is with force_logging = YES.

select force_logging from v$database;

FORCE_LOGGING
YES

Can you just confirm that my understanding of the Oracle documentation is correct if I say that it can be altered online without a restart of the instance ? Using the following ddl?
alter database no force logging;


Idem for tablespace ?
alter tablespace my_tablespace no force logging;



Additional question : I suppose that an direct insert with hint append has no effect (regarding to redo generate) as soon as the database or tablespace (tablespace of the object inserted) are in mode "force logging"?

Regards,
Sébastien.
Connor McDonald
December 17, 2020 - 4:22 am UTC

Yes you can change at will

SQL> conn / as sysdba
Connected.
SQL> alter database force logging;

Database altered.

SQL> alter database no force logging;

Database altered.

SQL>


Just don't forget that force logging is probably there for a reason (eg standy) so you need to make sure you don't break any managed recovery rules you have in place.

Even *with* logging, APPEND for large data loads is more efficient than without

Thank you

Sébastien Tromme, December 17, 2020 - 12:33 pm UTC

Thank you Chris and Connor,
That seems very clear to me now.

Regards,
Sébastien.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.