SQL Optimization method

1. Avoid using "*" in the SELECT clause
When you want to list all columns in the SELECT clause, it is a convenient way to refer to ' * ' Using a dynamic SQL column. Unfortunately, this is a very inefficient approach. In fact, during the parsing process, Oracle translates "*" into all column names, which is done by querying the data dictionary, which means more time is spent.
2. Use the Decode function to reduce processing time use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.
For example:
SQL code SELECT COUNT (*), SUM (SAL) from the EMP WHERE dept_no = 0020 and ename like ' smith% ';
SELECT COUNT (*), SUM (SAL) from the EMP WHERE dept_no = 0030 and ename like ' smith% ';
You can use the Decode function to get the same result efficiently:
SQL code SELECT COUNT (DECODE (dept_no,0020, ' X ', NULL)) D0020_count,
COUNT (DECODE (dept_no,0030, ' X ', NULL)) D0030_count,
SUM (DECODE (dept_no,0020,sal,null)) D0020_sal,
SUM (DECODE (dept_no,0030,sal,null)) D0030_sal from the EMP WHERE ename like ' smith% ';
Similarly, the Decode function can also be used in the group BY and ORDER BY clauses.
Decode meaning Explanation: decode (condition, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)
3. Delete duplicate records
The most efficient way to delete duplicate records (because ROWID is used)
SQL code DELETE from emp E where E.rowid > (SELECT MIN (x.rowid) from emp X where x.emp_no = E.emp_no);
4. Replace Delete with truncate
When you delete a record in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered, and if you do not have a COMMIT transaction, Oracle restores the data to the state before it was deleted (accurately reverting to the condition before the delete command was executed). When using truncate, the rollback segment no longer holds any information that can be recovered. When the command is run, the data cannot be restored. So very few resources are invoked and the execution time is very short.
5. Calculate the number of record strips
In contrast to the general view, COUNT (*) is slightly faster than count (1), and of course, the count of indexed columns is still the fastest if you can retrieve them by index. For example, COUNT (EMPNO)
6. Replace the HAVING clause with a WHERE clause
Avoid having a HAVING clause, and having only after retrieving all the records to filter the result set, this processing needs to sort, total, and so on, if you can limit the number of records through the WHERE clause, it can reduce this overhead.
For example:
SQL code
-Low efficiency
SELECT Region,avg (log_size) from location GROUP by region has region! = ' SYDNEY ' and region! = ' PERTH '--efficient
SELECT Region,avg (log_size) from location WHERE region! = ' SYDNEY ' and region! = ' PERTH ' GROUP by region

7.  replaces in   with exists;
   
in many underlying table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case,  Using EXISTS (or not exists) will usually improve the efficiency of your query.   
SQL code   
-inefficient     
select * from emp where empno > 0 and deptno in  ( select deptno from dept where loc =  ' Melb ')     
--efficient :   select * from emp where empno > 0 and  exists  (select  ' X '   from dept where dept. Deptno = emp. deptno and loc =  ' Melb ')     

8. Replace not in  with not exists 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 a table in a subquery) .   to avoid using not in, we can change it to 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 ');   
SQL code   
-- To improve efficiency rewrite:  
(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 ');   

9. Replace 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
For example:
SQL code
-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);

--exists makes queries faster because the RDBMS core module returns results immediately after the conditions of the subquery are met.

10.  uses indexes to improve efficiency   
    The
Index is a conceptual part of the table used to improve the efficiency of retrieving data, in fact Oracle uses a complex self-balancing b-tree structure, It is usually faster to query the data through an index than a full table scan, and when Oracle finds the best path to execute the query and the UPDATE statement, the  oracle Optimizer uses the index,  to also improve efficiency when it joins multiple tables, and the other advantage of using indexes is that It provides the uniqueness of the primary key (Primary key) validation, except those long or Long raw data types,  You can index almost all the columns .  usually,  using indexes in large tables is particularly effective.   Of course, you will also find that when,  scan a small table, the use of indexes can also improve efficiency, although the use of indexes can improve the efficiency of query, but we must also pay attention to its cost .  indexes need space to store, also need to maintain regularly, Each time a record is added or changed in the table or the index column is modified, the index itself is modified, which means that the insert , delete , update of each record will pay more 4 , 5  Secondary disk i/o,  Because the index requires additional storage space and processing, those unnecessary indexes can slow down the query response time.   NOTE: It is necessary to periodically refactor the index.    

11. Avoid using calculations on indexed columns

In the WHERE clause, if the index column is part of a function, the optimizer uses a full table scan without using the index.
Example:
SQL code
-Low efficiency:
Select "From DEPT WHERE SAL * > 25000;
--Efficient:
Select "From DEPT WHERE SAL > 25000/12;

12. Replacing > with >=
SQL code

--If there is an index on the Deptno
--Efficient: SELECT * from EMP WHERE DEPTNO >=4
--Inefficient: 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.

SQL Optimization method