Summary of oracle Database SQL Optimization

I have made some optimizations to oraclesql and recorded them myself. I also hope to help you: 1. Use where to use less having; 2. query more than two tables, place fewer records on the right. 3. Reduce the number of table accesses. 4. Place subqueries at the top of where subqueries. 5: avoid using * in the select statement as much as possible *

I have made some optimizations to oracle SQL and recorded them myself. I also hope to help you: 1. Use where to use having less; 2. query more than two tables, place fewer records on the right. 3. Reduce the number of table accesses. 4. Place subqueries at the top of where subqueries. 5: avoid using * in the select statement as much as possible *

I have summarized some oracle SQL optimizations and recorded them myself. I also hope to help you:

1. Use where to use having less;

2. When you query more than two tables, place less records on the right;

3. Reduce the number of table accesses;

4. When a where subquery exists, the subquery is placed at the beginning;

5. Avoid using * in the select statement as much as possible (* is converted to the column name in sequence during execution );

6. Use commit as much as possible;

7. Decode can avoid repeated scan of the same record or join the same table;

8. Internal functions can also improve SQL efficiency;

9: when connecting multiple tables, use the alias and prefix the alias on each field;

10. Replace in with exists

11: not exists instead of not in (not in statements will execute an internal sorting and merging. in any case, not in is the most inefficient, and all the tables in the subquery are scanned. To avoid using not in, you can rewrite it to outer joins or not exists );

12: table join is more efficient than exists;

Thirteen: replace distinct with exists

Example:

Low: high:

Select distinct dept_no, dept_name select 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 the SQL Performance status;

15th: Improve efficiency with indexes (cost: The INDEX requires space and regular INDEX reconstruction is necessary: ALTER INDEX REBUILD

First, we will introduce the indexing principles to facilitate the following understanding of index optimization:

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

Note: In sixteen examples, the LODGING column has a unique index and the MANAGER column has a non-unique index.

Sixteen: index range query (index range sacen ):

Applicable to two situations:

1) Query Based on a range:

Select lodging from lodging where lodging like'm %'

(The where clause condition includes a series of values. oracle will query LODGING_PK through index range query)

2) Non-unique index-based retrieval:

Select lodging from lodging where manager = 'lil ';

(This query is divided into two steps: the LODGING $ MANAGER index range query obtains the rowid of all matching records, and then obtains the value of the LODGING column through the rowid access table. This index is non-unique, and the database cannot perform unique index scans on it)

In the where clause, if the first character of the value corresponding to the index column starts with a wildcard, the index will not be used, but will be scanned across the table, such as SELECT ..... where manager like '% li'

17: basic table selection:

Base table: the first table to be accessed (usually accessed by full table scan ).

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

If CBO is used, the optimizer checks the physical size and index status of each table in the SQL statement, and then selects the path with the lowest charges.

If RBO is used and all the join conditions have an index, in this case, the base table is the table listed at the end of the FROM statement.

Example:

Select a. NAME, B. manager from woker a, lodging B where a. LODGING = B. LODGING;

Because the LODGING column has an index and there is no comparative index in the WORKER table, the WORKER table will be used as the basic query table.

18: Multiple equal indexes:

When the execution path of an SQL statement can use multiple indexes distributed on multiple tables, oracle colleagues use multiple indexes and merge their records at runtime, retrieve records that are only valid for all indexes.

The execution path selected by oracle is that the unique index level is higher than the non-unique index level. It is valid only when the index column and constant in the where clause are compared. If the index column is compared with the index column of other tables, this type of statement is very low in the optimizer;

If two indexes of the same level in different tables are referenced, the sequence of the tables in the FROM clause determines which one is used first. The final table index in the FROM statement has a high priority. If two indexes of the same level in the same table are referenced, the first referenced index in the where clause has the highest priority.

For example, DEPTNO has a non-unique index and EMP_CAT also has a non-unique index.

Select ename from emp where DEPT_NO = 20 AND EMP_CAT = 'a ';

The DEPTNO index is first retrieved and then merged with the results retrieved by the EMP_CAT index. 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

Nineteen: Equality comparison and range comparison:

Example 1:

Select ename from emp where DEPT_NO> 20 AND EMP_CAT = 'a ';

(On the premise of two non-unique indexes) at this time, the range index is not used, and the record is queried through the EMP_CAT index and then compared with the DEPT_NO condition.

Note: When comparing the uniqueness scope, the level is lower than that of the non-uniqueness index;

20: Forced index failure: