Shulou(Shulou.com)06/01 Report--

(01) choose the most efficient order of table names (written test)

ORACLE's parser processes table names in the FROM clause in right-to-left order

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

In the case of multiple tables in the FROM clause, you must select the table with the least number of records to be placed at the end

If there are more than 3 table join queries, you need to select the table referenced by the other tables at the end.

For example: query employee's number, name, salary, salary grade, department name

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

1) if the three tables are completely unrelated, write the table with the least records and column names at the end, and so on.

2) if the three tables are related, put the table with the most references at the end, and so on

(02) the connection order in the WHERE clause (written test)

ORACLE parses the WHERE clause in a right-to-left order. According to this principle, joins between tables must be written to the left of other WHERE conditions, and conditions that can filter out the maximum number of records must be written to the right of the WHERE clause.

For 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 > 1500)

(03) avoid using the * sign in the SELECT clause

In the process of parsing, ORACLE converts * to all column names in turn, which is done by querying the data dictionary, which means it will take more time.

Select empno,ename from emp

(04) use the DECODE function to reduce processing time

Use the DECODE function to avoid repeatedly scanning the same records or repeatedly joining the same tables

(05) easy to integrate, unrelated database access

(06) replace DELETE with TRUNCATE

(07) use COMMIT as much as possible

Because COMMIT releases the rollback point.

(08) replace the HAVING clause with the WHERE clause

WHERE executes first, HAVING then executes

(09) use more internal functions to improve SQL efficiency

(10) use the alias of the table

Salgrade s

(11) use aliases for columns

Ename e

(12) use index to improve efficiency

Make good use of indexes in queries

(13) string type, can use the = sign, not like

Because the = sign indicates exact comparison, like represents fuzzy comparison

(14) SQL statements are in uppercase

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

In eclipse, lowercase letters are first written and then converted to uppercase via ctrl+shift+X; ctrl+shift+Y is converted to lowercase

(15) avoid using NOT on index columns

Because when the Oracle server encounters NOT, he will stop his current work and perform a full table scan instead

(16) avoid using calculations on index columns

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

For example, there is an index on the SAL column

Inefficient:

SELECT EMPNO,ENAME FROM EMP WHERE SAL*12 > 24000

Efficient:

SELECT EMPNO,ENAME FROM EMP WHERE SAL > 24000 Universe 12

(17) replace > with > =

Inefficient:

SELECT * FROM EMP WHERE DEPTNO > 3

First locate the record of DEPTNO=3 and scan the first record with DEPT greater than 3

Efficient:

SELECT * FROM EMP WHERE DEPTNO > = 4

Skip directly to the first record where DEPT equals 4

(18) replace OR with IN

Select * from emp where sal = 1500 or sal = 3000 or sal = 800; select * from emp where sal in (1500 3000800)

(19) always use the first column of the index

If the index is built on multiple columns, the optimizer will choose to use the index only if its first column is referenced by the WHERE clause

When referencing only the second column of the index, but not the first column of the index, the optimizer uses a full table scan and ignores the index

Create index emp_sal_job_idex

On emp (sal,job)

-

Select *

From emp

Where job! = 'SALES'

(20) avoid changing the type of index column. Display is more secure than implicit.

When comparing characters with numeric values, ORACLE preferentially converts numeric types to character types

Select 123 | | '123' from dual

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.