1. The ORACLE parser processes the table names in the FROM clause in Right-to-left order, so the last table in the FROM clause (the underlying table driving tables) is processed first. In the case where multiple tables are included in the FROM clause, you must select the table with the least number of records as the underlying table.
For example:
Table TAB1 16,384 Records
Table TAB2 1 Records
Select TAB2 as the base table (best Practice)
Select COUNT (*) from TAB1,TAB2 execution time 0.96 seconds
Select TAB2 as the base table (Bad method)
Select COUNT (*) from TAB2,TAB1 execution time 26.09 seconds
2. ORACLE parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
For example:
(Inefficient, execution time 156.3 seconds)
SELECT ...
From EMP E
WHERE SAL > 50000
and JOB = ' MANAGER '
< (SELECT COUNT (*) from EMP
WHERE mgr=e.empno);
(Efficient, execution time 10.6 seconds)
SELECT ...
From EMP E
WHERE < (SELECT COUNT (*) from EMP
WHERE mgr=e.empno)
and SAL > 50000
and JOB = ' MANAGER ';
3. Avoid the use of ' * ' in the SELECT clause
When you want to list all columns in the SELECT clause, it is a convenient way to use the dynamic SQL column reference ' * '. Unfortunately, this is a very inefficient approach. In fact, ORACLE converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time will be spent.
4. Use the DECODE function to reduce processing time and use the DECODE function to avoid repeated scans of the same record or repeated connections to the same table.
5. Delete duplicate records
The most efficient way to delete a duplicate record (because ROWID is used)
DELETE from EMP E
WHERE e.rowid > (SELECT MIN (X.ROWID)
From EMP X
WHERE x.emp_no = e.emp_no);
6. Counting the number of record bars
Contrary to general opinion, COUNT (*) is slightly faster than count (1), but if you can retrieve it by index, the
The count of the pins is still the fastest. such as COUNT (EMPNO)
7. Replace the HAVING clause with the WHERE clause
Avoid the HAVING clause, which will filter the result set only after all records have been retrieved.
This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce the overhead.
For example:
Low efficiency
SELECT Region,avg (log_size)
From LOCATION
GROUP by REGION
Having REGION REGION!= ' SYDNEY '
and REGION!= ' PERTH '
Efficient
SELECT Region,avg (log_size)
From LOCATION
WHERE REGION REGION!= ' SYDNEY '
and REGION!= ' PERTH '
GROUP by REGION
The conditions in a having are generally used to compare some aggregate functions, such as COUNT (), and so on. In addition, general conditions should be written in the WHERE clause.
8. Reduce the query to the table
In SQL statements that contain subqueries, you should pay special attention to reducing the query to the table.
For example:
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)
9. Use Table aliases alias alias
When you are connecting multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column. This
Sample, you can reduce the time of parsing and reduce the syntax errors caused by column ambiguity.
(column ambiguity refers to the fact that because different tables in SQL have the same column name, the SQL parser cannot determine the attribution of this column when the column appears in the SQL statement)
10. Replace in with exists
In many base table based queries, a join to another table is often required to satisfy one condition. In this
Cases, using EXISTS (or not EXISTS) usually increases the efficiency of the query.
Low efficiency:
SELECT *
From EMP (base table)
WHERE EMPNO > 0
and DEPTNO in (SELECT DEPTNO
From DEPT
WHERE LOC = ' Melb ')
Efficient:
SELECT *
From EMP (base table)
WHERE EMPNO > 0
and EXISTS (SELECT ' X '
From DEPT
WHERE DEPT. DEPTNO = EMP. DEPTNO
and LOC = ' Melb ')
11. Use NOT exists instead of in
In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least efficient (because it performs a full table traversal of the table in the subquery). In order to avoid using not in, we can rewrite it as an outer join (Outer joins) or not EXISTS.
For example:
SELECT ...
From EMP
WHERE dept_no not in (SELECT dept_no
From DEPT
WHERE dept_cat= ' A ');
In order to improve efficiency. Rewrite as:
(Method one: efficient)
SELECT ....
From EMP a,dept B
WHERE A.dept_no = b.dept (+)
and B.dept_no is NULL
and B.dept_cat (+) = ' A '
(Method two: most efficient)
SELECT ....
From EMP E
WHERE not EXISTS (SELECT ' X '
From DEPT D
WHERE d.dept_no = E.dept_no
and Dept_cat = ' A ');
12. Replace exists with table connection
In general, table joins are more efficient than EXISTS
SELECT ename
From EMP E
WHERE EXISTS (SELECT ' X '
From DEPT
WHERE dept_no = E.dept_no
and Dept_cat = ' A ');
(More efficient)
SELECT ename
From DEPT d,emp E
WHERE e.dept_no = D.dept_no
and Dept_cat = ' A ';
13. Replace distinct with exists
When submitting a query that contains a one-to-many table of information, such as a department table and an employee table, avoid the SELECT clause
Use DISTINCT in. You can generally consider replacing with EXIST
For example:
Low efficiency:
SELECT DISTINCT Dept_no,dept_n
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);
14. Avoid using calculations on indexed columns
In the WHERE clause, if the indexed column is part of the function. The optimizer uses a full table scan without indexing.
Example:
Low efficiency:
SELECT ...
From DEPT
WHERE SAL * > 25000;
Efficient:
SELECT ...
From DEPT
WHERE SAL > 25000/12;
: This is a very practical rule, please be sure to keep in mind
Current 1/2 page
12 Next read the full text
