Analysis of SQL statement optimization techniques

Excerpted from http://www.cnblogs.com/wxj1020/archive/2008/04/27/1173638.html

I have been training in the database in recent weeks and I have benefited greatly from the excellent technical and vivid explanations of our teachers. In order to let more novice benefit, I took the time to put the SQL statement optimization part of the collation, I hope we progress together.

One, the operator optimization

1, 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: in a business-intensive SQL, try not to use the in operator, instead of using the EXISTS scheme.

2, not in operator

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

Recommended scenario: Replace with not EXISTS scheme

3, 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 index is not an index null value.

Recommended scenario : Replace with other operations with the same function, such as: A is not null changed to A>0 or a> ", etc. The field is not allowed to be empty, but instead of a null value with a default value, such as the Status field in the requisition is not allowed to be empty, the default is the request.

4, > 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 will be indexed to find, 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,39 million records of a=2,1 Records of the 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.

5. 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.

6. Union operator

The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. Most of the actual applications do not produce duplicate records, the most common being the process table and the History table Union. Such as:
SELECT * FROM Gc_dfys
Union
SELECT * FROM Ls_jg_dfys
This SQL takes out the results of two tables at run time, then sorts the duplicate records with the sort space, and finally returns the result set, which may cause the disk to be sorted if the table data volume is large.

Recommended Scenario: 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

Second, the impact of SQL writing

1, the same function the same performance different SQL effect.

As a SQL in a programmer wrote for Select * from ZL_YHJBQK

B programmer writes for Select * from DLYX.ZL_YHJBQK (prefixed with table owner)

C Programmers write for Select * from Dlyx. ZLYHJBQK (uppercase table name)

The D programmer writes for Select * from Dlyx. Zlyhjbqk (more spaces in the middle)

The result of the above four SQL is the same as the execution time after the Oracle analysis, but from the Oracle shared memory SGA, it can be concluded that Oracle will analyze each SQL and consume shared memory. If you write the SQL string and format exactly the same, then Oracle will only parse once, and the shared memory will only leave a single analysis, not only to reduce the time to analyze SQL, but also to reduce the duplication of shared memory information, Oracle can accurately count the frequency of SQL execution.

2, where the condition order after the influence

The condition order after the WHERE clause has a direct effect on the query of the large data scale. Such as:
Select * from zl_yhjbqk where dy_dj = ' 1KV or less ' and xh_bz=1
Select * from Zl_yhjbqk where xh_bz=1 and dy_dj = ' 1KV or less '
The above two SQL DY_DJ (voltage level) and XH_BZ (PIN household sign) Two fields are not indexed, so the execution is full table scan, the first SQL DY_DJ = ' 1KV below ' condition in the recordset ratio is 99%, and xh_bz=1 ratio is only 0.5%, At the time of the first SQL 99% records are compared Dy_dj and xh_bz, while in the second SQL 0.5% records are DY_DJ and xh_bz comparisons, so that the second SQL CPU utilization is significantly lower than the first one.

3. Influence of query Table order

The order of the list in the table following the from will have a performance impact on SQL, and with no indexes and no statistical analysis of the tables by Oracle, Oracle will be linked in the order in which the tables appear, so that the order of the tables is not the same as the data that is consuming the server resource. (Note: If the table is statistically analyzed, Oracle will automatically link the small table and then the large table)

Third, the use of SQL statement index

1. Operator optimization (IBID.)

2, some optimization of the condition field

fields that use function processing cannot take advantage of indexes such as:

substr (hbs_bh,1,4) = ' 5400 ', optimized 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 been explicitly or implicitly operated cannot be indexed, such as: ss_df+20>50, optimized processing: ss_df>30

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

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

hbs_bh=5401002554, optimized processing: hbs_bh= ' 5401002554 ', note: This condition implicitly to_number conversion for HBS_BH, because the Hbs_bh field is a character type.

field operations that include multiple tables in the condition cannot be indexed , such as: YS_DF>CX_DF, cannot be optimized
QC_BH | | Kh_bh= ' 5400250000 ', optimized processing: qc_bh= ' 5400 ' and kh_bh= ' 250000 '

Iv. Other

Oracle's hints feature is a relatively strong feature and is a complex application, and hints are only a recommendation to Oracle, and sometimes Oracle may not follow the prompts for cost considerations. According to practical application, the developer is generally not recommended to apply the Oracle hint, because the performance of the database and server is not the same, it is possible to improve performance in one place, but the other is down, Oracle in the SQL to perform the analysis is more mature, If the parse execution path is not the first should be analyzed in the database structure (mainly index), the server's current performance (shared memory, disk file fragmentation), database objects (tables, indexes) statistics are correct.

Analysis of SQL statement optimization techniques