High-efficiency Oracle SQL statements

From: http://www.iteye.com/topic/508161

Recently, javaeye found that many comrades know little about SQL optimization. Recently, I have summarized several articles for reference only. However, a few of them may depend on the situation, most of them are quite effective.

[Note: (inefficiency) and (efficiency) are both equivalent .]

1. Connection sequence in the WHERE clause:
Oracle uses the bottom-up sequence to parse the WHERE clause.
According to this principle, the join 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:
(Inefficient)
Select... from Table1 T1 where t1.sal> 300 and t1.jobtype = '000000' and 20 <(select count (*) from Table1 T2 where t2.pno = t1.tno;

(Efficient)
Select... from Table1 T1 where 20 <(select count (*) from Table1 T2 where t2.pno = t1.tno and t1.sal> 300 and t1.jobtype = '20180101 ';

2. Avoid "*" in the select clause:
When you want to list all columns in the select clause, using dynamic SQL column reference '*' is a convenient method.
Unfortunately, this is a very inefficient method.
In fact, Oracle converts '*' into all column names in sequence during parsing. This is done by querying the data dictionary, which means it takes more time.

3. Reduce the number of database accesses:
When each SQL statement is executed, Oracle executes a lot of work internally:
Parse SQL statements, estimate index utilization, bind variables, and read data blocks.
It can be seen that reducing the number of visits to the database can actually reduce the workload of oracle.

Example:
Question-I want to find information about students numbered 0001 and 0002.
(Inefficient)
Select name, age, gender, address from t_student where id = '000000 ';
Select name, age, gender, address from t_student where id = '000000 ';
(Efficient)
Select. name,. age,. gender,. address, B. name, B. age, B. gender, B. address from t_student A, t_student B where. id = '000000' and B. id = '000000 ';

4. Use the decode function to reduce the processing time:
You can use the decode function to avoid repeated scan of the same record or join the same table.

Example:
(Inefficient)
Select count (*), sum (banace) from Table1 where dept_id = '2013' and name like 'anger % ';
Select count (*), sum (banace) from Table1 where dept_id = '2013' and name like 'anger % ';
(Efficient)
Select count (decode (dept_id, '20140901', 'xyz', null) count_01, count (decode (dept_id, '20160901', 'xyz', null) count_02,
Sum (decode (dept_id, '20140901', dept_id, null) sum_01, sum (decode (dept_id, '20160901', dept_id, null) sum_02
From Table1
Where name like 'anger % ';

5. Simple Integration and no associated database access:
If you have several simple database query statements, you can integrate them into a single query (even if there is no relationship between them)

Example:
(Inefficient)
Select name from Table1 where id = '20140901 ';
Select name from Table2 where id = '20140901 ';
Select name from table3 where id = '20140901 ';
(Efficient)
Select t1.name, t2.name, t3.name
From Table1 T1, Table2 T2, table3 T3
Where t1.id (+) = '20180101' and t2.id (+) = '20180101' and t3.id (+) = '20180101'
[Note: although the above example is highly efficient, it has poor readability and the requirement depends on your needs !]

6. Delete duplicate records:
The most efficient method for deleting 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 replacing it with where.
Having filters the result set only after all records are retrieved. This processing requires sorting, total, and other operations.
If the WHERE clause can be used to limit the number of records, this overhead can be reduced.

8. Use the table alias whenever possible:
When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each column.
In this way, the parsing time can be reduced and the syntax errors caused by column ambiguity can be reduced.

9,Replace in with exists.ProgramPersonnel do not know how to use this ):
In many basic table-based queries, to meet one condition, you often need to join another table.
In this case, using exists (or not exists) usually improves the query efficiency.

Example:
(Inefficient)
Select... from Table1 T1 where t1.id> 10 and PNO in (select no from Table2 where name like 'www % ');
(Efficient)
Select... from Table1 T1 where t1.id> 10 and exists (select 1 from Table2 T2 where t1.pno = t2.no and name like 'www % ');

Select 1 is used to determine whether a record exists. If yes, N rows of records are returned. The value is 1, where N is the number of records that meet the conditions.

10. replace not in with not exists:
In a subquery, the not in Clause executes an internal sorting and merging.
In either case, not in is the most inefficient (because it executes a full table traversal for the table in the subquery ).
To avoid using not in, we can rewrite it into an outer join (outer joins) or not exists.

11. Replace distinct with exists:
When you submit a query that contains one-to-many table information, do not use distinct in the select clause. Generally, you can consider replacing it with exists.

Example:
(Inefficient)
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 the query more rapid, because the core module of RDBMS will return results immediately after the subquery conditions are met.

12. Replace exists with table join:
Generally, table join is more efficient than exists.

Example:
(Inefficient)
Select ename from 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 in the index column.
To avoid using any columns that can be empty in the index, Oracle will not be able to use the index.
This record does not exist in the index if the column contains a null value;
For composite indexes, if each column is empty, this record does not exist in the index;
If at least one column is not empty, the record is stored in the index.

Example:
If the unique index is created in column A and column B of the table, and there is a record in the table, the value of A and B is (123, null ),
Oracle will not accept the next record with the same a, B value (123, null) (insert ),
However, if all index columns are empty, Oracle considers that the entire key value is null, but null is not equal to null.
Therefore, you can insert 1000 records with the same key value. Of course, they are empty!
Because the null value does not exist in the index column, the Null Value Comparison of the index column in The WHERE clause will disable oracle.

14. It is best to take a look at the execution plan of complex SQL statements, which helps you analyze and know how efficient your SQL statements are.

The above materials have been accumulated by me for a long time, and many of them are already in the project, especially when there is a large amount of data.