SQL Optimization 34 article

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):
The ORACLE 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 the use of ' * ' 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 will be 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, the recommended value is 200
(6) Use the Decode function to reduce processing time:
Use the Decode function to avoid repeatedly scanning the same record or repeating 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 duplicate records (because of the ROWID) example:
DELETE from EMP E WHERE e.rowid > (SELECT MIN (X.ROWID)
From EMP X WHERE x.emp_no = e.emp_no);
(9) Replace 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, The data cannot be recovered. Therefore, very few resources are invoked and the execution time is short. (Translator: Truncate only applies when deleting full table, truncate is DDL not DML)
(10) Use commit as much as possible:
Whenever 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 released by commit:
A. The information used to recover data on the rollback segment.
B. Locks obtained by program statements
C. Space in the Redo log buffer
D. Oracle to manage the internal costs of the 3 resources mentioned above
(11) Replace 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. Therefore, 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 the query to the table. Example:
Select Tab_name from TABLES WHERE (tab_name,db_ver) = (select
Tab_name,db_ver from tab_columns WHERE VERSION = 604)
(13) Improve SQL efficiency through internal functions:
Complex SQL often sacrifices execution efficiency. It is very meaningful to master the application of the above function to solve the problem in practical work.
(14) Using table aliases (alias):
When you connect 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) Identify the ' inefficient execution ' of the SQL statement:
While there are many graphical tools for SQL optimization at the moment, writing your own SQL tools to solve problems is always the best approach:
SELECT executions, disk_reads, Buffer_gets,
ROUND ((buffer_gets-disk_reads)/buffer_gets,2) Hit_radio,
ROUND (disk_reads/executions,2) Reads_per_run,
Sql_text
From V$sqlarea
WHERE executions>0
and buffer_gets > 0
and (Buffer_gets-disk_reads)/buffer_gets < 0.8
Order by 4 DESC;
(17) Use Index to improve efficiency:
An index is a conceptual part of a table that is used to improve the efficiency of retrieving data, and Oracle uses a complex, b-tree structure. In general, querying data through an index is faster than full table scans. The Oracle optimizer uses indexes when Oracle finds the best path to execute queries and UPDATE statements. It also increases efficiency when you use indexes to join multiple tables. Another advantage of using the index is that it provides uniqueness validation for the primary key (primary key) ... Those long or long raw data types, you can index almost all columns. In general, using indexes in large tables is particularly effective. Of course, you'll also find that using indexes can also improve efficiency when scanning small tables. Although the use of indexes can improve query efficiency, we must also pay attention to its cost. Indexes require space for storage and regular maintenance, and the index itself is modified whenever a record is added or subtracted from the table or the index column is modified. This means that each record's insert, DELETE, and update will pay 4, 5 more disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow down query response times. It is necessary to periodically refactor the index.:
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>