34 ways for oracle to improve query efficiency

Source: Internet
Author: User

34 ways for oracle to improve query efficiency 1. Select the most efficient table name order (only valid in the rule-based Optimizer ): the ORACLE parser processes the table names in the FROM clause in the order FROM right to left. The table written in the FROM clause (basic table driving table) will be processed first, when the FROM clause contains multiple tables, You must select the table with the least number of records as the base table. If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables. 2. In the WHERE clause, the connection sequence www.2cto.com ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the connections between tables must be written before other WHERE conditions, the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause. 3. Avoid using "*" in the SELECT clause to convert "*" to all column names in sequence during ORACLE parsing. This is done by querying the data dictionary, this means that it will take more time 4. Reduce the number of accesses to the database ORACLE has performed a lot of work internally: parsing SQL statements, estimating the index utilization, binding variables, and reading data blocks; 5. re-set the ARRAYSIZE parameter in SQL * Plus, SQL * Forms, and Pro * C to increase the retrieval data volume for each database access. The recommended value is 2. 006. 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. 7. Integrate simple and unrelated 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) 8. The most efficient way to DELETE duplicate records (because ROWID is used) Example: delete from emp e where e. ROWID> (select min (X. ROWID) from emp x where x. EMP_NO = E. EMP_NO); 9. Use TRUNCATE instead of DELETE. When deleting a table record, a rollback segment is usually used to store information that can be recovered. If you do not have a COMMIT transaction, ORACLE will recover 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. 10. Use COMMIT as much as possible. Use COMMIT as much as possible in the program. As a result, the performance of the program is improved and the demand for resources released by COMMIT is reduced. The resources released by COMMIT include: ① information used to restore data in the rollback segment. ② locks obtained by Program Statements ③ space in redo log buffer ④ ORACLE uses the Where clause to replace HAVING clause to avoid HAVING clause to manage the internal costs of the above three types of resources, HAVING filters the result set only after all records are retrieved. This process requires sorting, total, and other operations. If the WHERE clause can be used to limit the number of records, this overhead can be reduced. (in non-oracle) where on, where, and having can be added, on is the first statement to execute, where is the second clause, and having is the last clause, because on filters out records that do not meet the conditions before making statistics, it can reduce the data to be processed by intermediate operations. It is reasonable to say that the speed is the fastest, where should also be faster than having, because it performs sum only after filtering data, and on is used only when two tables are joined, so in a table, then we can compare where with having. In the case of single-Table query statistics, if the filter condition does not involve fields to be calculated, the results will be the same, but the where technology can be used, having cannot be used. In terms of speed, the latter must be slow. If a calculated field is involved, the value of this field is uncertain before calculation, according to the workflow written in the previous article, the where function time is completed before computing, and having works only after computing. In this case, the results are different. In multi-table join queries, on takes effect earlier than where. The system first combines multiple tables into a temporary table based on the join conditions between tables, then filters them by where, then computes them, and then filters them by having after calculation. It can be seen that to filter a condition to play a correct role, we must first understand when the condition should take effect, then I decided to put it there 12. Reduce the number of queries to the table in the SQL statement containing subqueries. Pay special attention to reducing the number of queries to the table. example: SELECT TAB_NAME from tables where (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS where version = 604) 13. Using Internal functions to improve SQL efficiency complex SQL statements often sacrifice execution efficiency. being able to master the above method to solve the problem by using functions is very meaningful in actual work 14. When using the table Alias (Alias) to connect multiple tables in SQL statements, 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. 15. Replace IN with EXISTS, and replace not in with NOT EXISTS IN 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. 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 the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists. example: // (efficient) SELECT * from emp (basic table) where empno> 0 and exists (SELECT "X" from dept where dept. DEPTNO = EMP. deptno and loc = "MELB") // (inefficient) SELECT * from emp (basic table) where empno> 0 and deptno in (SELEC T deptno from dept where loc = "MELB") 16. Identify "inefficient execution" SQL statements. Although various graphical tools for SQL optimization are emerging, but writing your own SQL tool to solve the problem is always the best method: SELECT EXECUTIONS, DISK_READS, BUFFER_GETS, ROUND (BUFFER_GETS-DISK_READS)/BUFFER_GETS, 2) Hit_radio, ROUND (DISK_READS/EXECUTIONS, 2) Reads_per_run, SQL _TEXT FROM V $ SQLAREA WHERE EXECUTIONS> 0 AND BUFFER_GETS> 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS 0.8 ORDER BY 4 DESC; 17. Using indexes to improve efficiency index is an overview of the table. ORACLE uses a complex self-balancing B-tree structure to improve data retrieval efficiency. data Query by index is usually 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 when joining multiple tables can also improve efficiency. another advantage of using an index is that it provides uniqueness verification for the primary key .. 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 efficiency. although the index can improve the query efficiency, we must pay attention to its cost. the index requires space for storage and regular maintenance. The index itself is also modified whenever a record is increased or decreased in the table or the index column is modified. this means that the INSERT, DELETE, and UPDATE operations for each record will pay four or five more disk I/O. because indexes require additional storage space and processing, unnecessary indexes will slow the query response time .. Regular index reconstruction is necessary.: alter index REBUILD18. Replace DISTINCT with EXISTS. When submitting a query that contains one-to-many table information (such as department table and employee table), avoid using DISTINCT in the SELECT clause. in general, you can consider replacing it with EXIST, and EXISTS makes the query more rapid, because the RDBMS core module will return the result immediately after the subquery conditions are met. example: // (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); 19. SQL statements are capitalized because oracle is always the first Parse SQL statements, convert lowercase letters into uppercase letters, and then execute 20. Use the connector "+" to connect strings as little as possible in java code! 21. Avoid using NOT in the index column. Avoid using NOT in the index column. NOT will have the same effect as using the function in the index column. when ORACLE Encounters "NOT", it stops using indexes and performs full table scanning. 22. Avoid using computation on index columns. in the WHERE clause, if the index column is part of the function. the optimizer uses full table scan instead of indexing. example: // inefficient: SELECT... From dept where sal * 12> 25000; // efficient: SELECT... From dept where sal> 25000/12; 23. Replace with> => // 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. 24. Replacing OR with UNION (applicable to index columns) usually results in better performance when you replace OR in the WHERE clause with UNION. using OR for index columns will scan the entire table. note that the preceding rules are only valid for multiple index columns. if a column is not indexed, the query efficiency may be reduced because you did not select OR. in the following example, both LOC_ID and REGION have indexes. // efficient: SELECT LOC_ID, LOC_DESC, REGION From location where LOC_ID = 10 union select LOC_ID, LOC_DESC, region from location where region = "MELBOURNE" // inefficient: SELECT LOC_ID, LOC_DESC, region from location where LOC_ID = 10 or region = "MELBOURNE" if you insist on using OR, you need to write at the beginning of the index column with the least records. 25. Using IN to replace OR is a simple and easy-to-remember rule, but the actual execution results must be tested. IN ORACLE8i, the execution paths of the two seem to be the same. // inefficient: SELECT .... From location where LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 // efficient: SELECT... From location where LOC_IN IN (10, 20, 30); 26. 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 this index. this record does not exist in the index if the column contains a null value. 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 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, B value (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 of the index column in The WHERE clause will make ORACLE disable the index. // inefficient: (index failure) SELECT... From department where DEPT_CODE is not null; // efficient: (index valid) SELECT... From department where DEPT_CODE> = 0; 27. Always use the first column of the index. If the index is created on multiple columns, only the first column (leading column) when referenced by the where clause, the optimizer selects this index. this is also a simple and important rule. When only the second column of the index is referenced, the optimizer uses full table scan while ignoring index 28 and replaces UNION with UNION-ALL (if possible). When an SQL statement requires UNION, the two result sets are merged in the UNION-ALL method and sorted before the final result is output. if union all is used to replace UNION, sorting is unnecessary. the efficiency will be improved accordingly. note that union all will repeatedly output the same records in the two result sets. therefore, you still need to analyze the feasibility of using union all from the business needs. UNION will sort the result set. This operation will use SORT_ARE A_SIZE memory. this memory optimization is also very important. the following SQL statements can be used to query the consumption of sorting // inefficient: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = "31-DEC-95" UNION www.2cto.com SELECT ACCT_NUM, BALANCE_AMT FROM nation WHERE TRAN_DATE = "31-DEC-95" // efficient: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = "31-DEC-95" union all select ACCT_NUM, BALANCE_AMT FROM region WHERE TR AN_DATE = "31-DEC-95" 29. Replace the order by clause with the WHERE clause to use indexes only under two strict conditions; all columns in order by must be included in the same index and maintained in the ORDER of the index .; all columns in order by must be defined as non-empty. the index used BY the WHERE clause and the index used in the order by clause cannot be tied together. for example: // The DEPT table contains the following columns: DEPT_CODE pk not null DEPT_DESC not null DEPT_TYPE NULL // inefficient: (the index is NOT used) SELECT DEPT_CODE from dept order by DEPT_TYPE // efficient: (using indexes) SELECT DEPT_CODE from dept where DEPT_TYPE> 030. Avoid changing the index column type. ORACLE automatically simplifies columns when comparing different data types Single type conversion. Assume that EMPNO is a numeric index column. SELECT... From emp where empno = "123" In fact, after ORACLE type conversion, the statement is converted to: SELECT... From emp where empno = TO_NUMBER ("123") Fortunately, the type conversion does not occur on the index column, and the purpose of the index is not changed. assume that EMP_TYPE is a character-type index column. SELECT... From emp where EMP_TYPE = 123 this statement is converted to: WHERETO _ SELECT... From emp number (EMP_TYPE) = 123 because of internal type conversion, this index will not be used! To avoid implicit type conversion for your SQL statements, it is best to explicitly display the type conversion. note: When comparing characters and values, ORACLE will first convert the value type to character type 31. You must be careful that the WHERE clause in some SELECT statements does not use the index. here are some examples. in the example below ,"! = "No index will be used. remember, indexes only tell you what exists in the table, but not what does not exist in the table. "|" is a character connection function. as with other functions, indexes are disabled. "+" is a mathematical function. as with other mathematical functions, indexes are disabled. the same index Columns cannot be compared with each other, which enables full table scan. 32. If the retrieved data volume exceeds 30% of the records in the table. using indexes will not significantly improve the efficiency. in certain cases, using indexes may be slower than full table scanning, but this is an order of magnitude difference. in general, using an index is several times or even several thousand times more than a full table scan! 33. Avoid using resource-consuming SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by. The SQL engine is started to execute the resource-consuming sorting (SORT) function. DISTINCT requires a sorting operation, while other operations require at least two sorting operations. generally, SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways. if your database's SORT_AREA_SIZE is well configured, you can also consider using UNION, MINUS, and INTERSECT. After all, they are highly readable. 34. Optimize group by to improve the efficiency of group by statements. You can filter out unnecessary records before group. the following two queries return the same results, but the second one is much faster. // inefficient: www.2cto.com select job, AVG (SAL) from emp group job having job = "PRESIDENT" or job = "MANAGER" // efficient: select job, AVG (SAL) from emp where job = "PRESIDENT" or job = "MANAGER" GROUP JOB

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.