Oracle SQL Optimization Summary

For SQL optimization, from the actual work experience, summed up as follows:

1, where filter section, the equation left without any calculation and nesting functions, to find an equivalent replacement, on the right side of the equation to make changes;

2, can manually calculate the part, manually calculated to write a fixed value, do not write an expression in the SQL, so that the program every time to recalculate;

3, 3 and above large table association, slow to run for 30 minutes no results, the most straightforward way is to build a temporary table, split multiple table association for multiple 2 table associated with equivalent SQL;

4, for very slow SQL, you can try to regain the source table information, to obtain a new execution plan;

5, in the implementation of logic, for the sorting operation of the keyword (order by/group by/distinct/union/, etc.) to be sensitive to see if the implementation of logic can avoid these sorting operations;

6, to avoid writing functions to achieve their own functions, should use the function of Oracle;

7, not the same use of the left connection (left connection in the implementation of the function, there is a benefit, that is, do not have to tube so much, anyway will not lose data), should stand in the business angle to analyze the data, is not bound to the left connection, after all, the external connection is very

8, for large data, you can take the partition + parallel technology, for partitioning, in the implementation mode, to consider how to automatically add partitions for the table and time long how to automatically drop off the front of the partition;

9, the index is a double-edged sword, when used to test well, and can take into account the latter to manual maintenance, index failure, and other negative issues;

10), forced hint, artificial change Oracle execution plan;

11, multiple analysis execution plan, to judge the pros and cons of SQL, in order to avoid the impact of caching, the different test SQL are executed many times;

12, for the overall grasp of SQL, be sure to know how much the source table data amount, the final return of the results of the assembly, or optimization can not talk about, because all the technology and conclusions are due to the size of the data volume changes;

13, the above are developers in the implementation level of optimization, more fundamental lies in the previous design and structure;

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/