Oracle sql 语句剖析（国外英文资料）.doc

Oracle sql 语句分析（国外英文资料） Oracle SQL statement analysis Author: anonymous article source: EBOOK-CN.COM hits: 8 update time: 2005-12-4 The operator to optimize The advantage of SQL written IN IN is easy to write and easy to understand, which is suitable for modern software development. However, the SQL performance IN IN is always low, and the steps that are performed by ORACLE to analyze the SQL from IN IN are the following differences: ORACLE is trying to convert them to multiple tables connection, if the conversion is not successful, an executive IN the inside of the subquery, then queries the outer table records, if the conversion way success is directly connected by multiple tables query. Thus, there is at least one conversion process IN SQL IN SQL. The average SQL can be converted to success, but it cant be converted for SQL that contains grouping statistics. Recommendation: try not to use the IN operator IN the business intensive SQL. The NOT IN operator This operation is recommended by strong columns because it cannot be indexed by the table. Recommended solution: replace it with NOT EXISTS or (outer join + judgement) operator (not equal to) Not equal to the operator will never be indexed, so the processing of it will only produce full table scans. Recommendation: replace the operation operation with the same function A 0 is a 0 or a 0 A to a IS NULL or IS NOT NULL operation (if the field IS empty) Whether or not the field is empty is not an index, because the B tree index is not indexed. Recommendation: Replace the operation with other functions, such as A is not null to a or a . The field is not allowed to be empty, and a default value instead of an empty value, such as the status field in the industry extension application, is not allowed to be empty, and the default is an application. Create bitmap index (partition table can not be built, the bitmap index difficult to control, such as field values too many indexes can make the performance degradat