How the APPEND Hint Affects the Table Size (High Water Mark)
As direct-path inserts append data to the end of the table, they constantly increase the table high water mark, even if there is lots of free space within the table.
In tables that regularly have rows deleted, the use of the the
APPEND
hint can result in large tables containing lots of sparsely populated blocks. These will need to be managed by one of the following types of shrink operation.
Export the data, truncate the table and import the data.
Use a "
CREATE TABLE ... AS SELECT
" (CTAS) operation to build a new table with the data compacted, drop the original table and rename the new table to replace the original.
How the APPEND Hint Affects Redo Generation
If the database is running on
NOARCHIVELOG
mode, using just the
APPEND
hint will reduce redo generation. In reality, you will rarely run OLTP databases in
NOARCHIVELOG
mode, so what happens in
ARCHIVELOG
mode? In
ARCHIVELOG
mode, using the
APPEND
hint will not reduce redo generation unless the table is set to
NOLOGGING
. The examples below step through this process to show it in action.
The following example is run against a database running in
NOARCHIVELOG
mode. The redo generation is displayed in bold.
SQL> create table t1 as select * from all_objects where 1=2;
Table created.
SQL> set autotrace on statistics
SQL> insert into t1 select * from all_objects;
72512 rows created.
Statistics
----------------------------------------------------------
634 recursive calls
9946 db block gets
50116 consistent gets
2 physical reads
8464520 redo size
830 bytes sent via SQL*Net to client
796 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1508 sorts (memory)
0 sorts (disk)
72512 rows processed
SQL> truncate table t1;
Table truncated.
SQL> insert /*+ append */ into t1 select * from all_objects;
72512 rows created.
Statistics
----------------------------------------------------------
369 recursive calls
1689 db block gets
48194 consistent gets
2 physical reads
46048 redo size
822 bytes sent via SQL*Net to client
810 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1500 sorts (memory)
0 sorts (disk)
72512 rows processed
SQL> commit;
Commit complete.
As suggested, with the database running on
NOARCHIVELOG
mode, the addition of the
APPEND
hint did reduce the amount of redo generated.
The next example performs the same test, but this time on a database running in
ARCHIVELOG
mode.
SQL> create table t1 as select * from all_objects where 1=2;
Table created.
SQL> set autotrace on statistics
SQL> insert into t1 select * from all_objects;
88773 rows created.
Statistics
----------------------------------------------------------
613 recursive calls
11792 db block gets
116808 consistent gets
2 physical reads
10222352 redo size
370 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3142 sorts (memory)
0 sorts (disk)
88773 rows processed
SQL> truncate table t1;
Table truncated.
SQL> insert /*+ append */ into t1 select * from all_objects;
88773 rows created.
Statistics
----------------------------------------------------------
307 recursive calls
1573 db block gets
114486 consistent gets
0 physical reads
10222864 redo size
366 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3138 sorts (memory)
0 sorts (disk)
88773 rows processed
SQL> commit;
Commit complete.
Notice how the addition of the
APPEND
hint no longer has an impact on the amount of redo generated.
To allow the
APPEND
hint to have an impact on redo generation again, we must set the table to
NOLOGGING
.
SQL> alter table t1 nologging;
Table altered.
SQL> truncate table t1;
Table truncated.
SQL> set autotrace on statistics
SQL> insert into t1 select * from all_objects;
88773 rows created.
Statistics
----------------------------------------------------------
506 recursive calls
11790 db block gets
116652 consistent gets
0 physical reads
10222328 redo size
373 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3139 sorts (memory)
0 sorts (disk)
88773 rows processed
SQL> truncate table t1;
Table truncated.
SQL> insert /*+ append */ into t1 select * from all_objects;
88773 rows created.
Statistics
----------------------------------------------------------
307 recursive calls
1573 db block gets
114486 consistent gets
0 physical reads
25968 redo size
366 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3138 sorts (memory)
0 sorts (disk)
88773 rows processed
SQL> commit;
Commit complete.
SQL> drop table t1 purge;
Table dropped.
We can see that altering the table to
NOLOGGING
makes the behavior return.
So, except in the case of a
NOARCHIVELOG
mode database, the
APPEND
hint will only reduce redo generation if the table[space] is set to
NOLOGGING
.
Using NOLOGGING
In the previous section we demonstrated the reduction in redo generation by combining the
APPEND
hint with
NOLOGGING
in a database running in
ARCHIVELOG
mode. From a performance perspective that may sound appealing, but remember the impact this has on data recovery. When we use
NOLOGGING
we are no longer protecting the data, making it impossible to do point-in-time-recovery (PITR) of that data. The table structure will be protected, but the contents will not. As a result, any PITR may need the table to be truncated and repopulated. This is fine for transient tables, like staging tables in an ETL process, but it is a bad idea to any data you care about.
I prefer to keep transient staging tables in separate
NOLOGGING
tablespaces, so everyone understands the contents of those tablespaces are not protected like "real tables".
It should also be noted, in some situations like data guard environments using forced logging, the use of
NOLOGGING
is overridden, and will not result in the desired effect.
Hope this helps. Regards Tim...
