NOLOGGING on an Index applies only during a CREATE or REBUILD. Not after either action.
If you have created or, subsequently, set an Index to NOLOGGING, the NOLOGGING doesn't really apply to DML. INSERTs will generate Redo in respect of the necessary updates to the Index.
NOLOGGING on an Index does not apply to DELETE and UPDATE (of Index Key columns) operations at all. All such DML will always generate Undo and Redo.
Below is a simple demonstration of the impact the presence of 1 index has on a 1.6million row INSERT.
The first table has no Index and the INSERT operation with APPEND and NOLOGGING defined at the Table level generates very little Undo and Redo.
The second table does have 1 index only and yet, it generates a very high volume of Undo and Redo -- which are for updates to the Index to add new entries.
SQL> desc source_table;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select count(*) from source_table;
COUNT(*)
----------
1620768
SQL>
SQL> drop table target_no_index purge;
Table dropped.
SQL> create table target_no_index as select * from source_table where 1=2;
Table created.
SQL> alter table target_no_index nologging;
Table altered.
SQL>
SQL> drop table target_with_index purge;
Table dropped.
SQL> create table target_with_index as select * from source_table where 1=2;
Table created.
SQL> alter table target_with_index nologging;
Table altered.
SQL> create index target_ndx on target_with_index(object_id, owner) nologging;
Index created.
SQL> alter index target_ndx nologging;
Index altered.
SQL>
SQL>
SQL> REM REM REM ############################################################
SQL> -- Redo and Undo Stats on NOLOGGING Table with No Indexes
SQL> connect hemant/hemant
Connected.
SQL> select sn.name Stat_Name, ms.value Stat_Value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
5 order by 1
6 /
STAT_NAME STAT_VALUE
---------------------------------------------------------------- ----------
redo entries 0
redo size 0
undo change vector size 0
SQL>
SQL> insert /*+ APPEND */ into target_no_index select * from source_table;
1620768 rows created.
SQL> commit;
Commit complete.
SQL> -- show impact of first insert
SQL> select sn.name Stat_Name, ms.value Stat_Value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
5 order by 1
6 /
STAT_NAME STAT_VALUE
---------------------------------------------------------------- ----------
redo entries 2824
redo size 375628
undo change vector size 85140
SQL>
SQL> connect hemant/hemant
Connected.
SQL> insert /*+ APPEND */ into target_no_index select * from source_table;
1620768 rows created.
SQL> commit;
Commit complete.
SQL> -- show impact of second insert
SQL> select sn.name Stat_Name, ms.value Stat_Value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
5 order by 1
6 /
STAT_NAME STAT_VALUE
---------------------------------------------------------------- ----------
redo entries 1355
redo size 137784
undo change vector size 23748
SQL>
SQL>
SQL> REM REM REM ############################################################
SQL>
SQL> REM REM REM ############################################################
SQL> -- Redo and Undo Stats on NOLOGGING Table with 1 Index
SQL> connect hemant/hemant
Connected.
SQL> select sn.name Stat_Name, ms.value Stat_Value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
5 order by 1
6 /
STAT_NAME STAT_VALUE
---------------------------------------------------------------- ----------
redo entries 0
redo size 0
undo change vector size 0
SQL>
SQL> insert /*+ APPEND */ into target_with_index select * from source_table;
1620768 rows created.
SQL> commit;
Commit complete.
SQL> -- show impact of first insert
SQL> select sn.name Stat_Name, ms.value Stat_Value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
5 order by 1
6 /
STAT_NAME STAT_VALUE
---------------------------------------------------------------- ----------
redo entries 57906
redo size 149359644
undo change vector size 70866196
SQL>
SQL> connect hemant/hemant
Connected.
SQL> insert /*+ APPEND */ into target_with_index select * from source_table;
1620768 rows created.
SQL> commit;
Commit complete.
SQL> -- show impact of second insert
SQL> select sn.name Stat_Name, ms.value Stat_Value
2 from v$statname sn, v$mystat ms
3 where sn.statistic#=ms.statistic#
4 and ( sn.name in ('redo entries', 'redo size', 'undo change vector size'))
5 order by 1
6 /
STAT_NAME STAT_VALUE
---------------------------------------------------------------- ----------
redo entries 142198
redo size 249846348
undo change vector size 117892788
SQL>
Thus, we see that INSERT operation on the table "target_with_index" generates significantly more Undo and Redo because of the presence of the index.
.
.
.
No comments:
Post a Comment