Oracle Query Tips

Join order in the 1.WHERE clause: 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. Avoid the use of ' * ' in the 2.SELECT clause: Oracle will turn ' * ' in the process of parsing

1. Join order in the WHERE clause: 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.

2. Avoid the use of ' * ' in the SELECT clause: Oracle, in the parsing process, converts ' * ' to all column names, which is done by querying the data dictionary, which means more time will be spent

3. Use the Decode function to reduce processing time: Use the Decode function to avoid repeated scans of the same record or repeated connections to the same table.

4. Replace Delete with truncate: When you delete records in a table, in general, the rollback segment (ROLLBACK segments) is used to hold information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state before it was deleted (accurately, before the deletion was performed) and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command is run, The data cannot be recovered. Therefore, very few resources are invoked and the execution time is short. (Translator: Truncate only applies when deleting full table, truncate is DDL not DML)

5. Use of commit as much as possible: as much use of a commit in the program as it may be, the performance of the program is improved and the requirements are reduced by the resources released by the commit:

Resources released by commit:

The information used to recover data on the rollback segment.

Locks obtained by program statements

Space in the redo LOG BUFFER

oracle to manage the internal costs of these 3 resources

6. Use the alias of the table (alias): When connecting multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column. This allows you to reduce parsing time and reduce syntax errors caused by column ambiguity.

7. Substituting EXISTS instead of in, using not EXISTS instead of: In many queries based on the underlying table, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) usually increases the efficiency of the query. 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.

Example:

Efficient:

SELECT * from EMP (base table) where EMPNO > 0 and EXISTS (select ' X ' from DEPT where DEPT. DEPTNO = EMP. DEPTNO and LOC = ' Melb ')

Low efficiency:

SELECT * from EMP (base table) where EMPNO > 0 and DEPTNO in (select DEPTNO from DEPT where LOC = ' Melb ')

8. Replace distinct with exists: avoid using DISTINCT in the SELECT clause when submitting a query that contains a one-to-many table of information, such as a department table and an employee table. It is generally possible to consider replacing with exist, EXISTS make the query faster, because the RDBMS core module returns the result immediately after the condition of the subquery is satisfied.

Example:

Low efficiency:

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);

9. SQL statements are capitalized, because Oracle always parses the SQL statements first and converts lowercase letters to uppercase executions;

10. Use where instead of order by:

The order by clause uses indexes only under two strict conditions.

The order in which all columns in order by must be contained in the same index and kept in the index.

All columns in order by must be defined as non-null.

For example:

The table dept contains the following:

Dept_code PK not NULL

Dept_desc not NULL

Dept_type NULL

Inefficient: (index not used)

SELECT Dept_code from DEPT order by Dept_type

Efficient: (using index)

SELECT Dept_code from DEPT WHERE dept_type > 0

11. Optimize GROUP By:

Increase the efficiency of the group BY statement by filtering out unwanted records before group by. The following two queries return the same result but the second one is obviously much faster.

Low efficiency:

SELECT JOB, AVG (SAL)

From EMP

GROUP JOB

Having JOB = ' PRESIDENT '

OR JOB = ' MANAGER '

Efficient:

SELECT JOB, AVG (SAL)

From EMP

WHERE JOB = ' PRESIDENT '

OR JOB = ' MANAGER '

GROUP JOB