OracleSQL optimization suggestions in actual development, oraclesql Optimization

OracleSQL optimization suggestions in actual development, oraclesql Optimization

1) Avoid Select *

2) try to use the same encoding style when writing SQL statements. Statement parsing, statement execution, and return execution results. Shared SGA zone, saving parsing SQL

3) Replace delete with truncate. Delete is a DML language.

4) COMMIT transactions in a timely manner while ensuring business logic

5) in: subquery-> primary query exists: primary query-> subquery. The number of subqueries is small. Primary queries have large records and indexes using in, and vice versa.

When is not exists more efficient than not in?

6) replace distinct with exists

Inefficient SQL:

Select distinct e. deptno, d. dname from dept d, emp e where d. deptno = e. deptno

Efficient SQL:

Select d. deptno, d. dname from dept d where exists (select * from emp e where e. deptno = d. deptno)

7) replace union all with union

Union-> deduplication, sorting; union all-> simple result join

8) reduces the number of queries to a table while ensuring the function.

Inefficient SQL:

SELECT ename, JOB, sal, deptno FROM emp where job = (select job from emp WHERE ename = 'Scott ')

AND deptno = (SELECT deptno FROM emp WHERE ename = 'Scott ');

Efficient SQL:

SELECT ename, JOB, sal, deptno FROM emp WHERE (JOB, deptno) = (select job, deptno FROM emp WHERE ename = 'Scott ');

9) try to use the table Alias (Alias) and mark the table from before the column

Inefficient SQL:

Select ename, job, sal, EMP. deptno from emp, dept where EMP. deptno = dept. deptno;

Efficient SQL:

Select e. ename, e. job, e. sal, e. deptno from emp e, dept d where e. deptno = d. deptno;

10) do not use the having clause to implement the where clause function.

Grouping SQL statements are used as much as possible to put filtering conditions into the where clause for filtering. having is used to filter grouped results.

11) optimization principles in table join

* Identify the driver table and use the table with the smallest data volume as the driver table

* If you are querying multiple connection conditions, put the conditions that filter the maximum number of records at the beginning (that is, there are multiple conditions after where)

12) use indexes reasonably

* Avoid full table scan: No index, no where condition, no index for the column with the query condition, and a function or arithmetic expression is used for the index column in the query condition.

Ex: where upper (job) = 'saleman '-> use the Function

Where not in ('clark', 'saleman ')-> not in the condition

Where job is not null-> the condition is null, is not null, <> ,! =

Where job like '% SALEMAN %'

* Avoid using the like operator whenever possible

* Avoid using single-row functions or arithmetic expressions for columns in a large table query.

-> Where trunc (birthday, 'Year') = '000000'

Optimized:

Where birthday> = '2017-01-01'

And birthday <= '2017-12-31'

-> Where birthday + 30 = sysdate avoid calculation on the index Column

Optimized :;

Where birthday = sysdate + 30;

-> Where productId = 12345678; implicit data type conversion also invalidates the index.

Optimized:

Where productId = '000000'

* If there is no index on the columns in the order by statement, the performance will be reduced. We recommend that you add an index to the columns that are frequently sorted and avoid using the function expression in these columns.