SQL statement optimization technology analysis
Operator optimization
In operator
The advantages of SQL written in IN are more readily written and clear, which is more suitable for modern software development.
However, the SQL performance with IN is always relatively low, and the steps performed from Oracle are analyzed with the SQL of IN and the SQL without IN.
Oracle attempts to convert it into a plurality of tables, if the conversion is unsuccessful, then execute the subquery in I, and then query the outer layer record, if the conversion is successful, use multiple tables to join the connection method. This shows that at least one conversion process is seen using IN's SQL. The general SQL can be successful, but it cannot be converted for SQL containing packet statistics.
Recommended Scheme: Try not to use the in operator during business-intensive SQL.
NOT IN operator
This operation is a strong column recommended not to use because it cannot apply the index of the table.
Recommended solution: replacement with not exists or (external connection judge is empty)
<> Operator (not equal)
Not equal to the operator is never used index, so it will only generate a full table scan.
Recommended plan: instead of operating operations with other same functions, such as
A <> 0 is changed to a> 0 or a <0
a <> '' change to a> ''
Is NULL or IS NOT NULL operation (decision field is empty)
The determination field is empty, which is generally not applied, because the B tree index is no index null.
Recommended plan:
Instead of using other identical functions, such as
A is not NULL is changed to A> 0 or A> '', etc..
Do not allow fields to be empty, and use a default value instead of null value, if the status field in the industry is not allowed to be empty, the default is the application.
Establish a bitmap index (with the table of partitions cannot be built, the bitmap index is more difficult, such as the field value too many indexes to reduce performance, multi-person update operations increase data block locks)
> And Big than or less than the operator is generally no adjustment, because it has an index to use an index lookup, but in some cases it can optimize it, such as a table has 1 million records, a numeric field A, 300,000 The recorded A = 0, 300 million recorded A = 390,000 recorded A = 3, 10,000 recorded A = 3. Then there is a great difference between A> 2 and A> = 3, because A> 2 oracle will first identify the record index of 2 and then compare, and A> = 3 oracle is directly found directly = 3 record index. LIKE operator The LIKE operator can apply wildcard query, which can be almost arbitrary queries, but if it is not well used, it will generate performance, such as Like '% 5400%' This query will not reference indexes, and Like 'X5400%' will reference the range index. An actual example: Use the marked identification number behind the business number in the yw_yhjbqk table to query the business number YY_BH LIKE '% 5400%' This condition will generate a full-table scan, if it is changed to YY_BH LIKE 'X5400%' or yy_bh like 'B5400% 'The index of YY_BH will be used to carry out two ranges of queries, and performance is definitely greatly improved. UNION operator Union screens the repeated record after the table link, so the resulting set of result sets will be sorted after the table link, and the repeated recording is returned. In fact, most applications do not produce repetitive records, the most common is the process table and historical table UNION. Such as: SELECT * from GC_DFYSUNITION SELECT * from LS_JG_DFYS This SQL takes out the results of the two tables at runtime, and then uses the sort space to delete the repeated record, and finally return the result set, if the amount of the table data can be sorted by the disk. Recommended Scheme: Using the UNION All operator to replace Union, because the Union ALL operation is simply returned after the two results are merged. SELECT * from GC_DFYS Union all SELECT * from LS_JG_DFYS Effect of SQL writing The same function of the same function of different writing SQL Such as a SQL writes in a programmer Select * from zl_yhjbqk B programmers write Select * from dlyx.zl_yhjbqk (prefix with table owner) C programmers write Select * from dlyx.zlyhjbqk (uppercase table name) D programmer writes SELECT * from DLYX.ZLYHJBQK (more spaces in the middle) The above four SQLs are the same as the result of the Oracle Analysis, but the principle of the memory SGA is shared from Oracle, it can be analyzed to each SQL, and occupy the shared memory, if Writing SQL strings and formats Oracle only analyzes once, shared memory will only leave one analysis result, which can reduce the time to analyze SQL, but also reduce shared memory repetition information, Oracle also You can accurately count the execution frequency of SQL. The conditional order of the WHERE WHERE clauses After the order of the large data scale, the query of large data sheets will have a direct impact, 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 below' The two fields of DY_DJ (voltage level) and XH_bz (expense sign) are not indexed in the above two SQLs, so the full table scan is performed, and the first SQL DY_DJ = '1kV below' Conditions in the record set The inner ratio is 99%, while the ratio of XH_BZ = 1 is only 0.5%, and when the first SQL is performed, 99% comparisons are compared to DY_DJ and XH_BZ, and 0.5% records when the second SQL is performed. Both DY_DJ and XH_BZ are performed, and the CPU usage of the second SQL is significantly lower than the first. Influence of query form order The list of SQL executes the performance impact on the SQL in the list, and Oracle will be linked in the order of the table without indexing and oracle without the statistical analysis of the table, thereby causing it because the order of the table is not The data intersection of the server resource. (Note: If the table is statistically analyzed, Oracle will automatically advance the link of the small table, and then make a big table link) SQL statement index utilization Optimization of operators (see next section) Some optimizations for conditional fields Fields that use functions cannot be used indexes, such as: Substr (HBS_BH, 1, 4) = ' 5400 ', optimization processing: HBS_BH LIKE' 5400% ' Trunc (SK_RQ) = Trunc (Sysdate), optimized: SK_RQ> = trunc (sysdate) and SK_RQ SS_DF 20> 50, optimization processing: SS_DF> 30 'X' || HBS_BH> 'X 5400021452 ', optimization processing: HBS_BH>' 5400021542 ' SK_RQ 5 = SYSDATE, optimization processing: SK_RQ = sysdate-5 HBS_BH = 5401002554, optimization processing: HBS_BH = ' 5401002554 ', Note: This condition is implicitted to HBS_BH, because the HBS_BH field is a character type. Include in the condition, you cannot index when the field operations of this table, such as: YS_DF> CX_DF, unable to optimize Qc_bh || kh_bh = ' 5400250000 ', optimization processing: QC_BH =' 5400 'and kh_bh =' 250000 ' Application Oracle's Hint Processing The prompt processing is used in the case where the SQL analysis generated by Oracle is not satisfactory. It can make SQL in the following prompts Target Tips: COST (by cost optimization) Rule (optimized by rules) CHOOSE (Oracle Automatically select Cost or Rule Optimization) All_Rows (all the rows return as soon as possible) First_ROWS (first line of data returned as soon as possible) Execute the prompt: Use_nl (using the NESTED LOOPS mode) USE_MERGE (Union using Merge Join) Use_hash (using a Hash Join method) Index Tip: Index (Table Index) (Introducing the Row of Tips) Other advanced tips (such as parallel processing, etc.) Oracle's prompt function is a relatively strong function, but also a complicated application, and prompts only give Oracle's suggestions, sometimes if it is considered for cost, Oracle may not be punctible. According to practice applications, it is generally not recommended to apply Oracle tips because the performance of each database and server is very likely that a local performance is improved, but another place has fallen, and Oracle has matured in SQL execution analysis. The path of analysis is not analyzed in the database structure (mainly index), server current performance (shared memory, disk file fragment), database object (table, index) statistics. Operator optimization In operator The advantages of SQL written in IN are more readily written and clear, which is more suitable for modern software development. However, the SQL performance with IN is always relatively low, and the steps performed from Oracle are analyzed with the SQL of IN and the SQL without IN. Oracle attempts to convert it into a plurality of tables, if the conversion is unsuccessful, then execute the subquery in I, and then query the outer layer record, if the conversion is successful, use multiple tables to join the connection method. This shows that at least one conversion process is seen using IN's SQL. The general SQL can be successful, but it cannot be converted for SQL containing packet statistics. Recommended Scheme: Try not to use the in operator during business-intensive SQL. NOT IN operator This operation is a strong column recommended not to use because it cannot apply the index of the table. Recommended solution: replacement with not exists or (external connection judge is empty) <> Operator (not equal) Not equal to the operator is never used index, so it will only generate a full table scan. Recommended plan: instead of operating operations with other same functions, such as A <> 0 is changed to a> 0 or a <0 a <> '' change to a> '' Is NULL or IS NOT NULL operation (decision field is empty) The determination field is empty, which is generally not applied, because the B tree index is no index null. Recommended plan: Instead of using other identical functions, such as A is not NULL is changed to A> 0 or A> '', etc.. Do not allow fields to be empty, and use a default value instead of null value, if the status field in the industry is not allowed to be empty, the default is the application. Establish a bitmap index (with the table of partitions cannot be built, the bitmap index is more difficult, such as the field value too many indexes to reduce performance, multi-person update operations increase data block locks) > And Big than or less than the operator is generally no adjustment, because it has an index to use an index lookup, but in some cases it can optimize it, such as a table has 1 million records, a numeric field A, 300,000 The recorded A = 0, 300 million recorded A = 390,000 recorded A = 3, 10,000 recorded A = 3. Then there is a great difference between A> 2 and A> = 3, because A> 2 oracle will first identify the record index of 2 and then compare, and A> = 3 oracle is directly found directly = 3 record index. LIKE operator The LIKE operator can apply wildcard query, which can be almost arbitrary queries, but if it is not well used, it will generate performance, such as Like '% 5400%' This query will not reference indexes, and Like 'X5400%' will reference the range index. An actual example: Use the marked identification number behind the business number in the yw_yhjbqk table to query the business number YY_BH LIKE '% 5400%' This condition will generate a full-table scan, if it is changed to YY_BH LIKE 'X5400%' or yy_bh like 'B5400% 'The index of YY_BH will be used to carry out two ranges of queries, and performance is definitely greatly improved. UNION operator Union screens the repeated record after the table link, so the resulting set of result sets will be sorted after the table link, and the repeated recording is returned. In fact, most applications do not produce repetitive records, the most common is the process table and historical table UNION. Such as: SELECT * from GC_DFYS union SELECT * from LS_JG_DFYS This SQL takes out the results of the two tables at runtime, and then uses the sort space to delete the repeated record, and finally return the result set, if the amount of the table data can be sorted by the disk. Recommended Scheme: Using the UNION All operator to replace Union, because the Union ALL operation is simply returned after the two results are merged. SELECT * from GC_DFYS Union all SELECT * from LS_JG_DFYS Effect of SQL writing The same function of the same function of different writing SQL Such as a SQL writes in a programmer Select * from zl_yhjbqk B programmers write Select * from dlyx.zl_yhjbqk (prefix with table owner) C programmers write Select * from dlyx.zlyhjbqk (uppercase table name) D programmer writes SELECT * from DLYX.ZLYHJBQK (more spaces in the middle) The above four SQLs are the same as the result of the Oracle Analysis, but the principle of the memory SGA is shared from Oracle, it can be analyzed to each SQL, and occupy the shared memory, if Writing SQL strings and formats Oracle only analyzes once, shared memory will only leave one analysis result, which can reduce the time to analyze SQL, but also reduce shared memory repetition information, Oracle also You can accurately count the execution frequency of SQL. The conditional order of the WHERE WHERE clauses After the order of the large data scale, the query of large data sheets will have a direct impact, 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 below' The two fields of DY_DJ (voltage level) and XH_bz (expense sign) are not indexed in the above two SQLs, so the full table scan is performed, and the first SQL DY_DJ = '1kV below' Conditions in the record set The inner ratio is 99%, while the ratio of XH_BZ = 1 is only 0.5%, and when the first SQL is performed, 99% comparisons are compared to DY_DJ and XH_BZ, and 0.5% records when the second SQL is performed. Both DY_DJ and XH_BZ are performed, and the CPU usage of the second SQL is significantly lower than the first. Influence of query form order The list of SQL executes the performance impact on the SQL in the list, and Oracle will be linked in the order of the table without indexing and oracle without the statistical analysis of the table, thereby causing it because the order of the table is not The data intersection of the server resource. (Note: If the table is statistically analyzed, Oracle will automatically advance the link of the small table, and then make a big table link) SQL statement index utilization Optimization of operators (see next section) Some optimizations for conditional fields Fields that use functions cannot be used indexes, such as: Substr (HBS_BH, 1, 4) = ' 5400 ', optimization processing: HBS_BH LIKE' 5400% ' Trunc (SK_RQ) = Trunc (Sysdate), optimized: SK_RQ> = trunc (sysdate) and SK_RQ Fields with explicit or implicit operations cannot be indexed, such as: SS_DF 20> 50, optimization processing: SS_DF> 30 'X' || HBS_BH> 'X 5400021452 ', optimization processing: HBS_BH>' 5400021542 ' SK_RQ 5 = SYSDATE, optimization processing: SK_RQ = sysdate-5 HBS_BH = 5401002554, optimization processing: HBS_BH = ' 5401002554 ', Note: This condition is implicitted to HBS_BH, because the HBS_BH field is a character type. Include in the condition, you cannot index when the field operations of this table, such as: YS_DF> CX_DF, unable to optimize Qc_bh || kh_bh = ' 5400250000 ', optimization processing: QC_BH =' 5400 'and kh_bh =' 250000 ' Application Oracle's Hint Processing The prompt processing is used in the case where the SQL analysis generated by Oracle is not satisfactory. It can make SQL in the following prompts Target Tips: COST (by cost optimization) Rule (optimized by rules) CHOOSE (Oracle Automatically select Cost or Rule Optimization) All_Rows (all the rows return as soon as possible) First_ROWS (first line of data returned as soon as possible) Execute the prompt: Use_nl (using the NESTED LOOPS mode) USE_MERGE (Union using Merge Join) Use_hash (using a Hash Join method) Index Tip: Index (Table Index) (Introducing the Row of Tips) Other advanced tips (such as parallel processing, etc.) Oracle's prompt function is a relatively strong function, but also a complicated application, and prompts only give Oracle's suggestions, sometimes if it is considered for cost, Oracle may not be punctible. According to practice applications, it is generally not recommended to apply Oracle tips because the performance of each database and server is very likely that a local performance is improved, but another place has fallen, and Oracle has matured in SQL execution analysis. The path of analysis is not analyzed in the database structure (mainly index), server current performance (shared memory, disk file fragment), database object (table, index) statistics.
