High-efficiency Oracle SQL statements

1. The connection order in the WHERE clause:
Oracle parses the WHERE clause in a bottom-up order.
According to this principle, the connection between tables must be written before other where conditions, and those conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

Example:
(Low efficiency)
Select ... from table1 t1 where t1.sal > and t1.jobtype = ' 0001 ' and < (select COUNT (*) from table1 T2 where T2.pno = T1.tno;

Efficient
Select ... from table1 t1 where < (select count (*) from table1 t2 where t2.pno = T1.tno and t1.sal > and T1.J Obtype = ' 0001 ';

2. 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, in the process of parsing, Oracle translates ' * ' into all column names, which is done by querying the data dictionary, which means more time is spent.

3, reduce the number of access to the database:
Oracle performs a lot of work internally when executing each SQL statement:
Parse SQL statements, estimate utilization of indexes, bind variables, read data blocks, and more.
Thus, reducing the number of accesses to the database can actually reduce the workload of Oracle.

Example:
Topic-I'm looking for information for students numbered 0001 and 0002.
(Low efficiency)
Select Name,age,gender,address from t_student where id = ' 0001 ';
Select Name,age,gender,address from t_student where id = ' 0002 ';
Efficient
Select a.name,a.age,a.gender,a.address,b.name,b.age,b.gender,b.address from t_student a,t_student b where a.id = ' 0001 ' and b.id = ' 0002 ';

4. 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.

Example:
(Low efficiency)
Select COUNT (*), SUM (banace) from table1 where dept_id = ' 0001 ' and name like ' anger% ';
Select COUNT (*), SUM (banace) from table1 where dept_id = ' 0002 ' and name like ' anger% ';
Efficient
Select COUNT (Decode (dept_id, ' 0001 ', ' XYZ ', null)) Count_01,count (Decode (dept_id, ' 0002 ', ' xyz ', null)) count_02,
SUM (Decode (dept_id, ' 0001 ', dept_id,null)) Sum_01,sum (Decode (dept_id, ' 0002 ', dept_id,null)) sum_02
From table1
Where name like ' anger% ';

5, integrated simple, no associated database access:
If you have a few simple database query statements, you can integrate them into a single query (even if they are not related)

Example:
(Low efficiency)
Select name from table1 where id = ' 0001 ';
Select name from table2 where id = ' 0001 ';
Select name from table3 where id = ' 0001 ';
Efficient
Select T1.name, T2.name, T3.name
From table1 T1, table2 t2, Table3 T3
where t1.id (+) = ' 0001 ' and t2.id (+) = ' 0001 ' and t3.id (+) = ' 0001 '
"Note: Although the above example is efficient, but the readability is poor, need the amount of love!" 】

6. Delete duplicate records:
The most efficient way to delete duplicate records (because ROWID is used)

Example:
Delete from table1 t1
where T1.rowid > (select min (t2.rowid) from table1 t2 where t1.id = t2.id);

7, try not to use the HAVING clause, you can consider where to replace.
Having the result set is filtered 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 this overhead.

8, try to use the table alias:
When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the aliases on each column.
In this way, you can reduce the parsing time and reduce the syntax errors caused by the column ambiguity.

9, replace in with exists (found many programmers do not know how to use this):
In many base-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 the query.

Example:
(Low efficiency)
Select ... from table1 t1 where t1.id > Pno in (select No from table2 where name like ' www% ');
Efficient
Select ... from table1 t1 where t1.id > exists (select 1 from table2 t2 where t1.pno = t2.no and name like ' www% ‘);

10. Substitute 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 effective (because it performs a full table traversal of the table in the subquery).
To avoid using not, we can change it to an outer join (Outer Joins) or not exists.

11. Replace distinct with exists:
When submitting a query that contains one-to-many table information, avoid using DISTINCT in the SELECT clause. You can generally consider replacing with exists

Example:
(Low efficiency)
Select distinct D.dept_no, d.dept_name from T_dept D, t_emp e where d.dept_no = E.dept_no;
Efficient
Select D.dept_no, d.dept_name from T_dept D where exists (select 1 from t_emp where d.dept_no = E.dept_no);

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

12. Replace the exists with the table connection:
In general, table joins are more efficient than exists.

Example:
(Low efficiency)
Select Ename from the EMP e where exists (select 1 from dept where dept_no = e.dept_no and Dept_cat = ' W ');
SELECT ename
Efficient
Select Ename from Dept D, emp e where e.dept_no = d.dept_no and Dept_cat = ' W ';

13. Avoid using is null and is not NULL on index columns
To avoid using any nullable columns in the index, Oracle will not be able to use the index.
For single-column indexes, this record will not exist in the index if the column contains null values;
For composite indexes, if each column is empty, the same record does not exist in the index;
If at least one column is not empty, the record exists in the index.

Example:
If the uniqueness index is established on column A and column B of the table, and there is a record in the table with a A, a, or a value of (123,null),
Oracle will not accept the next record (insert) with the same A, B value (123,null).
However, if all the index columns are empty, Oracle will assume that the entire key value is empty and null is not equal to NULL.
So you can insert 1000 records with the same key value, of course they are empty!
Because null values do not exist in the index column, a null comparison of indexed columns in the WHERE clause causes Oracle to deactivate the index.

High-efficiency Oracle SQL statements