TRUNCATE Vs Index Status

Comments: The Redo size of the current session

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;NAME VALUE
-------------- ----------
redo size 32404
Comments: create the CTAS table with NOLOGGING.

SQL> create table t nologging as select * from all_objects where 1=0;

Table created.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;
NAME VALUE
-------------- ----------
redo size 69912

Comments: create the index with NOLOGGING option.

SQL> create index object_name_idx on t(object_name) nologging;

Index created.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;
NAME VALUE
-------------- ----------
redo size 87920

Comments: Insert the records into table with the append hint. It will not create any Redo for the table data but it will create a Redo for the index. Check the Redo size and It is high.

SQL> insert /*+ append */ into t select * from all_objects;

27196 rows created.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;

NAME VALUE
-------------- ----------
redo size 4302432

Comments: Make the index as UNUSABLE state.

SQL> alter index object_name_idx unusable;

Index altered.

SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;
NAME VALUE
-------------- ----------
redo size 4305028
Comments: Check the status of the index and truncate the table data.

SQL> select index_name, status from user_indexes where table_name = 'T';INDEX_NAME STATUS
------------------------------ --------
OBJECT_NAME_IDX UNUSABLE

SQL> truncate table t;

Table truncated.
Comments: Insert the records into table with the append hint.

SQL> insert /*+ append */ into t select * from all_objects;

27196 rows created.

Comments: Now we check the Redo generation. Still it is high and it has generated the Redo for the index because the TRUNCATE operation has made that index as VALID.


SQL> select name,a.value
from v$sesstat a, v$sysstat b
where b.statistic#=a.statistic#
and b.name = 'redo size' and sid = 16;

NAME VALUE
-------------- ----------
redo size 8529448

Comments: check the current status of the index.

SQL> select index_name, status from user_indexes where table_name = 'T';
INDEX_NAME STATUS
------------------------------ --------
OBJECT_NAME_IDX VALID