Oracle Database query performance optimization

Oracle Database query performance optimization

Querying, adding, modifying, and deleting databases are important factors that affect web application performance indicators. The following are some common suggestions to improve query performance.

1. Optimize JDBC connections

The database connection pool mechanism can be used to store opened database connections in the cache, so that other parts of the program can continue to be used, thus saving the database connection time.

2. Improve the query speed of the select clause

(1) Create an index

If you want to query data through a field in a table, you can set this field as an index of the table. In the select query, if the queried column is an index column, the database will scan the data from the index table and no longer need to scan the entire data table. This will greatly improve the performance.

(2) Avoid "*" in the select clause.

During the parsing process, the database converts "*" to all column names in sequence. This task is done by querying the data dictionary, which means it takes more time. It is best to write the column names one by one.

3. Avoid resource-consuming operations

SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by enable the SQL engine to execute resource-consuming sorting (SORT. DISTINCT requires a sorting operation, while other operations require at least two sorting operations. Group by triggers the nested sort operation. When UNION is executed, the sort unique operation is executed and later than the NESTED sorting operation. The depth of embedded sorting greatly affects the query efficiency.

4. Optimize the where clause to increase the query speed

(1) SQL statement in uppercase: Because Oracle always parses SQL statements first, converts lowercase letters to uppercase and then executes them.

(2) connection sequence in the WHERE clause: ORACLE uses a bottom-up (from right to left) sequence to parse the WHERE clause. Based on this principle, the connections between tables must be written before other WHERE conditions, the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

(3) Replace HAVING clause with the Where clause: avoid HAVING clause. HAVING filters the result set only after all records are retrieved. this process requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced.

(4) When querying multiple tables, use the table alias: This reduces the parsing time and syntax errors caused by Column ambiguity.

(5) Replace IN with EXISTS, replace not in with not exists, and replace DISTINCT with EXISTS: IN subqueries, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). to avoid using not in, we can rewrite it into an Outer join (Outer Joins) or not exists. EXISTS makes the query faster, because the RDBMS core module will return results immediately after the subquery conditions are met.

(6) Optimize group by: to improve the efficiency of group by statements, you can filter unwanted records before it.

(7) Efficient Use of where clauses: Some where clauses can be replaced without indexes (indexes only tell the table content, but cannot tell the table that the table does not exist ), for example, replace a with a> 0 and a <0! = 0, a <> 0, replaced by in or.