SQL Performance Optimization

We have to do not only write SQL, but also to write a good performance of SQL, the following is the author of learning, excerpts, and summary of the department
Share the information with you!

2. Select the most efficient table name order (valid only in the rule-based optimizer)

   The parser for ORACLE processes the table name from the FROM clause in a right-to-left order, written in the last table in the FROM clause
   (driving table) will be processed first, and in the case where the FROM clause contains more than one table, you must choose to remember
   Table with the lowest number of records as the base table. If you have more than 3 tables connected to the query, you need to select a crosstab (intersec
   tion table), which is the table that is referenced by the other tables, is used as the underlying tables.

4. Connection order in the WHERE clause

   ORACLE uses a bottom-up sequence to parse where clauses, and according to this principle, the connections between tables must be written in other whe
   Before the RE condition, those conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

6. Avoid using ' * ' in the SELECT clause

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

8. 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 data blocks
   such as

10. Reset the ArraySize parameter in Sql*plus, Sql*forms, and pro*c to increase each
    The amount of retrieved data accessed by the database, with a recommended value of 200

12. Use the DECODE function to reduce processing time

    Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table

14. Integrated simple, non-associative database access

    If you have a few simple database query statements, you can integrate them into a single query (even if there are no
    Relationship

16. Delete duplicate records

    The most efficient method of deleting duplicate records (because of the use of rowID) Example:
    DELETE from EMP E WHERE e.rowid > (SELECT MIN (X.ROWID)
    From EMP X WHERE x.emp_no = e.emp_no);

18. Replace Delete with truncate

    When you delete a record in a table, in general, the rollback segment (rollback segments) is used to store can be restored
    The information. If you do not have a COMMIT transaction, ORACLE restores the data to the state it was before it was deleted (to be precise, recovery
    Until the delete command is executed) and when Truncate is applied, the rollback segment no longer holds any information that can be recovered.
    When the command is run, the data cannot be restored. So very few resources are invoked and the execution time is very short. (Translator by: TRU
    NCATE only applies if the whole table is removed, TRUNCATE is DDL is not DML)

20. Use commit as much as possible

    Whenever possible, use commit as much as you can in your program, so that the performance of the program is improved, and the need for a commit
    The resources released, and the reduced
    Resources Freed by COMMIT:

    • The information that is used to recover data on the rollback segment.
    • Locks obtained by program statements
    • Redo space in the log buffer
    • ORACLE manages internal spending in 3 of these resources

22. Replace a HAVING clause with a WHERE clause

    Avoid having a HAVING clause that filters 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 this overhead. (Non-Oracle) on, where, have the three clauses that can be added conditionally, on is the first execution, where the second, having the last, because on is the non-qualifying records filtered before the statistics, it can reduce the intermediate operation to process the data, It should be said that the speed is the fastest, where should also be faster than having to, because it filters the data before the sum, in two table joins only use on, so in a table, the left where and have compared. In the case of this single-table query statistics, if the conditions to be filtered do not involve the fields to be calculated, then they will be the same result, but where you can use the Rushmore technology, and have not, at the speed of the latter slow if you want to relate to the calculated field, it means that before the calculation, The value of this field is indeterminate, according to the workflow of the previous write, where the action time is done before the calculation, and having is calculated after the function, so in this case, the results will be different. On a multi-table join query, on has an earlier effect than where. The system first synthesizes a temporary table based on the conditions of the joins between the tables, then the where is filtered, then calculated, and then filtered by having. Thus, to filter the conditions to play the right role, first of all to understand when this condition should play a role, and then decided to put it there

24. Reduce the query on the table

    In the SQL statement that contains the subquery, pay particular attention to reducing the query on the table. Example:
    Select Tab_name from TABLES WHERE (tab_name,db_ver) = (select
    Tab_name,db_ver from tab_columns WHERE VERSION = 604)

26. Improve SQL efficiency with intrinsic functions

    Complex SQL often sacrifices execution efficiency. The ability to master the above application function to solve the problem in the actual work
    It's very meaningful.

28. Use Alias for table

    When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the aliases on each column. In this way,
    You can reduce the time to parse and reduce the syntax errors caused by column ambiguity

30. Replace in with EXISTS instead of not EXISTS instead of in

    In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case
    , using EXISTS (or not EXISTS) will usually improve the efficiency of the query. In a subquery, the NOT IN clause executes
    An internal sort and merge. In either case, not in is the least effective (because it is a subquery
    The table performs a full table traversal). To avoid using not, we can change it to an outer join (Outer Join
    s) 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 ')
    (Low efficiency) SELECT * from EMP (base table) WHERE EMPNO > 0 and DEPTNO in (SELECT DEP
    TNO from DEPT WHERE LOC = ' Melb ')

32. Identify SQL statements for ' inefficient execution '

    Although there are many graphical tools for SQL optimization, it is a good idea to write your own SQL tools to solve the problem
    :
    SELECT EX Ecutions, 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;

34. Increasing efficiency with indexes the

    Index is a conceptual part of the table used to improve the efficiency of retrieving data, and ORACLE uses a complex self-balancing B-TR
    EE structure. Typically, querying data by index is faster than full-table scans. When the Ora CLE the ORACLE Optimizer uses the index when it finds the best path to execute the query and the UPDATE statement
    . Also, using indexes when joining multiple tables can improve efficiency.
    Another advantage of using an index is that it provides the uniqueness of the primary key (primary key) Validation: Those long or long
    RAW data types, you can index almost all of the columns. In general, using indexes in large tables is particularly effective. Of course,
    you will also find that using indexes can improve efficiency when scanning small tables. Although the use of indexes can be used to get the query efficiency of the
    high, but I They also have to pay attention to its cost. Indexes require space to store, and they need to be maintained regularly, and the index itself is modified whenever a record
    is added to the table or the index column is modified. This means that for each record, insert, DEL
    ETE, UPDATE will pay 4, 5 disk I/O. Because the index Additional storage space and processing are required, and
    those unnecessary indexes will slow down the query response time. It is necessary to periodically refactor the index.:
    ALTER index REBUILD

36. Replace distinct with exists

    Avoid using D in a SELECT clause when submitting a query that contains one-to-many table information, such as a departmental table and an employee table
    Istinct. You can generally consider replacing with exist, EXISTS makes the query faster because the RDBMS core module will be
    Once the condition of the subquery is met, the result is returned immediately. Example:
    (inefficient):
    SELECT DISTINCT dept_no,dept_name from DEPT D, EMP E
    WHERE d.dept_no = E.dept_no
    (efficient):
    Select Dept_no,dept_name from DEPT D WHERE EXISTS (select ' X '
    From EMP E WHERE e.dept_no = d.dept_no);

38. SQL statements are capitalized, because Oracle always parses the SQL statements first, converting lowercase letters to uppercase and then executing

20. Use the connector "+" connection string sparingly in Java code!
21. Avoid using not on indexed columns

Usually we want to avoid using not on indexed columns, and not to have the same effect as using functions on indexed columns. When Oracle "encounters" not, he stops using the index instead of performing a full-table scan.

1. Avoid using calculations on indexed columns

   Where clause, if the index column is part of a function. The optimizer will use a full table scan without using an index.
   Example:
   Low efficiency:
   SELECT ... From DEPT WHERE SAL * > 25000;
   Efficient:
   SELECT ... From DEPT WHERE SAL > 25000/12;

2. replacing > with >=

   Efficient:
   SELECT * from EMP WHERE DEPTNO >=4
   Low efficiency:
   SELECT * from EMP WHERE DEPTNO >3
   The difference between the two is that the former DBMS will jump directly to the first record that dept equals 4 and the latter will be positioned first to dept
   No=3 record and scan forward to the first dept greater than 3 record.

24. Replace or with union (for indexed columns)

In general, replacing or in a WHERE clause with Union will have a good effect. Using or will be used for indexed columns
Scan the table. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query
Rate may be lowered because you have not chosen or. In the following example, indexes are built on both loc_id and region.
Efficient:
SELECT loc_id, Loc_desc, Region
From location
WHERE loc_id = 10
UNION
SELECT loc_id, Loc_desc, Region
From location
WHERE region = "MELBOURNE"
Low efficiency:
SELECT loc_id, Loc_desc, Region
From location
WHERE loc_id = ten OR region = "MELBOURNE"
If you persist in using or, you need to return the least logged index column to the front.

2. Replace or with in.

   This is a simple and easy to remember rule, but the actual implementation of the effect must be tested, under the oracle8i, both the implementation of the road
   The path seems to be the same.
   Low efficiency:
   SELECT .... From location WHERE loc_id = ten or loc_id = or loc_id = 30
   Efficient
   SELECT ... From location WHERE loc_in in (10,20,30);

4. Avoid using is null and is not NULL on indexed columns

   To avoid using any nullable columns in the index, ORACLE will not be able to use the index. For single-column indexes, if the columns
   Contains a null value, the record will not exist in the index. For composite indexes, if each column is empty, the same does not exist in the index
   This record. If at least one column is not empty, the record exists in the index. Example: If the uniqueness index is based on the
   Table in columns A and B, and the table has a record of a A, a, or a value of (123,null), ORACLE will not accept the next
   Record (insert) with the same A, B value (123,null). However, if all of the index columns are empty, ORACLE will
   It is assumed that the entire key value is empty and null is not equal to NULL. So you can insert 1000 records with the same key values, and of course they
   It's all empty! Because null values do not exist in the index column, a null comparison of the indexed columns in the WHERE clause causes the Oracle
   Deactivate the index.
   Inefficient: (Index invalidation)
   SELECT ... From DEPARTMENT WHERE dept_code are not NULL;
   Efficient: (Index valid)
   SELECT ... From DEPARTMENT WHERE Dept_code >=0;

6. Always use the first column of an index

   If the index is built on more than one column, only when its first column (leading column) is referenced by a WHERE clause
   The optimizer will not choose to use this index. This is also a simple and important rule, when referencing only the second column of an index,
   The optimizer uses a full table scan and ignores the index

8. Replace union with Union-all (if possible)

   When the SQL statement requires a union of two query result sets, the two result sets are union-all in
   and then Sort before outputting the final result. If you use UNION ALL instead of union, the order is not necessary to
   . The efficiency will be improved. It is important to note that the Union all will repeat the same record in the two result set
   . So you still have to analyze the feasibility of using union all from business requirements UNION will sort the result set,
   This operation will use the memory of sort_area_size. This memory optimization is also very important. The following
   SQL can be used to query the consumption of sorting
   Inefficient:
   SELECT ACCT _num, Balance_amt
   from debit_transactions
   WHERE tran_date = ' 31-dec-95 '
   UNION
   SELECT acct_num, BALANCE _amt
   from debit_transactions
   WHERE tran_date = ' 31-dec-95 '
   Efficient:
   SELECT acct_num, Balance_amt
   from Debit_transactions
   WHERE tran_date = ' 31-dec-95 '
   UNION all
   SELECT acct_num, Balance_amt
   from Debit_tra Nsactions
   WHERE tran_date = ' 31-dec-95 '

9. The

10. uses the where instead of the order by

    ORDER BY clause to use the index only under two strict conditions. All columns in the
    order by must be contained in the same index and in the order in which they are arranged in the index. All columns in the
    order by must be defined as non-empty. The index used in the
    WHERE clause and the index used in the ORDER BY clause cannot be tied.
    For example:
    Table DEPT contains the following:
    Dept_code PK NOT NULL
    Dept_desc NOT NULL
    Dept_type NULL
    Inefficient: (index not used)
    S Elect Dept_code from DEPT ORDER by Dept_type
    Efficient: (using index)
    SELECT dept_code from DEPT WHERE dept_type > 0

    /blockquote>

12. Avoid changing the type of indexed columns

    ORACLE automatically makes simple type conversions to columns when comparing data of different data types.
    Suppose Empno is an indexed column of a numeric type.
    SELECT ... From EMP WHERE EMPNO = ' 123 '
    In fact, after the Oracle type conversion, the statement translates to:
    SELECT ... From EMP WHERE EMPNO = to_number (' 123 ')
    Fortunately, the type conversion did not occur on the index column, and the purpose of the index was not changed.
    Now, suppose Emp_type is an indexed column of a character type.
    SELECT ... From EMP WHERE Emp_type = 123
    This statement is translated by Oracle to:
    SELECT ... From EMP Whereto_number (emp_type) =123
    This index will not be used because of the type conversions that occur internally! To prevent Oracle from implicitly making your SQL
    Type conversion, it is best to explicitly display the type conversion. Note that when comparing characters to numbers, ORACLE takes precedence
    converting numeric types to character types

14. A WHERE clause that needs to be careful

    The WHERE clause in some SELECT statements does not use an index. Here are some examples.
    In the following example, (1) '! = ' will not use the index. Remember, the index can only tell you what exists in the table, not
    Can tell you what doesn't exist in the table. (2) ' | | ' is a character join function. As with other functions, the index is deactivated.
    (3) ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated. (4) The same index columns cannot each other
    Comparison, this will enable full table scanning.

32.a. If the number of records in a table that has more than 30% data is retrieved. Using indexes will not be significantly more efficient. B. In certain cases, using an index may be slower than a full table scan, but this is the same order of magnitude difference. In general, the use of indexes than the full table scan to block several times or even thousands of times!
33. Avoid using resource-intensive operations

SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine to perform the resource-intensive sorting (sort) function. DISTINCT requires a sort operation, while the others need to perform at least two sorting. Typically, SQL statements with union, minus, and INTERSECT can be overridden in other ways. If your database is well-sort_area_size, using union, minus, INTERSECT can also be considered, after all, they are very readable

1. Optimize GROUP BY
   

Increase the efficiency of the group BY statement by filtering out unwanted records before group by. The following two queries return the same result but the second one is significantly faster.
Low efficiency:
SELECT JOB, AVG (SAL)
From EMP
GROUP by JOB
Having JOB = ' president '
OR JOB = ' MANAGER '
Efficient:
SELECT JOB, AVG (SAL)
From EMP
WHERE JOB = ' President '
OR JOB = ' MANAGER '
GROUP by JOB

SQL Performance Optimization