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