A big summary of Oracle SQL performance optimization techniques

(1) Select the most efficient table name order (valid only in the Rule-based optimizer):

The Oracle parser processes the table names in the FROM clause in Right-to-left order, the last table (driving table), which is written in the FROM clause, is processed first, and in the case where multiple tables are included in the FROM clause, you must select the table with the least number of records as the underlying table. If you have more than 3 table join queries, you need to select the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables.

(2) The order of joins in the WHERE clause. ：

Oracle parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

(3) Avoid the use of ' * ' in the SELECT clause:

Oracle converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time will be spent

(4) Reduce the number of accesses to the database:

Oracle has done a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, etc.

(5) Reset the arraysize parameters in Sql*plus, sql*forms and pro*c to increase the amount of retrieved data per database access, the recommended value is 200

(6) Use the Decode function to reduce processing time:

Use the Decode function to avoid repeatedly scanning the same record or repeating the same table.

(7) Simple integration, no associated database access:

If you have a few simple database query statements, you can integrate them into a single query (even if there is no relationship between them).

(8) Delete duplicate records:

The most efficient way to delete duplicate records (because of the ROWID) example:

DELETE from EMP E WHERE e.rowid > (SELECT MIN (X.ROWID)

From EMP X WHERE x.emp_no = e.emp_no);

(9) Replace Delete with truncate:

When you delete records in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state before it was deleted (accurately, before the deletion was performed) and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command is run, The data cannot be recovered. Therefore, very few resources are invoked and the execution time is short. (Translator: Truncate only applies when deleting full table, truncate is DDL not DML)