Oracle SQL optimization

Operator optimization

In operator

The advantages of SQL in write are easier to write and easy to understand, which is more suitable for modern software development style.

But SQL performance with in is always lower, and the steps taken from Oracle to parse SQL with in is the following differences from SQL without in:

Oracle attempts to convert it into a connection to multiple tables, and if the conversion is unsuccessful, it executes the subquery in the inside, then queries the outer table record, and if the conversion succeeds, it directly uses the connection method of multiple tables. This shows that using in SQL at least one more conversion process. General SQL can be converted successfully, but for the inclusion of grouping statistics and other aspects of SQL cannot be converted.

Recommended scenario: Try not to use the in operator in a business-intensive SQL.

Not in operator

This action is not recommended for strong columns because it does not apply the index of the table.

Recommended scenario: Replace with not EXISTS or (outer join + empty) scheme

<> operator (not equal to)

The non-equal operator is never indexed, so processing it only results in a full table scan.

Recommended scenario: Replace with other operations of the same function, such as

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

A<> "instead of a>"

is null or is not NULL operation (determines whether the field is empty)

Determining whether a field is empty generally does not apply an index, because the B-tree index is not indexed by a null value.

Recommended Solutions:

Use other operations of the same function instead, such as

A is not NULL changed to a>0 or a> ', and so on.

The field is not allowed to be empty, and a default value is used instead of a null value, such as an application where the Status field is not allowed to be empty, and the default is the request.

Bitmap indexing (partitioned tables can not be built, bitmap indexes are difficult to control, such as the field values too many indexes will degrade performance, multi-person update operation will increase block lock phenomenon).

> and < operator (greater than or less than operator)

The greater than or less than the operator generally does not need to adjust, because it has an index to find the index, but in some cases it can be optimized, such as a table has 1 million records, a numeric field A, 300,000 records of a=0,30 Records of the A=1,3array million records a= 2, 10,000 records of A=3. There is a big difference between performing a>2 and a>=3, because Oracle finds the index of records for 2 and then compares them, while A>=3 Oracle locates the records index of =3 directly.

Like operator

The LIKE operator can apply a wildcard query, where the wildcard combination may reach almost arbitrary queries, but if used poorly it can produce performance problems, such as the "%5400%" query does not reference the index, and the "x5400%" reference to the scope index. A practical example: Use the user identification number behind the business number in the YW_YHJBQK table to query the business number YY_BH like '%5400% ' this condition will result in a full table scan, if changed to yy_bh like ' x5400% ' or yy_bh like ' b5400% ' will benefit The performance of the two-range query with YY_BH Index is certainly greatly improved.

Oracle SQL optimization