Notes for writing Database SQL statements

1. When selecting records from multiple tables: (table name order)

The execution sequence is from right to left, that is, to the right where the number of table records is small, that is, the most basic table. If more than three tables are used for join queries, we use the cross tabulation as the base table (that is, the table applied by other tables, that is, the relational table)

2. WhereThe join order in the clause.

Oracle runs in the order from left to right, that is, the conditions that can filter out the maximum number of records must be written in the rightmost, that is, the end of the WHERE clause.

SQL Server has two statements: first, SQL Server automatically filters out a large amount of data without considering the sequence, and second, execution sequence from left to right.3. Select'*' Must be avoided in the clause '*':

Note that it is time consuming to use.

4. Delete duplicate records:

Avoid repeated records in the table.

Example of the most efficient method for deleting duplicate records in Oracle: delete from EMP e where E. rowid> (select Min (X. rowid) from emp x where X. emp_no = E. emp_no );5 Use commit as much as possible:

Whenever possible, use commit as much as possible in the program to release resources in time and improve performance. Resources released by commit: first, the information used to restore data in the rollback segment. second, the lock obtained by the Program Statement. Third, the space in the redo log buffer is fourth. Oracle manages the internal costs of the above three types of resources.

6 Replace having clause with the WHERE clause:Avoid using the having clause. Having needs to retrieve all records before filtering. This processing requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced.7Use the table alias (alias ):When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each column. in this way, the parsing time can be reduced and the syntax errors caused by column ambiguity can be reduced.8 Replace in with exists and not exists instead of not in:Using exists (or not exists) usually improves the query efficiency. in either case, not in is the most inefficient (because it executes a full table traversal for the table in the subquery ).

Example: (efficient) Select * from EMP (basic table) Where empno> 0 and exists (select 'x' from Dept where Dept. deptno = EMP. deptno and loc = 'melb') (inefficient) Select * from EMP (basic table) Where empno> 0 and deptno in (select deptno from Dept where loc = 'melb ')9 Improve efficiency with indexes:Because indexes require additional storage space and processing, unnecessary indexes will slow the query response time .. Regular index reconstruction is necessary: Alter index <indexname> rebuild <tablespacename>10 Replace distinct with exists:When you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using distinct in the select clause. in general, you can consider replacing it with exist, and exists makes the query more rapid, because the RDBMS core module will return results immediately after the subquery conditions are met. example: (inefficient): Select distinct dept_no, dept_name from Dept D, EMP e where D. dept_no = E. dept_no

(Efficient): Select dept_no, dept_name from Dept d Where exists (select 'x' from EMP e where E. dept_no = D. dept_no );11. SQLStatement in uppercase;

Because Oracle always parses SQL statements first, converts lowercase letters into uppercase letters, and then executes12 Avoid using not in index Columns

We should avoid using not in the index column, not will have the same impact as using the function in the index column. when Oracle Encounters "not", it stops using indexes and performs full table scanning.
13 Avoid using computation on index columns.If the index column is part of the function, the optimizer will use full table scanning without using the index. Example: inefficient: select... From dept where Sal * 12> 25000;

Efficient: select... From dept where SAL> 25000/12;

14 Replaced by> =>Efficient: Select * from EMP where deptno> = 4

Inefficient: The difference between select * from EMP where deptno> 3 is that, the former DBMS will directly jump to the first record whose DEPT is equal to 4, while the latter will first locate the record whose deptno is = 3 and scan forward to the record whose first DEPT is greater than 3.

15 Avoid using is null and is not null in the index column.If the index is null, the index cannot be used. inefficient: (index failure) select... From department where dept_code is not null; efficient: (index valid) select... From department where dept_code> = 0;16 Where clause to be careful:First ,! = The index will be invalid.

Second, 'is the character concatenation function. The index will be invalid.

Third, + is a mathematical function, which will invalidate the index.