Skip to Main Content
  • Questions
  • Modify NOLOGGING property of lob index and segment for a extended data type VARCHAR2 column

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Natividad.

Asked: February 16, 2021 - 4:07 pm UTC

Last updated: February 25, 2021 - 5:36 am UTC

Version: 19.7.0.0.0

Viewed 100+ times

You Asked

Hi Everyone.

I am trying to setup all tables and related indexes for the temporary (staging) tables in a DWH-Database 19.7 to NOLOGGING in order to minimize REDO operations. This database has the initialization parameter MAX_STRING_SIZE = EXTENDED.

I could achieve that for already created tables and indexes, inclusive for the lob segments and indexes using the following ALTER statements:

alter table table_name nologging; 

alter index index_name nologging; 

alter table table_name modify lob (lob_column) (nocache nologging); 


However I realised that these commands do not have any effect on lob segments and indexes created internally in the database as a result of an extended datatype (for example, a column VARCHAR2(4000 CHAR) which is stored as a LOB). They keep being LOGGING, because that is the default value for the tablespace they are created on.

Is there any way to modify the LOGGING property for those LOBs? I tried of course the syntax to modify a lob column, but the VARCHAR2 column is not recognized as a LOB datatype and it fails.

Thank you very much in advance for your help.
Best regards,
Natividad.

and we said...

Sorry you can't. This is not an extended varchar2 issue directly.

*Any* inline clob is always logged, eg

SQL> create table t ( x clob ) nologging;

Table created.

SQL> insert into t values ('x');

1 row created.

SQL> commit;

Commit complete.

SQL> select logging
  2  from user_lobs
  3  where table_name = 'T';

LOGGING
-------
YES


If you want a clob to be nologging, it must be stored out of line

SQL> drop table t purge;

Table dropped.

SQL> create table t ( x clob ) nologging lob ( x ) store as securefile (
  2   tablespace users disable storage in row chunk 8192
  3    nocache nologging );

Table created.

SQL> insert into t values ('x');

1 row created.

SQL> commit;

Commit complete.

SQL> select logging
  2  from user_lobs
  3  where table_name = 'T';

LOGGING
-------
NO

SQL>


which is why extended varchar2 will always be logged

Rating

  (2 ratings)

Comments

I've observed something different

Natividad Moreno, February 17, 2021 - 9:38 am UTC

Hi Connor.

Thanks for your answer. Actually I've read exactly that before posting this question in an old white paper from 2004 about LOBs: "NOLOGGING: changes to LOB data (stored in LOBSEGMENTs) are not logged into the redo logs, however in-line LOB changes are still logged as normal.". But in the practice, when I create an inline-LOB I could observe, that the LOGGING-property is set to 'NO' as well:

create table t ( x clob ) nologging lob ( x ) store as securefile (
tablespace users enable storage in row chunk 8192
nocache nologging );
-- Table created

select l.in_row, l.segment_created, l.logging segment_logging, i.logging index_logging
from  user_lobs l,
      user_indexes i
where l.index_name = i.index_name
and i.table_name = 'T';

-- Output:
IN_ROW   SEGMENT_CREATED   SEGMENT_LOGGING   INDEX_LOGGING
YES           NO                                   NO                                     YES


It is very curious, that the segment is created with NOLOGGING but the index is LOGGING. If now I performed an alter of this column to NOCACHE NOLOGGING, then the index is set up to NOLOGGING as well, even though the LOB is defined inline:

alter table t modify lob(x) (nocache nologging);

-- Output:
IN_ROW   SEGMENT_CREATED   SEGMENT_LOGGING   INDEX_LOGGING
YES           NO                                   NO                                     NO


If I now insert a value in the clob column, it is really inserted in the segment table and not in the lob segment created:

insert into t values ('x');
commit;

select t.rowid
from t;
-- AC/iiiAAAAAOwH0AAA

select object_name from dba_objects
where data_object_id = dbms_rowid.rowid_object('AC/iiiAAAAAOwH0AAA');
-- T


My question is, whether the documentation and expected behavior of the NOLOGGING property with inline-lobs is wrong, or am I missing any point in this reasoning?
Connor McDonald
February 23, 2021 - 6:22 am UTC

Indeed it looks like we have made some improvements in that area. Thanks for bringing it to my attention!


SQL> set verify off
SQL>
SQL> define tab_logging = logging
SQL> define clob_logging =  logging
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
  2  store as securefilefile (
  3  tablespace users enable storage in row chunk 8192 nocache &&clob_logging );

Table created.

SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                           676

1 row selected.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rpad('x',100,'x')
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                      27589960

1 row selected.

SQL>
SQL>
SQL>
SQL> define tab_logging = nologging
SQL> define clob_logging =  logging
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
  2  store as securefilefile (
  3  tablespace users enable storage in row chunk 8192 nocache &&clob_logging );

Table created.

SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                           564

1 row selected.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rpad('x',100,'x')
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                        133280

1 row selected.

SQL>
SQL>
SQL> define tab_logging = logging
SQL> define clob_logging =  nologging
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
  2  store as securefilefile (
  3  tablespace users enable storage in row chunk 8192 nocache &&clob_logging );

Table created.

SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                           676

1 row selected.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rpad('x',100,'x')
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                      27589944

1 row selected.

SQL>
SQL>
SQL> define tab_logging = nologging
SQL> define clob_logging =  nologging
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
  2  store as securefilefile (
  3  tablespace users enable storage in row chunk 8192 nocache &&clob_logging );

Table created.

SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                           564

1 row selected.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rpad('x',100,'x')
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                        133180

1 row selected.

SQL>
SQL>


and here is the same script for cached lobs


SQL> set verify off
SQL>
SQL> define tab_logging = logging
SQL> define clob_logging =  logging
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
  2  store as securefilefile (
  3  tablespace users enable storage in row chunk 8192 cache &&clob_logging );

Table created.

SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                           676

1 row selected.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rpad('x',100,'x')
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                      27589920

1 row selected.

SQL>
SQL>
SQL>
SQL> define tab_logging = nologging
SQL> define clob_logging =  logging
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
  2  store as securefilefile (
  3  tablespace users enable storage in row chunk 8192 cache &&clob_logging );

Table created.

SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                           564

1 row selected.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rpad('x',100,'x')
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                        133196

1 row selected.

SQL>
SQL>
SQL> define tab_logging = logging
SQL> define clob_logging =  nologging
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
  2  store as securefilefile (
  3  tablespace users enable storage in row chunk 8192 cache &&clob_logging );

Table created.

SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                           676

1 row selected.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rpad('x',100,'x')
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                      27589876

1 row selected.

SQL>
SQL>
SQL> define tab_logging = nologging
SQL> define clob_logging =  nologging
SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x clob ) &&tab_logging lob ( x )
  2  store as securefilefile (
  3  tablespace users enable storage in row chunk 8192 cache &&clob_logging );

Table created.

SQL>
SQL> conn scott/tiger@db19_pdb1
Connected.
SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                           564

1 row selected.

SQL>
SQL> insert /*+ APPEND */ into t
  2  select rpad('x',100,'x')
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select s.name, st.value
  2  from v$statname s, v$mystat st
  3  where st.STATISTIC# = s.STATISTIC#
  4  and s.name = 'redo size';

NAME                                                              VALUE
------------------------------------------------------------ ----------
redo size                                                        133196

1 row selected.

SQL>
SQL>


Going back to the first question...

Natividad Moreno, February 24, 2021 - 12:44 pm UTC

Hi Connor.

Thank you so much for confirming, that the logging property of inline LOBs can now be also modified!

Going back to my first question, do you know how could I possibly change this LOGGING property for a LOB created as a result of a extended datatype with an "alter table" statement? I tried with the syntax used for lob columns, but these extended datatypes are not recognized as lobs, and it fails.

In the database I want to change this property for the tables with lobs as extended datatypes, the underlying tablespace has the property LOGGING="YES". If it would be changed to "NO", would then these lobs for extended datatypes be created as NOLOGGING too?

Best regards,
Natividad.
Connor McDonald
February 25, 2021 - 5:36 am UTC

Unfortunately you don't get access to it, because (theoretically) the implementation is undefined. For example, one day we might use something other than a LOB to store it etc

SQL> create table t ( x varchar2(32767)) nologging;

Table created.

SQL>
SQL> insert into t values (rpad('x',5000));

1 row created.

SQL>
SQL> select logging
  2  from user_lobs
  3  where table_name = 'T';

LOGGING
-------
YES


More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database