Summary of Oracle Database SQL optimization

Some of your own optimization summaries of Oracle SQL are also documented and want to help:

One: use where less to use having;

Second: Check more than two tables, the record less on the right;

Third: Reduce the number of visits to the table;

Four: When there is a subquery, the subquery is placed in the front;

V: try to avoid using the * in the SELECT statement (which converts the * to the column name in turn);

VI: Use a commit as much as possible;

Seven: Decode can avoid repeated scans of the same records or repeated connections to the same table;

Viii. internal functions can also improve SQL efficiency;

IX: When connecting multiple tables, use aliases and prefix the aliases on each field;

Ten: Replace in with exists

11: NOT exists instead of not in (the NOT IN clause will perform an internal sort and merge, in any case, not in is the least efficient, and the entire table in the subquery is scanned. In order to avoid using not in, it can be written as outer joins or not exists);

12: Table connection more efficient than exists;

13: replace distinct with exists

Cases:

Low: High:

Select distinct Dept_no, Dept_name Sele CT Dept_no, Dept_name

From Dept D, EMP E From Dept D

where d.dept_no = E.dept_no; where exists (select 1 from emp e where e.dept_no = D.dept_no);

14: Use the TKPROF tool to query SQL performance status;

The use of indexes to increase efficiency (the price is: The index requires space, and periodically refactoring the index is necessary: ALTER index REBUILD

The principle of the following index is introduced to facilitate the following understanding of index optimization:

Find rowID through the index, and then access the table through ROWID. However, if the query column is included in index, the second operation is not performed because the retrieved data is stored in the index, and access to the index alone can fully meet the query requirements.

Prerequisite Summary: In 16 cases, the lodging column has a unique index; The manager column has a non-unique index.

16: Index range Query (Index range Sacen):

Applies to two situations:

1) based on a range of queries:

SELECT Lodging from lodging WHERE lodging like ' m% '

(WHERE clause condition includes a series of values, Oracle will query LODGING_PK by index range query)

2) based on a non-uniqueness index search:

SELECT Lodging from Lodging WHERE MANAGER = ' LI ';

(This query is two-step: Lodging$manager's index range query gets all the rowid that match the criteria records, and then gets the value of the lodging column through the ROWID Access table.) The index is a non unique index, and the database cannot perform an index-only scan on it.

In the WHERE clause, if the first character Fu Yutung of the value corresponding to the indexed column starts, the index is not taken, and the entire table is scanned, such as SELECT ... WHERE MANAGER like '%li '

17: Selection of the base table:

Underlying table: tables that are first accessed (typically accessed as a whole table scan).

Depending on the optimizer, the selection of the underlying table in the SQL statement is different:

If you use the CBO, the optimizer checks the physical size of each table in the SQL statement, the status of the index, and then chooses the path with the lowest charges.

If you use Rbo, and all join conditions have an index, the underlying table is the last table in the FROM clause

Cases:

SELECT A.name, B.manager from Woker A, lodging B WHERE a.lodging = b.lodging;

Because there is an index on the lodging column and there is no comparison index in the worker table, the worker table is used as the base table for the query.

18: Multiple Equal indexes:

When the execution path of an SQL statement can use multiple indexes that are distributed across multiple tables, Oracle colleagues use multiple indexes and merge their records at run time to retrieve records that are valid only for all indexes.

The Oracle Select execution path is that the unique index level is higher than the non unique index, only if the index columns and constants are more effective in the WHERE clause. This phrase is very low in the optimizer if the index column is compared to the indexed columns of other tables;

If two indexes of the same rank in different tables are referenced, the order of the tables in the FROM clause determines which is used first. The last table index in the FROM clause has high precedence. If two indexes of the same rank in the same table are referenced, the first referenced index in the WHERE clause will have the highest precedence.

Example: There is a DEPTNO index on the Emp_cat, and a non-uniqueness index is also available

SELECT ename from EMP WHERE dept_no = and Emp_cat = ' A ';

The DEPTNO index is retrieved first and then merged with the results retrieved by the Emp_cat index, and the execution path is as follows:

TABLE ACCESS by ROWID on EMP

and _equal

INDEX RANGE SCAN on Dept_idx

INDEX RANGE SCAN on Cat_idx

19: Equation Comparison with range comparison:

The first example:

SELECT ename from EMP WHERE dept_no > and emp_cat = ' A ';

(under two non-uniqueness indexes) The range index is not used at this time, and is compared with the dept_no condition by Emp_cat index query out Records

Note: Uniqueness so when the range is compared, the rank is lower than the non-uniqueness index;

20: Force Index Expiration:

If two or more than two indexes have the same rank, you can force the Oracle optimizer to use one of them. When will this strategy be used? If an index is close to unique, and the other index has many duplicate values, sorting and merging can be a burden, and the latter may be masked to invalidate the index.

(Failure mode: Add the calculation ' +0 ' or ' | | ') to the index column;