Oracle Series: (+) Oracle SQL statement optimization

(01) Select the most efficient table name sequence (written test)

The parser for Oracle processes the table names in the FROM clause in a right-to-left order.

The last table written in the FROM clause will be processed first,

In cases where the FROM clause contains more than one table, you must select the table with the fewest number of record bars to put in the last,

If you have more than 3 tables connected to the query, you need to select the table that is referenced by the other table to be last.

Example: Query employee's number, name, salary, salary level, department name

Select Emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname from Salgrade,dept,emp where (Emp.deptno = DEPT.DEP TNO) and (Emp.sal between Salgrade.losal and Salgrade.hisal)

1) If three tables are completely non-relational, write the table with the fewest records and column names , and

2) If three tables are related, the table that will be referenced most , put in the last, and so on

(n) The connection order in the WHERE clause (written test)

Oracle parses the WHERE clause from right to left, and according to this principle, the connection between tables must be written to the left of other where conditions , and those conditions that can filter out the maximum number of records must be written to the right of the WHERE clause .

Example: Query employee's number, name, salary, department name

Select Emp.empno,emp.ename,emp.sal,dept.dname from emp,dept where (Emp.deptno = Dept.deptno) and (Emp.sal > 1 500)

Avoid using the * number in the SELECT clause

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

Select Empno,ename from EMP;

(04) 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

(05) Integrated simple, no associated database access

(06) Replace Delete with truncate

(07) Use Commit as much as possible

Because commit will release the rollback point

Replace a having clause with a WHERE clause

Where executes first, having after having

(09) More use of intrinsic functions to improve SQL efficiency

(10) Use the alias of the table

Salgrade s

(11) Use Aliases for columns

Ename E

improve efficiency with indexes

In the query, use the index

string type, can use = number, do not like

Because the = number represents an exact comparison, like means a fuzzy comparison

SQL statements are capitalized

Because the Oracle server always turns lowercase letters to uppercase before executing

in eclipse, lowercase letters are first written and then capitalized by ctrl+shift+x; ctrl+shift+y to lowercase

(15) Avoid using not on indexed columns

Because the Oracle server encounters not, he stops his current work and instead performs a full-table scan

(16) Avoid using calculations on indexed columns

In the WHERE clause, if the index column is part of a function, the optimizer will use a full table scan without using the index, which will become slower

For example, there is an index on the SAL column,

Low efficiency:

SELECT empno,ename from EMP WHERE sal*12 > 24000;

Efficient:

SELECT empno,ename from EMP WHERE SAL > 24000/12;

(17) Replace > with >=

Low efficiency:

SELECT * from EMP WHERE DEPTNO > 3

First locate the Deptno=3 record and scan to the first dept greater than 3

Efficient:

SELECT * from EMP WHERE DEPTNO >= 4

Jump directly to the first record that dept equals 4

(18) Replace or with in

SELECT * from emp where Sal = $ or Sal = N or sal = 800;

SELECT * from emp where Sal in (1500,3000,800);

(19) Always use the first column of an index

If the index is built on more than one column, the optimizer chooses to use the index only if its first column is referenced by a WHERE clause

When referencing the second column of an index only, the optimizer uses a full table scan and ignores the index when the first column of the index is not referenced

CREATE INDEX Emp_sal_job_idex

On EMP (sal,job);

----------------------------------

SELECT *

From EMP

where job! = ' SALES ';

(20) Avoid changing the type of indexed columns, showing more secure than implicit

Oracle takes precedence over numeric types to character types when comparing characters to numbers

Select 123 | | ' 123 ' from dual;

Oracle Series: (+) Oracle SQL statement optimization