Content other than SELECT

Insert Direct Path method

This method does not look for space in an existing block, it starts inserting data directly above the high water level. Using the nologging mode directly, remember that indexes on tables loaded by direct path insertions By default are still generating undo and redo. The table data is modified only by the data dictionary because nologging does not produce redo and undo.

Insert/*+ Append */Into table select * from Hr.employees nologging;

Insert/*+ Append_values */Into dual (dumy) VALUES (' Y ');

However, the quick approach has several questions.

• Only one direct path can be written to a table at any given point in time
• The data will be inserted above the high water level, so no free space below any high water level can be used in direct path insertion
• A session that is inserted after the start cannot do anything to the table (even select the table), directing the Commit or rollback.
• Some less commonly used data structures (object types, index organization tables, and so on) are not supported
• Referential constraints are not supported (that is, they will cause insertion through traditional methods)

Multiple table Insert Condition insertion

The above two, in other blogs have been introduced

DML error Log

This feature provides a mechanism to allow your 1 million rows of data to be inserted without having to fail just a few lines of trouble, introduced after 10g, similar to the Sql*loader error logging feature, whose rationale is to put any records that could cause a statement failure to be transferred into a single error log table. In addition, the LOG ERRORS clause is equally applicable in other DML statements (update, delete, merge)

1) suitable for dbms_errlog. Create_error_log to create love you error log table

2) Declare the LOG ERRORS clause in the INSERT statement

Execute dbms_errlog. Create_error_log (' big_emp ', ' Big_emp_bad ');

For example:

Insert into Big_emp (employee_id, first_name, last_name, hire_date, email, department_id, job_id)

VALUES (303, ' Bob ', ' Loblaw ', ' 01-jan-10 ', '[email protected]', ' 2a45 ', 1)

Log errors into Big_emp_bad;

This way, if an insert error occurs, the data is inserted into the Big_emp_bad table, and then we can modify the script that inserts the data, and then reinsert it according to what is not inserted in the Big_emp_bad table, because if an error is encountered, it will be rolled back when it is just inserted, so It is possible that none of the 1 data has been inserted.

Although DML error logging is very powerful, you need to be aware of the following warnings

• The log ERRORS clause does not cause an implicit commit, and the insertion of an error record is handled by an autonomous transaction, which means that even if an error is returned and the bad record is inserted into the ERRORS table, you can either commit or roll back the record inserted into the base table, even if the transaction was rolled back, loaded into ERRORS The records in the table will also be retained.
• The LOG ERRORS clause does not disable the APPEND hint. If the append hint is used, the insertion of the base table will be done using the direct path write mechanism. However, any writes to the errors table are not written using a direct path. This is usually not a problem, because you rarely load large amounts of data into the Errors table.
• A direct path insert operation that violates a unique key or index constraint will cause the statement to fail and be rolled back.
• The log ERRORS clause does not track the value of the log, LONG, or object type column, and it can be used with tables that contain columns of unsupported data types but columns of these unsupported data types will not be inserted into the ERRORS table. Therefore, it is important to note that if you continue to use ERRORS, you must use the skip_unsupported parameter of the create_error_log stored procedure if you still need to use it.

Update

If you need to update a lot of content, such as an app that needs to update 1 billion data nightly, the best way is to truncate and reload it first. If not, a new table can be created using the CREATE table as select.

A lot of updates are not a good idea.

DELETE

Similar to a large number of updates, large deletions are often not a good idea. It is usually faster to rebuild a table or a partition than to delete a large percentage of the data rows in the table. The biggest drawback to rebuilding is that objects must be protected during the rebuild to prevent other modifications. Basic ideas

1) Create a temporary table

2) Place records that do not need to be deleted into the staging table (you can use the Append method)

3) Rebuilding related objects (indexes, constraints, authorizations, and triggers)

4) Rename Table

In addition, if bussiness logic allows us to truncate, then this is also a good solution

MERGE

Syntax is not described here, the MERGE provides maximum flexibility, so do not overlook the fact that this individual SQL statement can perform multiple DML operations during a single execution.

Content other than SELECT