SQL Optimization Tips (Oracle)

SQL Optimization Tips (1): Connection order in the WHERE clause: Oracle parses the WHERE clause in a bottom-up order, and according to this principle, the connection between tables must be written before other where conditions, and those conditions that can filter out a large number of records must be written at the end of the WHERE clause. For example inefficient: SELECT * from Report_sale_account ewhere hsje>5000and dzxl = ' 000001 ' and 25< (select COUNT (*) from report_sale_a      Ccountwhere code=e.code); efficient: SELECT * from Report_sale_account ewhere 25< (SELECT COUNT (*) from Report_sale_account where Code=e.code) and hsje>5000and DZXL = ' 000001 '; SQL Optimization Tips (2) When deleting a full table, replace Delete with TRUNCATE, noting that truncate can only be applied when the full table is deleted, because truncate is DDL instead of DML. For example, delete a 1 million row of data.  Truncate table Report_sale_account; than delete from Report_sale_account; at least 1000 times times faster. SQL Optimization Tips (3) Use commit as much as possible: whenever possible, use commit as much as possible for each delete, insert, update operation in your program, so that system performance can be greatly improved by the resources freed by commit. SQL Optimization Tips (4) replacing in with exists can improve query efficiency.                     Inefficient select * from Report_sale_account WHERE com_code not in (select CODE from Baseinfo_goods  where DZXL = ' 000001 ') efficient select * from Report_sale_account WHERE not EXISTS (select CODE from Baseinfo_goods WHERE CODE = Report_sale_account.com_code and DZXL = ' 000001 ') optimization tips for SQL (5) Optimize group by to improve the efficiency of the group by statement by adding unwanted records to the Grou P by before filtering out. SQL Optimization Tips (5) For example: inefficient: Select DZXL, AVG (Hsje) from Report_sale_account Group by Dzxl ha              Ving Tsun DZXL = ' 000001 ' or DZXL = ' 000002 '; efficient: Select DZXL, AVG (Hsje) From report_sale_account where dzxl = ' 000001 ' or DZXL = ' 000002 ' GROUP by DZ XL; Avoid having a HAVING clause, and having only after retrieving all the records to filter the result set, this processing needs sorting, statistics and other operations. If you can limit the number of records through the WHERE clause, you can reduce this overhead. SQL Optimization Tips (6) conditional use of Union-all instead of union: This will increase the efficiency by 3 to 5 times times.　　                              SQL Optimization Tips (7) In SQL statements that contain subqueries, pay particular attention to reducing queries for tables such as: Inefficient SELECT SUM (Hsje) from report_sale_account WHERE DZXL = (SELECT DZXL From baseinfo_goods WHERE CODE = ' 0001 ') and PP = (SE Lect PP from baseinfo_goods WHERE CODE = ' 0001 ') highEffect SELECT SUM (Hsje) from Report_sale_account WHERE (DZXL, PP) = (select Dzxl,pp The From baseinfo_goods WHERE CODE = ' 0001 ') update multiple column also applies to the above example. SQL Optimization Tips (8) Avoid using ' * ' in the SELECT clause when you want to list all columns in the SELECT clause, it is a convenient way to use dynamic SQL column references ' * ', unfortunately, this is a very inefficient method, in fact, Oracle in the process of parsing, Converts the ' * ' to all column names, which is done by querying the data dictionary, which means more time is spent. The use of some functions while blindly referencing functions in SQL can degrade performance, if proper use of proper functions will not only enhance SQL readability, but also improve SQL performance, making complex queries easy to implement inefficient practices: Select Name,score, ' excellent '  Grade from score where score>=90union allselect name,score, ' good ' grade from score where score>=80 and score<90union Allselect name,score, ' middle ' grade from score Where score>=60 and score<80union allselect name,score, ' poor ' grade from Scor E Where score<60; Efficient Practice: Select name, score, decode (sign (score-90),-1, Decode (                     Sign (SCORE-80),-1, Decode (sign (score-60),-1, ' poor ', ' Medium '),        ' Liang '),      ' excellent ') as grade from Score;from table; Index reference (1) When the inserted data is more than 10% of the number of records in the data table, you first need to delete the index of the table to improve the efficiency of the data insertion, and when the data is inserted, then the reference to the indexed index (2) avoids the index column Using functions or calculations, in the WHERE clause, if the index is part of a function, the optimizer will no longer use the index for full table scanning. such as: inefficient: SELECT * from Report_sale_account where hsjj*10 >1000; efficient: SELECT * from Report_sale_account where HSJ J >1000/10; Index references (3) try to avoid using not and "! =" and "<>" on the index columns, and the index can only tell what exists in the table, not what does not exist in the table, and when the database encounters not and "! =" and "<>", it stops using the index to perform a full table scan. Index references (4) It is important to note that the index columns are not processed in the retrieval, such as: Trim,to_date, type conversions, destroying indexes, using full table scans, references that affect the SQL Execution Efficiency index (5) Avoid using is null on indexed columns and is not Null to avoid using any nullable columns in the index, Oracle will not be able to use the index for a single-column index, if the column contains null values, the record will not exist in the index, and 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 because the null value does not exist in the index column, so a null comparison of the indexed column in the WHERE clause will cause Oracle to deactivate the reference (6) on the index column ">=" instead of ">" for "Low efficiency": SELECT * FROM Report_sale_account where HSJJ > 10; Efficient: SELECT * from Report_sale_account where Hajj >=10.000000000000001;　　The Compare size function syntax: sign (n) function Description: Take the symbol of the number n, greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0 Example: First, select sign (+), sign (-+), signs (0) from dual; Sign ( -100) sign (0) ———-———-———-　　1-1 02, a=10,b=20 sign (A-B) returns the -1sql optimization method The first method: Connect multiple fields with a connector. Second method: Cancels the duplicate rows. The third method: Use the where statement in a diligent manner. Fourth method: Flexible use of the Count function fifth method: Only the fields that must be queried. The sixth method: the proper handling of the null field. The seventh method: Use a lot of fuzzy query. The eighth method: use like wildcard characters with caution. The Nineth method: using annotations to improve the readability of query statements. Tenth method: When necessary, restrict the rows that the user is using. 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 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 method of deleting duplicate records (because ROWID is used) SQL code delete from emp E WHERE e.rowid > (SELECT MIN (x.rowid) from EMP X wher  E 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 the 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 ( It is accurate to revert to the condition before executing the delete command, and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command is run, the data cannot be restored. So very few resources are invoked and the execution time is very short. 5. Count the number of records 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 retrieved by index. For example, COUNT (EMPNO) 6. Replace the HAVING clause with a WHERE clause to avoid having a HAVING clause that filters the result set only after retrieving all records, which requires operations such as sorting, totals, and so on, if the number of records can be restricted by a WHERE clause, That would reduce this overhead, for example: SQL code-inefficient SELECT region,avg (log_size) from the location GROUP by region has region! = ' SYDNEY ' and Regi  On! = ' PERTH '--efficient SELECT region,avg (log_size) from location WHERE region! = ' SYDNEY ' and region! = ' PERTH ' GROUP by Region 7. Replace in with EXISTS 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.   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. Instead of using the not exists to replace not in the 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. For example: SELECT ... From the EMP where Dept_no not in (SELECT dept_no from DEPT where dept_cat= ' A '); SQL code-in order to improve efficiency rewrite to: (Method one: Efficient) SELECT .... from EMP a,dept B WHERE a.dept_no = b.dept (+) and B.dept_no are 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 when you submit a query that contains one-to-many table information, such as a departmental table and an employee table, avoid using distinct in the SELECT clause.    You can generally consider replacing with exist for example: SQL code-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);  --exists makes queries faster because the RDBMS core module returns results immediately after the conditions of the subquery are met. 10. Use the index to extractA high-efficiency index is a conceptual part of a table used to improve the efficiency of retrieving data, in fact Oracle uses a complex self-balancing b-tree structure that typically queries data faster than full-table scans, when Oracle finds the best path to execute queries and UPDATE statements. The Oracle optimizer uses indexes, as well as using indexes to join multiple tables, and the other advantage of using indexes is that it provides uniqueness validation of primary keys (primary key), and you can index almost any column except those long or long raw data types. In general, using indexes in large tables is particularly effective. Of course, you will also find that using indexes can improve efficiency when scanning small tables, although the use of indexes can improve query efficiency, but we must also pay attention to its cost. The index needs space to store, also need regular maintenance, whenever there are records in the table or the index column is modified, the index itself will be modified, which means that each record of INSERT, DELETE, update will pay more than 4, 5 times the 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 the calculate WHERE clause on an indexed column, if the index column is part of a function, the optimizer will use a full table scan without using an index. Example: SQL code-inefficient: SELECT ...   From DEPT WHERE SAL * > 25000; -Efficient: SELECT ...  From DEPT WHERE SAL > 25000/12; 12. Replace > SQL code with >=-if there is an index on DEPTNO-efficient: SELECT * from EMP WHERE DEPTNO >=4-Inefficient: SELECT * FRO  The difference between the M EMP WHERE DEPTNO >3 is that the former DBMS will jump directly to the record where the first dept equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first dept greater than 3.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL Optimization Tips (Oracle)