Oracle query statement Optimization

Oracle query statement optimization. The advantage of SQL written IN is that it is easier to write and understand, which is more suitable for modern software development. However, the SQL Performance with IN is always compared.

Oracle query statement optimization. The advantage of SQL written IN is that it is easier to write and understand, which is more suitable for modern software development. However, the SQL Performance with IN is always compared.

IN Operator

SQL statements written IN are easy to write and understand, which is suitable for modern software development. However, SQL statements using IN always have low performance. The following differences exist between SQL statements using IN and SQL statements without IN: ORACLE tries to convert it to the join of multiple tables. If the conversion fails, it first executes the subquery IN and then queries the outer table records, if the conversion is successful, multiple tables are directly connected for query. It can be seen that at least one conversion process is added to SQL statements using IN. General SQL statements can be converted successfully, but SQL statements that contain grouping statistics cannot be converted.

Recommended Solution: avoid using the IN operator IN business-intensive SQL statements.

Not in Operator

This operation is not recommended for strong columns because it cannot apply table indexes.

Recommended Solution: Use NOTEXISTS or (Outer Join + null) instead

<> Operator (not equal)

The non-equals operator will never use the index, so the processing of it will only generate a full table scan.

Recommended Solution: use operations with the same functions, such

A <> 0 to a> 0 or a <0

A <> ''To a>''

ISNULL or ISNOTNULL operation (determines whether the field is empty)

Generally, indexes are not used to determine whether a field is null, because the B-tree index does not have a null index.

Recommended Solution: replace it with other operations with the same function, for example, change aisnotnull to a> 0 or a>. Fields are not allowed to be empty, but a default value is used to replace null values. For example, status fields in the application for expansion cannot be empty. The default value is application. Create a bitmap index (partition-based tables cannot be created, and bitmap indexes are difficult to control. If too many fields are indexed, performance will be degraded, and data block locks will be added when multiple users perform update operations)

> And <操作符（大于或小于操作符）< p>

If the value is greater than or less than the operator, you do not need to adjust it. Because it has an index, index search is used, but in some cases it can be optimized. For example, if a table has 1 million records ,, for A numeric field A, 0.3 million records A = 3. Therefore, the effect of executing A> 2 and A> = 3 is very different, because ORACLE will first find the record index of 2 and then compare it, when A> = 3, ORACLE directly finds the record Index = 3.

LIKE Operator

The LIKE operator can be used for wildcard queries. The wildcard combinations in the LIKE operator can be used for almost any queries. However, poor use may result in performance problems, for example, LIKE '% 100' does not reference the index, while LIKE 'x5400%' references the range index. An actual example: the user ID following the Business ID in the YW_YHJBQK table can be used to query the Business ID YY_BHLIKE '% 100'. This condition generates a full table scan, if it is changed to YY_BHLIKE 'x5400% 'oryy _ BHLIKE 'b5400% ', the index of YY_BH will be used to query the two ranges, and the performance will be greatly improved.