Error Log enabling in insert and Analysis

Error Log enabling in insert and Analysis

In normal work, sometimes a batch of data needs to be inserted, which may be temporary tables, external tables, common tables, subqueries, and other forms, similar to the following format
Insert into xxxx (select xxxxx from xxx where xxxxx );
If redundant data exists, the entire Insert statement will automatically roll back, and one piece of data cannot be inserted. The error is similar to the following format.
Insert/* + append */into mow.memo select * from mow.memo_ext_92;
*
ERROR at line 1:
ORA-00001: unique constraint (mig_test.mow.memo_pk) violated

We may want to ensure that normal data can be inserted. It is a good choice to use error logs to process data that violates constraints and so on later.
The first step is to create an error log, which can be created by using the provided package or manually.
Here, I need to use the table containing the lob field. The following error occurs when an error log is created.
EXEC DBMS_ERRLOG.create_error_log (dml_table_name => 'mo1 _ memo ')
BEGIN DBMS_ERRLOG.create_error_log (dml_table_name => 'mo1 _ memo'); END;


ERROR at line 1:
ORA-20069: Unsupported column type (s) found: MEMO_SYSTEM_TEXT_C
ORA-06512: at "SYS. DBMS_ERRLOG", line 235
ORA-06512: at line 1

However, it is reasonable to think about it. But the problem still needs to be solved.
Let's take a look at the packages for creating error logs. Oracle has considered this. We can ignore this unsupported type, and of course we can also specify the name of the error log.
SQL> desc dbms_errlog
PROCEDURE CREATE_ERROR_LOG
Argument Name Type In/Out Default?
-------------------------------------------------------------------
DML_TABLE_NAME VARCHAR2 IN
ERR_LOG_TABLE_NAME VARCHAR2 IN DEFAULT
ERR_LOG_TABLE_OWNER VARCHAR2 IN DEFAULT
ERR_LOG_TABLE_SPACE VARCHAR2 IN DEFAULT
SKIP_UNSUPPORTED BOOLEAN IN DEFAULT


We create error logs
SQL> EXEC DBMS_ERRLOG.create_error_log (dml_table_name => 'mo1 _ memo', SKIP_UNSUPPORTED => true, ERR_LOG_TABLE_NAME => 'mo1 _ MEMO_ERROR ');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00. 03

Try to insert redundant data,
SQL> insert/* + append */into mow.memo select * from mow.memo_ext_92 LOG ERRORS INTO mow.memo_error ('test _ unique ') REJECT LIMIT UNLIMITED
2/
Insert/* + append */into mow.memo select * from mow.memo_ext_92 log errors into mow.memo_error ('test _ unique ') REJECT LIMIT UNLIMITED
*
ERROR at line 1:
ORA-00001: unique constraint (mig_test.mow.memo_pk) violated
Elapsed: 00:00:17. 32

If an error is directly thrown, it seems that the error log is not correctly enabled.
Check the error log, which is empty.

SQL> SELECT * FROM mow.memo_error; -- no rows

After repeated attempts, the reason was found to be the Hint. if you remove the Hint, there is no problem.
SQL> insert into mow.memo select * from mow.memo_ext_92 LOG ERRORS INTO mow.memo_error ('test _ unique') REJECT LIMIT UNLIMITED;
99 rows created.
Elapsed: 00:04:04. 83

1 * select count (*) from mow.memo_error
SQL>/
COUNT (*)
----------
907544

The error log contains detailed data. However, if you carefully check the execution time, you will find that if you do not use append, the performance will be much worse.
Below is a simple test,
If you do not use append, it takes about 1 minute to insert 0.8 million of data. If you use append, it takes about 13 seconds.
In addition, if about 0.8 million of the data in 99% records is redundant, it takes about 4 minutes to insert the error log.
SQL> insert into mow.memo select * from mow.memo_ext_99 LOG ERRORS INTO mow.memo_error REJECT LIMIT UNLIMITED;
877245 rows created.
Elapsed: 00:01:12. 91

SQL> insert into mow.memo select * from mow.memo_ext_98;
753637 rows created.
Elapsed: 00:01:04. 75

SQL> rollback;
Rollback complete.
Elapsed: 00:00:56. 35

SQL> insert/* + append */into mow.memo select * from mow.memo_ext_98;
753637 rows created.
Elapsed: 00:00:13. 20

Therefore, you can choose to enable error logs based on your needs, which has advantages and disadvantages.

Oracle-insert Performance Optimization

Data Tables cannot be inserted or updated in Oracle databases.

Data Tables cannot be inserted or updated in Oracle databases.

Oracle insert Extension

Oracle multi-table insert operations