SQL Optimization Technical ANALYSIS-1: operator optimization

One, operator optimization 1, in the operator in the write out of the advantages of SQL is relatively easy to write and clear, which is more suitable for modern software development style. But in SQL performance is always lower, from the steps performed by Oracle to analyze the SQL with in is different from SQL without in: Oracle attempts to convert it to multiple tables of the connection, if the conversion is unsuccessful then execute in the subquery, and then query the outer table records, If the conversion succeeds, it is queried directly by the connection mode 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 operation is not recommended for strong columns because it cannot apply the index of the table. Recommended scenario: Replace the 3, is NULL, or is not NULL operation with the not EXISTS scheme (to determine if the field is empty) it is generally not applied to determine whether a field is empty, because the index is null-indexed.
   Is null and is not NULL cannot be indexed with NULL, and any columns that contain null values will not be included in the index. Even if the index
With more than one column, the column is excluded from the index as long as there is a column in the column that contains null. This means that if a column has a null value, even if
Indexing This column also does not improve performance. Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.
Therefore, if you are building an index on a field, the field cannot be empty, and a default value can be used instead of a 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.
   Therefore, if you are building an index on a field, the field cannot be empty, and a default value can be used instead of a null value.

4, > and < operator (greater than or less than operator) is generally not adjusted because it has an index, but it can be indexed to find, but in some cases it could be optimized, such as a table has 1 million records, a numeric field A, 300,000 records of the a= 0, 300,000 records of the A=1,39 Records of the a=2,1 million recorded by 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, the LIKE operator like operator can apply wildcard query, the wildcard combination may reach almost arbitrary query, but if the use is not good will produce performance problems, such as the "%5400%" such as the query does not reference the index, and as ' x5400% ' 　　　　The range index is referenced. 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. The Union operator Union will filter out duplicate records after the 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 the results of two tables at run time, sorts the sorting space to delete duplicate records, and finally returns the result set, which can cause disk 　　　　To sort.　　　　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

SQL Optimization Technical ANALYSIS-1: operator optimization