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.Ĭonnor McDonald made the following comment, which is worth remembering. 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". 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. As a result, any PITR may need the table to be truncated and repopulated. The table structure will be protected, but the contents will not. When we use NOLOGGING we are no longer protecting the data, making it impossible to do point-in-time-recovery (PITR) of that data. From a performance perspective that may sound appealing, but remember the impact this has on data recovery. 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. So, except in the case of a NOARCHIVELOG mode database, the APPEND hint will only reduce redo generation if the table is set to NOLOGGING. We can see that altering the table to NOLOGGING makes the behavior return. To allow the APPEND hint to have an impact on redo generation again, we must set the table to NOLOGGING. Notice how the addition of the APPEND hint no longer has an impact on the amount of redo generated. The next example performs the same test, but this time on a database running in ARCHIVELOG mode.ĥ52 bytes received via SQL*Net from clientĥ66 bytes received via SQL*Net from client SQL> insert /*+ append */ into t1 select * from all_objects Ĩ10 bytes received via SQL*Net from clientĪs suggested, with the database running on NOARCHIVELOG mode, the addition of the APPEND hint did reduce the amount of redo generated. SQL> insert into t1 select * from all_objects ħ96 bytes received via SQL*Net from client SQL> create table t1 as select * from all_objects where 1=2 The redo generation is displayed in bold. The following example is run against a database running in NOARCHIVELOG mode. The examples below step through this process to show it in action. 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. If the database is running on NOARCHIVELOG mode, using just the APPEND hint will reduce redo generation. How the APPEND Hint Affects Redo Generation Use an online segment shrink operation to compact the data.Use and online table redefinition operation to recreate the 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. Export the data, truncate the table and import the data.These will need to be managed by one of the following types of shrink operation. 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.
#Lua table insert append free
How the APPEND Hint Affects the Table Size (High Water Mark)Īs 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. There are a number of additional restrictions mentioned here. The hint can still be used if referential integrity constraints and triggers on the table are disabled. * These two points could leave data logically corrupt, so Oracle ignores the APPEND hint and the data is loaded using conventional path if enabled referential integrity constraints and enabled triggers are present on a table. The combination of these features make direct-path inserts significantly quicker than conventional-path inserts.
How the APPEND Hint Affects Performance.Home » Articles » Misc » Here APPEND Hint
0 kommentar(er)
