Oracle database optimization skills (1)


Oracle database optimization skills (1)

1. Join order in where clause

In Oracle database, the execution order of where clause isBottom upAccording to this principle, the connection between tables must be written before other where conditions, and those conditions that can filter out the maximum number of records must be written before other where conditions

It must be written at the end of the where clause.

2. Avoid using ‘*’ in the select clause (it is forbidden to use *) in development
In the process of parsing, Oracle will convert ‘*’ to all column names in turn. This work is done by querying the data dictionary, which means more time will be spent.

3. Replace delete with truncate

When the records in the table are deleted, usually, the rollback segments are used to store the information that can be recovered.

If you don’t have a commit transaction, Oracle will restore the data to the state before deletion (to be exact, the state before executing the deletion command). When using truncate, the rollback segment will no longer store any recoverable messages

Interest. When the command runs, the data cannot be recovered. Therefore, few resources are called and the execution time is short.

Note: AlthoughThe efficiency of truncate is higher than that of deleteThe data deleted by truncate cannot be recovered, so it is not recommended to use it in actual development


4. Replace the having clause with the where clause

Avoid using the having clause. Having will only filter the result set after all records are retrieved. This process requires sorting, totaling and other operations

If we can limit the number of records by where clause, we can reduce the cost

Among the three clauses that can add conditions, on is the first to execute, where is the second, and having is the last. Because on filters the records that do not meet the conditions before making statistics, it can be subtracted

The data to be processed by less intermediate operations should be the fastest, and where should be faster than having, because it filters data before sum, and only uses on when two tables are joined.

In the case of single table query statistics, if the filtering conditions do not involve the fields to be calculated, their results will be the same, but where can use Rushmore technology, while living can’t

The latter is slower.

If the calculated field is involved, it means that the value of this field is uncertain before the calculation. The action time of where is completed before the calculation, and living is only effective after the calculation. So

In this case, the results will be different. In multi table join query, on works earlier than where.

5. Reduce the query of tables

The main table after from is full table scanning in the process of execution. In all forms of table access, full table scanning is the most time-consuming. Therefore, under the condition of meeting the business logic, we should reduce sub queries (that is, reduce full table scanning), which can be used

The associated query replaces the subquery.


6. Use index to improve efficiency

Index is a conceptual part of a table, which is used to improve the efficiency of data retrieval. Generally, querying data through index is faster than scanning the whole table

When Oracle finds out the best path to execute query and update statement, Oracle optimizer will use index. Similarly, using index can improve efficiency when joining multiple tables. Another advantage of using index is that it provides primar key

Verification of the uniqueness of.

  There are two main points needed to use all of them

a. If the amount of data retrieved exceeds 30% of the records in the table, the efficiency of using index will not be significantly improved.

b. In some cases, using an index may be slower than a full table scan, but it’s the same order of magnitude. In general, the use of index scan than the whole table to block several times or even thousands of times!


7. SQL statements are capitalized

Because Oracle always parses SQL statements first, converts lowercase letters into uppercase ones, and then executes them. In order to reduce the time consumed by parsing, it is recommended to use uppercase for SQL statements.

Editor case conversion shortcut (for idea and eclipse)

  CTRL + Shift + X changes the currently selected text to uppercase

CTRL + Shift + y changes the currently selected text to lowercase