SQL optimization skills (Oracle), SQL optimization skills oracle

SQL optimization skills (Oracle), SQL optimization skills oracle

SQL optimization tips (1): Connection sequence in the Where clause: oracle parses the where clause in order from bottom to top. According to this principle, the connection between tables must be written before other where conditions, 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 = '000000' and 25 <(select count (*) from report_sale_accountwhere 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 = '000000'; SQL optimization skills (2) Replace delete with truncate When deleting a full table. Note that truncate can only be used when deleting a full table, because truncate is ddl rather than dml. For example, you can delete a row of 1 million data. Truncate table report_sale_account; at least 1000 times faster than delete from report_sale_account. SQL optimization techniques (3) Use commit as much as possible: as long as possible, use commit as much as possible for each delete, insert, and update operation in the program, in this way, the system performance will be greatly improved because of the resources released by commit. SQL optimization technique (4) replacing in with exists can improve the query efficiency. Inefficient SELECT * FROM REPORT_SALE_ACCOUNT WHERE COM_CODE not in (select code from BASEINFO_GOODS where dzxl = '20140901 ') efficient SELECT * FROM REPORT_SALE_ACCOUNT where not exists (select code from BASEINFO_GOODS where code = REPORT_SALE_ACCOUNT.COM_CODE and dzxl = '000000') SQL optimization techniques (5) optimize group by to improve the efficiency of group by statements. You can filter out unnecessary records before group. SQL optimization skills (5) Example: inefficient: select dzxl, avg (hsje) from report_sale_account group by dzxl having dzxl = '000000' or dzxl = '000000'; efficient: select dzxl, avg (hsje) from report_sale_account where dzxl = '000000' or dzxl = '000000' group by dzxl; avoid HAVING clause, HAVING filters the result set only after all records are retrieved. This processing requires sorting, statistics, and other operations. If the WHERE clause can be used to limit the number of records, this overhead can be reduced. SQL optimization tips (6) conditional use of union-all to replace union: this will increase the efficiency by 3 to 5 times. SQL optimization techniques (7) in SQL statements containing subqueries, pay special attention to reducing the number of queries to the table, for example, inefficient SELECT SUM (HSJE) FROM REPORT_SALE_ACCOUNT where dzxl = (select dzxl from BASEINFO_GOODS where code = '000000') and pp = (select pp from BASEINFO_GOODS where code = '000000') Efficient select sum (HSJE) FROM REPORT_SALE_ACCOUNT WHERE (DZXL, PP) = (select dzxl, pp from BASEINFO_GOODS where code = '000000') Update multiple columns also applies to the preceding example. SQL optimization tips (8) Avoid using '*' 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, during ORACLE parsing, convert '*' to all column names in sequence. This task is completed by querying the data dictionary, which means it takes more time. Although blind reference to functions in SQL can reduce the performance of some functions, correct use of suitable functions not only improves the readability of SQL statements, but also improves the SQL Performance, make complex queries easy to achieve inefficient practices: Select name, score, 'you' score level From score Where score> = 90 UNION ALLSelect name, score, 'liang' score level From score Where score> = 80 and score <90 UNION ALLSelect name, score, 'Medium 'score level From score Where score> = 60 and score <80 UNION ALLSelect name, score, 'bad' score level From score Where score <60; efficient practice: select name, score, decode (sign (score-90) ,-1, decode (sign (score-80),-1, decode (sign (score-60),-1, 'bad', 'zhong '), 'loan'), 'out') as score level from score; from table; index reference (1) when the inserted data is more than 10% of the number of records in the data table, first, you need to delete the index of the table to improve the efficiency of data insertion. After the data is inserted, you need to create an index reference (2) Avoid using a function or computing in the index column, in the where clause, if the index is a part of the function, the optimizer will not use the index and use full table scan. For example: inefficient: select * from report_sale_account where hsjj * 10> 1000; efficient: select * from report_sale_account where hsjj> 1000/10; index reference (3) avoid using not and "! = "And" <> ", the index can only tell what exists in the table, but cannot tell what does not exist in the table, when the database encounters not and"! = "And" <> ", the full table scan is performed instead of using indexes. Index reference (4) be sure not to process index columns in the search, such as TRIM, TO_DATE, type conversion, and other operations. Destroy the index and use full table scan, references that affect SQL Execution efficiency index (5) Avoid using IS NULL and IS NOT NULL in index columns and avoid using any columns that can be empty in the index, ORACLE will not be able to use this index for a single column index. If a column contains null values, this record will not exist in the index. For a composite index, if each column is empty, this 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, therefore, the Null Value Comparison of the index column in The WHERE clause will make ORACLE disable the reference of the index (6) The index column "> =" instead of ">" is inefficient: select * from report_sale_account where hsjj> 10; efficiency: select * from report_sale_account where hajj> = 10.000000000000001; comparison function sign Function Syntax: sign (n) Function Description: returns 1 if the number n is greater than 0,-1 if the value is less than 0, and 0 if the value is equal to 0. Example 1. select sign (100), sign (-100 ), sign (0) from dual; SIGN (100) SIGN (-100) SIGN (0) ---- 1-1 0 2, a = 10, if B = 20, sign (a-B) returns- 1SQL optimization method the first method is to connect multiple fields using connectors. Method 2: Cancel duplicate rows. Method 3: Use the WHERE statement frequently. Method 4: flexible use of the COUNT Function Method 5: only required fields for query. Method 6: properly process the NULL field. Method 7: Make full use of fuzzy search. Method 8: use Like and other wildcards with caution. Method 9: Use annotations to improve the readability of query statements. Method 10: When necessary, restrict the rows used by the user. 1. Avoid using "*" 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 task is done by querying the data dictionary, which means it takes more time. 2. use the DECODE function to reduce processing time. Use the DECODE function to avoid repeated scanning of the same record or reconnecting to the same table. example: SQL code SELECT COUNT (*), SUM (SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE 'Smith % '; SELECT COUNT (*), SUM (SAL) from emp where DEPT_NO = 0030 and ename like 'Smith % '; you can use the DECODE function to efficiently get the same result: 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_S AL, SUM (DECODE (DEPT_NO, 0030, SAL, NULL) D0030_SAL from emp where ename like 'Smith % '; similarly, the DECODE function can also be used in group by and order by clauses. Decode Description: decode (condition, value 1, translation value 1, value 2, translation value 2 ,... value n, translation value n, default value) 3. the most efficient method for deleting 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 deleting records in a table, a rollback segment is usually used to store information that can be recovered. If you do not have a COMMIT transaction, ORACLE restores the data to the State before the deletion (which is precisely the State before the deletion command is executed). When TRUNCATE is used, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short. 5. calculate the number of records and a general view On the contrary, count (*) is slightly faster than count (1). Of course, if you can search by index, the index column count is still the fastest. for example, COUNT (EMPNO) 6. replace HAVING clause with the Where clause to avoid HAVING clause. 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, for example, SQL code-inefficient SELECT REGION, AVG (LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION! = 'Sydney 'and region! = '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 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. 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 the subquery, the not in Clause executes an internal sorting and merging. either way IN this case, not in is the most inefficient (because it executes a full table traversal for the tables IN the subquery ). to avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists. example: SELECT... From emp where DEPT_NO not in (SELECT DEPT_NO from dept where DEPT_CAT = 'A'); SQL code -- rewrite to (Method 1: efficient) SELECT… to improve efficiency .... From emp a, dept B WHERE. DEPT_NO = B. DEPT (+) and B. DEPT_NO is null and B. DEPT_CAT (+) = 'A' -- (Method 2: 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 the Department table and employee table), avoid using DISTINCT in the SELECT clause. in general, you can consider replacing it 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 -- efficiency: SELECT DEPT_NO, DEPT_NAME from dept d where exists (SELECT 'x' from emp e where e. DEPT_NO = D. DEPT_NO); -- EXIS TS makes queries faster, because the RDBMS core module will return results immediately after the subquery conditions are met. 10. using indexes to improve efficiency index is a conceptual part of a table to improve the efficiency of data retrieval. In fact, ORACLE uses a complex self-balancing B-tree structure, generally, data query by index is faster than full table scan. When ORACLE finds the optimal path for executing the query and Update statements, the ORACLE optimizer uses the index, using indexes to join multiple tables can also improve efficiency. Another advantage of using indexes is that it provides uniqueness verification of the primary key, except for those LONG or long raw data types, You Can index almost all columns. generally, using indexes in large tables is particularly effective. of course, you will also find that using indexes to scan small tables can also improve the efficiency. Although using indexes can improve the query efficiency, we must pay attention to the cost. indexes require space for storage and regular maintenance. Each time a record is added or removed from a table or the index column is modified, the index itself is also modified, which means The INSERT, DELETE, and UPDATE operations of each record will be charged for four or five more disk I/O operations, because the index requires additional storage space and processing, unnecessary indexes will slow the query response time. Note: Regular index reconstruction is necessary. 11. avoid using the WHERE clause in the index column. If the index column is part of the function, the optimizer will use full table scanning instead of using the index. example: SQL code-inefficient: SELECT... From dept where sal * 12> 25000; -- efficiency: SELECT... From dept where sal & gt; 25000/12; 12. replace with> => SQL code -- If DEPTNO has an index -- efficient: SELECT * FROM EMP WHERE DEPTNO> = 4 -- inefficient: SELECT * from emp where deptno> 3, the former DBMS will directly jump to the first record whose DEPT is equal to 4, while the latter will first locate the record whose DEPTNO is = 3 and scan forward to the record whose first DEPT is greater than 3.

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.