13 SQL Optimization Tips

1Avoid unplanned full table scans <!--? xml:namespaceprefix = o NS ="Urn:schemas-microsoft-com:office:office"/-->A full table scan is performed as follows:-The table is not indexed-None and restrictions on the rows returned (no WHERE clause)-Unrestricted condition for index primary column (first column of index)-The criteria for the index primary column are contained in an expression-the constraint on the primary column of the index is yes (not) null or the!=-to the index primary column is the like operation and the value is a bind variable or%the value of the beginning2Selectivity using only selective indexed indexes refers to the ratio of the number of records in the index column to those in the flag, and the unique index of the best non-null column is 1.0. Problems with the order of columns in a composite index:1The most frequently used column in the qualification should be the primary column2The most selective column (that is, the clearest column) should be the primary column if 1 and 2 are inconsistent, you can consider establishing multiple indexes. Make selections in composite indexes and multiple individual indexes: Consider the number of times the index is read selectively considering and-equal Operation3Manage multiple table connections (Nested Loops, merge Joins and hash Joins) optimize join operations Merge Joins is a collection operation Nested Loops and Hash Joins is a record operation returns the first batch of records quickly merge J Oins operation for batch operations, huge tables and remote queries 1 full table scan--〉2 Sort--The 〉3 Compare and merge performance overhead is primarily in the case of a full table scan in the first two steps, and the merge joins operation (more effective than nested loops) applies. Improved efficiency by 1: optimization I/O, improve the ability to use Oracle Multi-block read, improve 1 efficiency with options for parallel queries: increase the value of sort_area_size, use the sort Direct writes to provide dedicated tablespace for temporary segments4There are two ways to manage the SQL statement optimizer that contains the view to execute the SQL statement that contains the view:-Execute the view first, complete the result set, and then execute the query with the remaining query criteria as a filter-integrate view text into queries the view that contains the GROUP BY clause cannot be integrated into a large query. Using union in a view does not prevent the view's SQL from being integrated into the query's syntax. 5Refine subqueries6Using composite keys/Star Query7properly index the Connect by Operation8restricting access to remote tables9manage access to very large tables-Manage Data Proximity (proximity) records in a table the longest used column in a range scan of a table stores data in order to help range scanning, especially for large tables. -Avoid index scans that are not helpful when the data collection returned is large, the data block cache used for the SGA uses the index to occupy a large amount, affecting other users; the full table scan can also be obtained from the multi-block read mechanism of Oracle and the "consistent fetch/benefit from each block "feature. -Creating fully indexed tables enables access indexes to read more comprehensive data to establish multiple indexes that differ only from the primary column-Create hash clusters-creating split tables and Views-using parallel optionsTenusing the union all instead of the union all operation does not include the sort unique operation, the first row retrieves a fast response, and in most cases no temporary segment is completed, and the Union all build view can be integrated into the query syntax in the query to improve efficiency  OneAvoid using pl/in SQLSQL function Call Athe use of bind variable (bind Variable) management uses bind Variable and the Execute using mode to Like:name| | ' % ' rewritten into between:name and:name | |Char(225), the index is used instead of a full table scan.  -re-examine the optimization after a return visit to optimize process data changes

13 SQL Optimization Tips