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, using the SQL performance of IN is always relatively low, from Oracle's steps to analyze the SQL of IN with the SQL without IN. The following is different: Oracle tries to convert it to multiple tables, if the conversion is unsuccessful Execute the subquery in IN, then query the outer table record, if the conversion is successful, use multiple tables of connection to query. 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: replace <> operator (not equal) with NOT EXISTS or (external connection judgment empty)
Not equal to the operator is never used index, so it will only generate a full table scan. Recommended Scheme: Instead of operational operations of other same functions, such as a> 0 OR a <0 a <> '' change to a> '' is NULL or IS Not NULL operation (if the decision field is empty )
The determination field is empty, which is generally not applied, because the B tree index is no index null. Recommended Scheme: Instead of operating operations with other same 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 first, then use the sort space to delete the repeated record, and finally return the result set, if the amount of the table data can be used Disk is sorted. 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_dfysql writing affects the same function of different performance different writes SQL If a SQL writes for the select * from zl_yhjbqk b programmer, SELECT * from DLYX.ZL_YHJBQK (uppercase table name) ) D programmer writes as SELECT * from DLYX.ZLYHJBQK (there are many 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 query of large data sheets, the query of large data sheets produces direct influence, 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 = '1kv 'The two fields of DY_DJ (voltage level) and XH_bz (Sajunk) in the above two SQL do not index, so the full table scan is performed, and the first SQL DY_DJ =' 1kV below 'criteria is recorded The intensity range is 99%, while the ratio of XH_BZ = 1 is only 0.5%. When the first SQL is performed, 99% comparisons are compared to DY_DJ and XH_BZ, and 0.5% of the second SQL is performed. The records are compared to DY_DJ and XH_BZ, 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 of the conditional fields use the field of function processing that cannot be utilized, such as: Substr (HBS_BH, 1, 4) = ' 5400 ' , Optimization processing: HBS_BH Like '5400%' trunc (SK_RQ) = trunc (sysdate), optimized processing: 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> '' 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 pattern. 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 a prompt for SQL in the following tips: COST (Optimized by Cost Optimization), (Oracle Automatic Selection Cost or Rule) All_ROWS (Return to Return as soon as possible) First_ROWS (Return as soon as possible) Execute method: Use_nl (in combination with NESTED LOOPS) Use_mege (using Merge Join) Uses_hash (using a Hash Join method) Index Tip: Index (Table Index) (Introducing 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. This article is what I start school, some places are based on other participation information, and have not been fully tested, and some places may be incorrect. Please refer to yourself! ! Author Blog: http://blog.9cbs.net/yzsind/
