Oracle SQL statement Optimization

1. The most efficient way to delete duplicate records (because ROWID is used)
Example:
DELETE from emp E where E.rowid > (SELECT MIN (x.rowid) from emp X where x.emp_no = E.emp_no);

2. In the SQL statement that contains the subquery, pay special attention to reducing the query to the table
Example:
Select Tab_name from TABLES where (tab_name,db_ver) = (SELECT tab_name,db_ver from tab_columns where VERSION = 604)

3. Replace I N with EXISTS instead of not EXISTS instead of in:
  on many underlying table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case, use the EXISTS (or NO T EXISTS) will usually improve 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 effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change the   to 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 ')
  (inefficient) select  * from  EMP (base table) where  EMPNO > 0 and  DEPTNO in ( SELECT DEPTNO from  DEPT WHERE LOC = ' Melb ')

4. Replace the distinct with exists:
Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many table information, such as a departmental table and an employee table. You can generally consider replacing with exist, exists make the query more rapid because the RDBMS core module will be full after the condition of the subquery Return the result immediately after the foot.
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 ) ;

The 5.sql statement is capitalized; because Oracle always parses the SQL statements first, the lowercase letters are converted to uppercase and then executed.

6. Use the connector "+" connection string sparingly in Java code! Replace (single thread: stringbuild; Multithreading: StringBuffer)

7. Avoid using calculations on indexed columns.
Where clause, if the index column is part of a function. The optimizer will use a full table scan without using an index.
Example:
Low efficiency:
SELECT ... From DEPT WHERE SAL * > 25000;
Efficient:
SELECT ... From DEPT WHERE SAL > 25000/12;

8. Replacing > with >=
Efficient:
SELECT * from EMP WHERE DEPTNO >=4
Low efficiency:
SELECT * from EMP WHERE DEPTNO >3
The difference between the two is that the former DBMS will jump directly to the first record that DEPT equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first record with a DEPT greater than 3.

9. Replace OR with UNION all (for indexed columns)
In general, replacing OR in a WHERE clause with UNION will have a good effect.  Using OR on an indexed column causes a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query efficiency may be reduced because you did not select OR. In the following example, indexes are built on both loc_id and region.
Efficient:
SELECT loc_id, Loc_desc, region from location WHERE loc_id = 10
UNION
Select loc_id, Loc_desc, region from location WHERE region = "MELBOURNE"
Low efficiency:
Select loc_id, Loc_desc, region from location WHERE loc_id = ten OR region = "MELBOURNE"
If you insist on OR, you need to return the least logged index column to the front

10. Avoid using resource-intensive operations:
SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine to perform the resource-intensive sorting (sort) function. DISTINCT requires a sort operation, while the others need to perform at least two sorting. Typically, SQL statements with UNION, minus, and INTERSECT can be overridden in other ways. If your database is well-sort_area_size, using UNION, minus, INTERSECT can also be considered, after all, they are very readable

11. Avoid using not on indexed columns typically, we want to avoid using not on indexed columns, which does not produce the same effect as using functions on indexed columns. When ORACLE "encounters" not, he stops using the index instead of performing a full-table scan.