SQL Optimization Experience Summary 34

We want to do not only write SQL, but also to do a good performance of the SQL, the following for the author to learn, extract, and summarized part of the information to share with you! (1) Select the most efficient table name order (valid only in the Rule-based optimizer):


ORACLE's parser processes the table names in the FROM clause in Right-to-left order, the last table (driving table), which is written in the FROM clause, is processed first, and in the case where multiple tables are included in the FROM clause, you must select the table with the least number of records as the underlying table. If you have more than 3 table join queries, you need to select the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables.


(2) The order of joins in the WHERE clause. ：


Oracle parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.


(3) Avoid using ' * ' in the SELECT clause:


Oracle converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time is spent


(4) Reduce the number of accesses to the database:


Oracle has done a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, etc.


(5) Reset the arraysize parameters in Sql*plus, Sql*forms, and pro*c to increase the amount of retrieved data per database access, with the recommended value of 200


(6) uses the DECODE function to reduce processing time:


use the Decode function to avoid repeated scans of the same record or repeated connections to the same table.


(7) Simple integration, no associated database access:


If you have a few simple database query statements, you can integrate them into a single query (even if there is no relationship between them)


(8) Delete duplicate records:


The most efficient way to delete a duplicate record (since the ROWID is used) is an example:


DELETE from EMP E WHERE e.rowid > (SELECT MIN (x.rowid)


from EMP X WHERE x.emp_no = e.emp_no);


(9) replaces delete with truncate:


when you delete records in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state before it was deleted (accurately, before the deletion was performed) and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command is run, Data cannot be recovered. Therefore, few resources are invoked and execution time is short. (Translator: Truncate only applies in the deletion of the full table, truncate is DDL is not DML)


(10) Use commit as much as possible:


if possible, use a commit in the program as much as possible, so that the performance of the program is improved and the requirements are reduced by the resources released by the commit:

Resources freed by
commit:


A. The information used to recover data on a rollback segment.


B. Lock
obtained by program statement
Space
in
c. Redo Log Buffer

Oracle to manage the internal costs of these 3 resources


(11) replaces the HAVING clause with the WHERE clause:


Avoid the HAVING clause, which will filter the result set only after all records have been retrieved. This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce the overhead. On, where, and having these three clauses that can be conditionally, on is the first execution, where is the second, having the last, because on is the first to filter the records that do not meet the criteria before statistics, it can reduce the intermediate operations to deal with the data, It is supposed to be the fastest, where it should be faster than having, because it filters the data before the sum is used on the two table joins, so there is a table where there is a comparison with having. In the case of this single table query statistic, if the condition to be filtered does not involve the calculation of fields, then their result is the same, where the Rushmore technique is used, and the having is not, the slower the latter is slow if it involves a calculated field, it means that before the calculation, The value of this field is indeterminate, according to the workflow in the previous article, where the action time is done before the calculation, and the having is only after the calculation, so in this case the results will be different. On a multiple table join query, on has an earlier effect than where. First, the system is based on the join conditions between the tables, a number of tables into a temporary table, and then filtered by the where, and then calculated, after the calculation by having to filter. Thus, to filter the conditions to play a correct role, first of all to understand how this condition should work, and then decide to put it there


(12) Reduce the query to the table:


in SQL statements that contain subqueries, you should pay special attention to reducing queries to tables. Example:


Select Tab_name from TABLES WHERE (tab_name,db_ver) = (select


tab_name,db_ver from tab_columns WHERE VERSION = 604)


(13) improves SQL efficiency through internal functions.:


complex SQL often sacrifices execution efficiency. It is very meaningful to master the method of solving the problem by using the function


(14) using a table alias (alias):


When connecting multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column. This allows you to reduce parsing time and reduce syntax errors caused by column ambiguity.


(15) substituting exists instead of in, using not exists instead of in:


in many queries based on the underlying table, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) usually increases the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least efficient (because it performs a full table traversal of the table in the subquery). In order to avoid using not in, we can rewrite it as an outer join (Outer joins) or not EXISTS.


Example:


(Efficient) SELECT * from EMP (base table) where EMPNO > 0 and EXISTS (select ' X ' from DEPT where DEPT. DEPTNO = EMP. DEPTNO and LOC = ' MELB ')


(Inefficient) SELECT * from EMP (base table) WHERE EMPNO > 0 and DEPTNO in (SELECT DEPTNO from DEPT where LOC = ' MELB ')


(16) identifies the ' inefficient execution ' of the SQL statement:


Although various graphical tools for SQL optimization are emerging, it is always a good idea to write your own SQL tools to solve problems:


SELECT Executions, disk_reads, Buffer_gets,


ROUND (buffer_gets-disk_reads)/buffer_gets,2) Hit_radio,