(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