Shulou(Shulou.com)06/01 Report--

Operator optimization:

IN operator

The advantage of SQL written in IN is that it is easy to write and easy to understand, which is more suitable for the style of modern software development.

However, the SQL performance of using IN is always low. There are the following differences between SQL with IN and SQL without IN from the steps performed by ORACLE:

ORACLE attempts to convert it into a join of multiple tables. If the conversion is not successful, it first executes the subquery in IN, and then queries the outer table records. If the conversion is successful, it directly uses the join mode of multiple tables. Thus it can be seen that there is at least one more conversion process in SQL with IN. General SQL can be converted successfully, but SQL with grouping statistics and other aspects can not be converted.

Recommended solution: try not to use the IN operator in business-intensive SQL.

NOT IN operator

This operation is not recommended for strong columns because it cannot apply the index of the table.

Recommended solution: use NOT EXISTS or (external connection + judged to be null) instead

Operator (not equal to)

Does not mean that the operator will never use an index, so processing it will only result in a full table scan.

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

A0 changed to a > 0 or axiom'

IS NULL or IS NOT NULL operation (determine whether the field is empty)

The index is generally not applied to determine whether the field is empty, because the B-tree index does not index null values.

Recommended options:

Replace it with other operations with the same function, such as

An is not null is changed to a > 0 or a >''and so on.

The field is not allowed to be empty, and a default value is used to replace the null value. For example, the status field in the industry expansion application is not allowed to be empty, and the default is the application.

Establish a bitmap index (partitioned tables cannot be built, and bitmap indexes are difficult to control. For example, indexes with too many field values will degrade performance, and multi-person update operations will increase block locking)

> and

There is a big difference between the effect of 2 and A > = 3, because when A > 2, ORACLE will first find the record index of 2 and then compare it, while A > = 3 ORACLE will directly find the record index of = 3.

LIKE operator

The LIKE operator can apply wildcard queries, and the combination of wildcards may reach almost any query, but if it is not used well, it will cause performance problems, such as LIKE 'T00%' this kind of query does not refer to the index, while LIKE 'X5400%' refers to the range index. A practical example: using the household identification number after the business number in the YW_YHJBQK table to query the business number YY_BH LIKE 'T00%' will generate a full table scan. If it is changed to YY_BH LIKE 'X5400%' OR YY_BH LIKE 'B5400%', the YY_BH index will be used to query two ranges, and the performance will certainly be greatly improved.

UNION operator

UNION filters out duplicate records after table linking, so it sorts the resulting result set after table linking, deletes duplicate records and returns the result. Most practical applications will not produce duplicate records, the most common is the process table and history table UNION. Such as:

Select * from gc_dfys union select * from ls_jg_dfys

This SQL first takes out the results of the two tables at run time, then sorts the duplicate records with the sort space, and finally returns the result set, which may lead to sorting by disk if the table has a large amount of data.

Recommended solution: use the UNION ALL operator instead of UNION, because the UNION ALL operation simply merges the two results and returns.

Select * from gc_dfys union all select * from ls_jg_dfys

The influence of SQL Writing

The influence of different Writing methods of SQL with the same function and the same performance

As an SQL programmer wrote in A for

Select * from zl_yhjbqk

B programmer wrote for

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

C programmers write

Select * from DLYX.ZLYHJBQK (uppercase table name)

The D programmer wrote

Select * from DLYX.ZLYHJBQK (with spaces in the middle)

After ORACLE analysis and arrangement, the results and execution time of the above four SQL are the same, but from the principle of ORACLE shared memory SGA, it can be concluded that ORACLE will analyze each SQL once and occupy shared memory. If the string and format of SQL are exactly the same, ORACLE will only analyze once, and shared memory will only leave analysis results once, which can not only reduce the time of analyzing SQL. And it can reduce the duplicate information of shared memory, and ORACLE can also accurately count the execution frequency of SQL.

The influence of conditional order after WHERE

The conditional order after the WHERE clause has a direct impact on the query of big data scale, such as

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

The dy_dj (voltage level) and xh_bz (closing sign) fields in the above two SQL are not indexed, so the full table scan is performed. The dy_dj = '1KV below' condition of the first SQL is 99% in the recordset, while the ratio of xh_bz=1 is only 0.5%. In the first SQL, 99% records are compared with dy_dj and xh_bz. In the second SQL, 0.5% records are compared with dy_dj and xh_bz, so it can be concluded that the CPU occupancy rate of the second SQL is significantly lower than that of the first.

The influence of the order of query tables

The order of lists in the tables after FROM will affect the performance of SQL. In the case of no index and no statistical analysis of the tables by ORACLE, ORACLE will link in the order in which the tables appear, because the order of the tables is not correct, which will result in data crossover that consumes server resources. (note: if the table is statistically analyzed, ORACLE will automatically link small tables, and then link large tables.)

The use of SQL sentence Index

Optimization of operators

Some optimizations for conditional fields

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

Substr (hbs_bh,1,4) = '5400%, optimized processing: hbs_bh like' 5400%'

Trunc (sk_rq) = trunc (sysdate), optimization processing:

Sk_rq > = trunc (sysdate) and sk_rq

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

Ss_df+20 > 50, optimization: ss_df > 30

'X' | | hbs_bh >' X5400021452'. Optimized processing: hbs_bh > '5400021542'

Sk_rq+5=sysdate, optimization processing: sk_rq=sysdate-5

Hbs_bh=5401002554, optimization: hbs_bh=' 5401002554, note: this condition implicitly converts to_number to hbs_bh because the hbs_bh field is character-based.

Conditions that include multiple fields in this table cannot be indexed, such as:

Ys_df > cx_df, unable to optimize

Qc_bh | | kh_bh='5400250000', optimization processing: qc_bh='5400' and kh_bh='250000'

Apply HINT (prompt) processing of ORACLE

Prompt processing is used when the execution path of SQL analysis generated by ORACLE is not satisfactory. It can prompt SQL for the following aspects

Tips on goals:

COST (optimized by cost)

RULE (optimized by rules)

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

ALL_ROWS (all rows are returned as soon as possible)

FIRST_ROWS (the first row of data is returned as soon as possible)

Tips for the execution method:

USE_NL (federated using NESTED LOOPS)

USE_MERGE (federated using MERGE JOIN)

USE_HASH (federated using HASH JOIN)

Index hint:

INDEX (TABLE INDEX) (query using the prompted table index)

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

Summary

The prompt function of ORACLE is a relatively strong function, but also a more complex application, and the prompt is only a suggestion for ORACLE to execute, and sometimes ORACLE may not follow the prompt for cost reasons. According to the practical application, it is generally not recommended that developers apply ORACLE prompts. Because the performance of each database and server is different, it is likely that the performance of one place has improved, but it has declined in another. ORACLE has become more mature in SQL execution analysis. If the path of the analysis is incorrect, it should be analyzed in the following aspects: the data base structure (mainly indexes), the current performance of the server (shared memory, disk file fragments), and whether the statistics of database objects (tables, indexes) are correct.

The above is the whole content of the analysis of the technical points of ORACLE SQL sentence optimization in this article. I hope it will be helpful to you. Interested friends can refer to: oracle database startup phase analysis, oracle virtual private database detailed introduction, talk about the difference between oracle rac and distributed database, Oracle RMAN automatic backup control file method introduction, etc., if you have any questions, you can leave a message at any time, editor must reply you in time, hope friends support a lot!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.