Implications of logging and nologging Modes

1. Use Direct Path Loads: The conventional path loader essentially loads the data by using standard insert statements. The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format. The fact that SQL is not being issued makes the entire process much less taxing on the database. There are certain cases, however, in which direct path loads cannot be used (clustered tables). Direct path loads bypass the Oracle buffer cache and write data directly to the database files, which can significantly improve performance. Direct path loads are typically used for loading large amounts of data, such as data from a data warehouse or a flat file. They can also be used to load data from an external table into a regular table. To prepare the database for direct path loads, the script
   

   $ORACLE_HOME/rdbms/admin/catldr.sql.sql 
   

   
   must be executed.
2. Disable Indexes and Constraints: For conventional data loads only, the disabling of indexes and constraints can greatly enhance the performance of SQL*Loader
3. Use a Larger Bind Array: For conventional data loads only, larger bind arrays limit the number of calls to the database and increase performance. The size of the bind array is specified using the bindsize parameter. The bind array's size is equivalent to the number of rows it contains (rows=) times the maximum length of each row. Also see the columnarrayrows and streamsize parameters.
4. Use ROWS=n : For conventional data loads only, rows specifies the number of rows per commit and is related to bindsize. Issuing fewer commits will enhance performance, and the larger rows parameter affects performance (see benchmark below).
5. Use Parallel Loads: Available with direct path data loads only, this option allows multiple SQL*Loader jobs to execute concurrently.
   

   $ sqlldr control=first.ctl  parallel=true direct=true 
   $ sqlldr control=second.ctl parallel=true direct=true 
   

6. Use Fixed Width Data: Fixed width data format saves Oracle some processing when parsing the data. The savings can be tremendous, depending on the type of data and number of rows.
7. Disable Archiving During Load: While this may not be feasible in certain environments, disabling database archiving can increase performance considerably.
8. Use unrecoverable. The unrecoverable option (unrecoverable load data) disables the writing of the data to the redo logs. This option is available for direct path loads only.
   The unrecoverable option (unrecoverable load data) still exists in Oracle. It was introduced in Oracle 7 and is still available in Oracle 19c. The unrecoverable option can be used to improve the performance of data loads by disabling the writing of redo log entries. However, it is important to note that using the unrecoverable option can make the database more vulnerable to data loss in the event of a failure. To use the unrecoverable option, you can specify the `UNRECOVERABLE` clause when using the `SQL*Loader` utility to load data. For example, the following command would load data from the `data.csv` file into the `emp` table using the unrecoverable option:
   

   sqlldr userid=scott/tiger control=loader.ctl data=data.csv UNRECOVERABLE
   

   
   It is important to note that the unrecoverable option should only be used in limited circumstances, such as when loading large amounts of data and the database is regularly backed up. Here are some of the risks associated with using the unrecoverable option:
   • If the database crashes before the data load is complete, the data that was loaded will not be recoverable.
   • If the database is corrupted before the data load is complete, the data that was loaded may also be corrupted.
   • If the database is backed up before the data load is complete, the backup will not contain the loaded data.
   
   If you need to use the unrecoverable option, be sure to take steps to mitigate the risks, such as backing up the database regularly and testing the backups to ensure that they are recoverable.

Direct-load INSERT with nologging

ALTER TABLE emp NOLOGGING;
ALTER TABLESPACE user_data NOLOGGING;
ALTER TABLE emp MODIFY PARTITION emp_history NOLOGGING;

1. You can insert into tables with nologging - If you use the APPEND hint and place the table in nologging mode, redo will be bypassed.

alter table customer nologging;

insert /*+ append */ into customer values ('hello',';there');

create index newidx ... nologging;

   alter table...move partition
   alter table...split partition
   alter index...split partition
   alter index...rebuild
   alter index...rebuild partition
   create table...as select
   create index
   direct load with SQL*Loader
   direct load INSERT (using APPEND)

Create table  
   new_customer 
tablespace  
   new_ts  
NOLOGGING  
as 
   select * from customer;
Drop table customer;
Rename new_customer to customer;

Direct-Load Insert

ALTER TABLE MON_TRANS NOPARALLEL;
INSERT /*+ APPEND */ INTO MON_TRANS
SELECT * FROM DAILY_TRANS;

ALTER TABLE MON_TRANS NOLOGGING;