Analysis of SQL statement optimization technology

Optimization | statement
Operator optimization

In operator

The advantage of using in-write SQL is that it is easier to write and understand, which is more suited to the style of modern software development.

However, in-SQL performance is always relatively low, the steps performed from Oracle analyze the following differences in SQL with no in sql:

If the conversion is unsuccessful, Oracle attempts to convert it into a multiple-table connection by executing a subquery in inside, querying the outer table record, and querying the connection of multiple tables directly if the conversion succeeds. This shows that in the SQL at least one more conversion process. Normal SQL can be converted successfully, but for SQL that contains packet statistics, it cannot be converted.

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

Not in operator

This action is recommended by the strong column because it does not apply the index of the table.

Recommended scheme: Replace with not EXISTS or (outer join + null-judged) scheme

<> operator (not equal to)

Not equal to the operator is never used in the index, so processing it will only produce a full table scan.

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

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

A<> ' changed to A> '

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

Determining whether a field is empty generally does not apply the index because the B-tree index is not indexed for null values.

Recommended programs:

Replace with other operational operations of the same function, 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 substituted for the null value, such as the Status field in the industry expansion request is not allowed to be null, and the default is the application.

Create a bitmap index (a partitioned table is not built, bitmap index is more difficult to control, such as the field value too many indexes can degrade performance, many people update operation will increase the phenomenon of block lock)



> < operator (greater than or less than operator)

Greater-than or less-than operators are generally not adjusted, because it has an index will use index lookup, but in some cases it can be optimized, such as a table with 1 million records, a numeric field A, 300,000 recorded a=0,30 million records of the a=2,1 million records of A=3. The effect of performing a>2 and a>=3 is very different, because Oracle will first find the record index of 2 and then compare it to the A&GT;2, while Oracle can find the =3 record index directly when a>=3.



Like operator

The LIKE operator can apply wildcard queries, where the wildcard combination can be almost arbitrary, but if poorly used, it can create performance problems, such as the kind '%5400% ' query that does not refer to the index, as the ' x5400% ' refers to the range index. A practical example: Use the yw_yhjbqk of the business number in the table to query the business number YY_BH like '%5400% ' this condition will produce a full table scan, if the change to yy_bh like ' x5400% ' or yy_bh like ' b5400% ' will benefit With the YY_BH index of two range of queries, the performance must be greatly improved.



Union operator

The Union filters out duplicate records after a table link, so the resulting result sets are sorted after the table is connected, the duplicate records are deleted, and the results are returned. Most applications do not produce duplicate records, the most common of which is the process table and the History table Union. Such as:

SELECT * FROM Gc_dfys

Union

SELECT * FROM Ls_jg_dfys

This SQL is run with the result of two tables first, then sorted by sorting to delete duplicate records, and finally return the result set, if the table data volume may cause the disk to sort.

Recommendation: Use the union ALL operator instead of union because the union all operation simply merges two results and returns.

SELECT * FROM Gc_dfys

UNION ALL

SELECT * FROM Ls_jg_dfys



The impact of SQL writing

The same function, same performance, different syntax, SQL effect

such as a SQL in a programmer written for

Select * from Zl_yhjbqk

b The programmer writes for

Select * from dlyx.zl_yhjbqk (prefix with table owner)

C the programmer writes for

Select * from Dlyx. ZLYHJBQK (uppercase table name)

D Programmer writes for

Select * from Dlyx. ZLYHJBQK (more space in the middle)



The above four SQL results are the same as the execution time after Oracle profiling, but the principle of the Oracle Shared memory SGA allows Oracle to analyze it once for each SQL and consume shared memory. If you write the SQL string and the format exactly the same, Oracle will only analyze once, shared memory will only leave the analysis results, not only can reduce the time to analyze SQL, but also can reduce the share of memory duplication of information, Oracle can also accurately statistics the execution frequency of SQL.



The conditional order of the Where is affected



The conditional order after the WHERE clause has a direct effect on queries to large data scales, such as

Select * from zl_yhjbqk where dy_dj = ' 1KV below ' and xh_bz=1

Select * from Zl_yhjbqk where xh_bz=1 and dy_dj = ' below ' 1KV

The above two SQL DY_DJ (voltage level) and XH_BZ (PIN user flag) Two fields are not indexed, so the execution is all table scan, the first SQL DY_DJ = ' 1KV below ' condition in the recordset ratio is 99%, and the xh_bz=1 ratio is only 0.5%, In the first SQL when the 99% records are DY_DJ and xh_bz comparison, and in the second SQL when the 0.5% records are DY_DJ and xh_bz comparison, so that the second SQL CPU occupancy rate is significantly lower than the first one.



Effect of query Table order

The order of the lists in the table following from will have an impact on SQL performance, and Oracle will link the table in the order in which it appears if there are no indexes and the Oracle does not have statistical analysis of the tables, because the order of the tables does not result in data crossings that are extremely consuming server resources. (Note: If a statistical analysis of the table, Oracle will automatically advanced small table links, and then a large table link)



Use of SQL statement indexes

Optimization of operator (see above section)

Some optimizations for conditional fields

Fields that are processed by functions cannot take advantage of indexes, such as:

substr (hbs_bh,1,4) = ' 5400 ', optimizing processing: Hbs_bh like ' 5,400% '

Trunc (SK_RQ) =trunc (sysdate), optimized processing:

Sk_rq>=trunc (Sysdate) and Sk_rq<trunc (sysdate+1)

Fields that have an explicit or implicit operation cannot be indexed, such as:

SS_DF+20&GT;50, optimizing Processing: ss_df>30

' X ' | | Hbs_bh> ' X5400021452 ', optimized for processing:hbs_bh> ' 5400021542 '

Sk_rq+5=sysdate, optimizing Processing: sk_rq=sysdate-5

hbs_bh=5401002554, Optimizing Processing: hbs_bh= ' 5401002554 ', note: This condition implicitly to_number the conversion to HBS_BH because the Hbs_bh field is a character type.

A condition that includes multiple field operations for this table cannot be indexed, such as:

YS_DF&GT;CX_DF, unable to optimize

qc_bh| | Kh_bh= ' 5400250000 ', Optimizing Processing: qc_bh= ' 5400 ' and kh_bh= ' 250000 '



Apply Oracle's hint (hint) processing



Prompt processing is used when the SQL profiling execution path generated by Oracle is not satisfactory. It prompts you for the following aspects of SQL

Goal-related tips:

Cost (optimized by costs)

Rule (optimized by rules)

CHOOSE (default) (Oracle automatically selects costs or rules for optimization)

All_rows (all lines are returned as soon as possible)

First_rows (first line of data return as soon as possible)

Tips for executing methods:

USE_NL (combined with nested loops method)

Use_merge (union with MERGE join)

Use_hash (Federated with HASH join)

Index Hint:

Index (table index) (query by using a hint of the tables indexed)

Other advanced hints (such as parallel processing, etc.)



Oracle's hints are more powerful and complex applications, and hints are only a recommendation for Oracle, and sometimes Oracle may not follow the prompts for cost considerations. Based on practical applications, developers are generally not advised to apply Oracle hints, because each database and server performance is not the same, it is likely that one local performance increased, but the other place is down, Oracle in the SQL execution analysis has been more mature, If the parsing execution path does not first analyze the database structure (primarily the index), the server's current performance (shared memory, disk file fragmentation), database object (table, index) statistics are correct.




This article is my beginner Oracle wrote, some places are based on other information to participate, without my comprehensive testing, may be some local analysis is not correct, please a lot of users correct!!