SQL trainning Summary

<title>SQL trainning Summary</title> 1,oracle uses indexes to traverse tables faster, and if SQL is unreasonable, it causes the optimizer to delete the index and use a full table scan, which is poor SQL.          1. Do not want Oracle to do too much, 1) avoid complex multi-table associations, 2) avoid using *; 3) Avoid using useful resources: DISTINCT, UNION, minus, Interset, ORDER by, etc. Replace DISTINCT with exists using UNION ALL instead of union (if necessary)
2. Give the optimizer a clear command; Automatically select the index to include at least the first column of the combined index avoid using a function on the index avoid using a pre-wildcard character to avoid using not on index columns to avoid using is null and is not NULL on the index Avoid the automatic conversion of indexed columns when querying with as few format conversions as possible 3. Reduce the number of visits; Use decode to reduce processing time reduce query on tables   Low-efficiency SELECT tab_name
From TABLES
WHERE tab_name = (SELECT tab_name
From Tab_columns
WHERE VERSION = 604)
and db_ver= (SELECT db_ver
From Tab_columns
WHERE VERSION = 604)
Efficient
SELECT Tab_name
From TABLES
WHERE (Tab_name,db_ver)
= (SELECT tab_name,db_ver)
From Tab_columns
WHERE VERSION = 604)  4. Details of the impact. Wehre sentence should be filtered, return the result set of small in the end, where the sentence using reasonable functions and expressions ORDER by using indexed columns, not to be established, the junction column does not use the index wildcard like statement, should not appear in the word first WHE RE instead of having a exist〉not in efficiency, do not use index >= instead of > Example:

SELECT * FROM employee where salary <> 3000;

for

This query, it can be rewritten to not use not:

select * FROM employee where salary<3000 or salary>3000;

Use of external links:

The

outer join "+" is left and right joined by the left or right side of the "=". If a row in a table without the "+" operator does not directly match any row in the table with the "+" budget, the former row matches a blank line in the latter and is returned. The external join "+" can be used to replace the inefficient not-in operation, which greatly improves the running speed. For example, the following command is slow to execute:

Select A.empno from emp a where a.empno

not in

(select empno from emp1 where job= ' SALE ');

with an outer join, rewrite the command as follows:

Select A.empno from emp A, EMP1 b

where A.empno=b.empno (+)

and

b.empno is null

and

b.job= ' SALE ';

This allows for a significant increase in operating speed.

Use commit more, replace delete COUNT (*) > Count (1) with truncate



From for notes (Wiz)

SQL trainning Summary