Efficient Optimization of Oracle SQL statements

High-efficiency optimization of Oracle SQL statements many people seem to know little about SQL optimization. Recently I have summarized several articles for your 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 of www.2cto.com: (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 = '000000 ′; 2. Avoid using "*" in the Select clause: When you want to list all columns in the select clause, it is convenient to use dynamic SQL column reference. 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. Www.2cto.com 3. Reduce the number of visits to the database: when each SQL statement is executed, ORACLE performs a lot of internal work: 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 processing time: 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 = '000000' and name like 'anger % '; select count (*), sum (banace) from table1 where dept_id = '000000' and name like 'anger % '; (efficient) select count (decode (dept_id, '000000', 'xyz', null) count_01, count (decode (dept_id, '000000', 'xyz', null) count_02, sum (decode (dept_id, '000000', dept_id, null) sum_01, sum (decode (dept_id, '000000', dept_id, null) sum_02from table1whe Re 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 query (even if there is no relationship between them) for example: (inefficient) select name from table1 where id = '000000 ′; select name from table2 where id = '000000'; select name from table3 where id = '000000'; (efficient) select t1.name, t2.name, t3.namefrom table1 t1, table2 t2, table3 t3where t1.id (+) = '000000' and t2.id (+) = '000000' and t3.id (+) = '000000' [Note: although the above example is efficient, it has poor readability, the amount depends on your needs!] Www.2cto.com 6. delete duplicate records: the most efficient way to delete duplicate records (because ROWID is used) Example: delete from table1 t1where 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 (many programmers 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 of www.2cto.com: (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 % '); 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 submitting a query that contains one-to-many table information, avoid using distinct in the select clause. in general, we can consider replacing the example with exists: (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 RDBMS core module will immediately return the result once the subquery conditions are met. 12. Replace exists with table join: Generally, table join is more efficient than exists. Example of www.2cto.com: (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. Avoid using any columns that can be empty in the index. ORACLE will not be able to use this 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; www.2cto.com if at least one column is not empty, the record is stored in the index. For example, if the unique index is created in column A and column B of the table, and the and B values of A record exist in the table are (123, null ), ORACLE will not accept the next record with the same A and B values (123, null) (insert). However, if all index columns are empty, ORACLE considers the entire key value to be 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.