To improve the efficiency of the SQL query where clause conditions the order of how to write your SQL statement to fully optimize the Category: Database 2010-11-10 18:17 33 Read Comments (0) Add report reproduced from suyineng
Final edit suyineng
Not only do we have to write SQL, but also to achieve excellent performance to write the SQL statement.
(1) choose the most efficient sequence table name (only in the rule-based optimizer effectively):
Oracle's parser in accordance with the order processing from right to left in the FROM clause table name, FROM clause written in the last table (base table driving table) will be processed first in the FROM clause contains multiple tables case, you must select the least number of records as the basis of the table table. If there are more than 3 tables join query, it would need to select the cross-table (intersection table) as the underlying table, crosstab is that the table referenced by other tables.
(2) WHERE clause of the connection:
Oracle uses a bottom-up parsing WHERE clause order, according to this principle, the connection between the table must be written before the other WHERE condition, which can filter out the maximum number of records the conditions in the WHERE clause must be written at the end.
(3) SELECT clause to avoid using '*':
Oracle in the resolution process, will be '*' in order to convert all the column names, this work is done by querying the data dictionary, which means more time consuming.
(4) to reduce the number to access the database:
Oracle's internal implementation of a number of tasks: parsing SQL statements, estimates the index of utilization, bind variables, such as reading data blocks.
(5) SQL * Plus, SQL * Forms and Pro * C ARRAYSIZE re-set the parameters for each database access can increase the amount of data retrieval, the proposed value of 200.
(6) using the DECODE function to reduce the processing time:
Scan using the DECODE function to avoid duplicate records or repeat the same connection the same table.
(7) integration of simple, non-associated database access:
If you have a few simple database queries, you can put them into a query (even if no relationship between them).
(8) to remove duplicate records:
The most efficient way to delete duplicate records (because of the use of ROWID) examples:
DELETE FROM EMP E WHERE E. ROWID> (SELECT MIN (X. ROWID) FROM EMP X WHERE X. EMP_NO = E. EMP_NO);
(9) replaced with TRUNCATE DELETE:
When you delete records in the table, in general, rollback (rollback segments) can be used to store recovery information. If you do not COMMIT transaction, ORACLE will be deleted before the data back to the state (precisely back to the delete command before the situation) and when using TRUNCATE, the rollback will not store any information that can be restored. When the command runs, the data can not be restored, so few resources is called, execution time will be very short. (TRUNCATE to delete the whole table only applies, TRUNCATE is DDL not DML).
(10) as much as possible using the COMMIT:
Whenever possible, use as much as possible in the program COMMIT, improve the performance of such procedures, the demand will be because the resources released by COMMIT reduced, COMMIT released resources:
a. on rollback for recovery data.
b. by the program statements to obtain the lock.
c. redo log buffer in the space.
d. Oracle for the management of these three kinds of resources in-house costs.
(11) replaced with a Where clause, HAVING clause:
Avoid using HAVING clause, HAVING will retrieve all records in the result set after filtering fishes. This process requires a sort, total and other operations. If through the WHERE clause to restrict the number of records that can reduce this overhead. (Not the oracle) on, where, having these three conditions can increase the clause, on the first execution, where second, having the last, because on the first record does not meet the conditions after the statistical filter , it can reduce data center operations to deal with, arguably should be the fastest speed, where it should quickly than having, as it filters the data only after the sum, only two connections with on the table, So in a table, where on the left compared with having. In this single-table query statistics in the case, if the conditions are not related to the filter to calculate the field, and that their results are the same, but where you can use the rushmore technology, and having it not, slower speed on the latter If you are involved in the calculation of the field, would mean not calculated before the value of this field is uncertain, according to the articles written on the workflow, where the role of time before the completion of the calculation, and is having calculated after work, so in this case, the two results will be different. In multi-table join query, on earlier work than where. System first joins between tables based on various conditions, the synthesis of a temporary table more than one table, and then filtered by the where, and then calculate, by the end of the calculation having to filter. Thus, in order to filter the right to play the role, we must first understand the conditions of work should be at what time, and then decide on there.
(12) to reduce the query table:
Containing sub-queries in the SQL statement, paying particular attention to reducing the table query. Example:
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME, DB_VER) = (SELECTTAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
(13) through an internal function to improve SQL efficiency:
Often at the expense of complex SQL execution efficiency. Able to master the use of the above functions to solve problems in practical work is very meaningful.
(14) using a table alias (Alias):
When the SQL statement to connect more than one table, use the table alias and the alias prefix in each Column on. As a result, we can reduce the analysis time and reduce the ambiguity that caused by the Column of syntax errors.
(15) replaced with EXISTS IN, NOT EXISTS instead using NOT IN:
In many queries based on the underlying table in order to satisfy a condition, often need to join to another table. In this case, use the EXISTS (or NOT EXISTS) usually will improve the efficiency of query. In the sub-query, NOT IN clause will perform an internal sort and merge. In either case, NOT IN is the least efficient (because of its sub-tables in the query performs a full table traverse). In order to avoid using NOT IN, we can rewrite it into outer join (Outer Joins) or NOT EXISTS.
(High) SELECT * FROM EMP (base table) WHERE EMPNO> 0 AND EXISTS (SELECT 'X''FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC =' MELB'')
(Inefficient) SELECT * FROM EMP (base table) WHERE EMPNO> 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB'')
(16) identify the 'inefficient implementation' SQL statement:
Although a variety of graphical tools for SQL optimization endless, but to write their SQL tools to solve the problem is always the best way:
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;
Index is a conceptual part of the table, used to improve the efficiency of data retrieval, Oracle uses a complex self-balancing B-tree structure. Typically, the query data through the index faster than full table scan. When execute the query and Oracle Update statement to find out the best path, Oracle optimizer will use index. Similarly the use of multiple tables in a join index can improve efficiency. Another advantage of using an index is that it provides a primary key (primary key) is unique verification. Those LONG or LONG RAW data type, you can index almost all the columns. Typically, a large table in the particularly effective use of the index of course, you will find a small table in the scan, use the index can also improve efficiency. Although the index can be used to improve the efficiency of the query, but we also must be noted that price. Need space to store the index, also need regular maintenance, whenever a record changes in the table or index column is modified, the index itself will be modified. This means that each record of the INSERT, DELETE, UPDATE will do more than pay four or five times the disk I / O. Because the index requires additional storage space and processing, the index would bring unnecessary slow query response time. Periodic reconstruction of the index is necessary:
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
(18) replaced with EXISTS DISTINCT:
When submitting a form that contains many information (such as the department table and employee table) query, to avoid in the SELECT clause to use DISTINCT. Generally consider replacing EXIST, EXISTS make queries more quickly, because the RDBMS kernel module in the subquery once the conditions are met, immediately returns the result. Example:
(Inefficient): SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP E WHERE D. DEPT_NO = E. DEPT_NO
(High): SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT 'X''FROM EMP E WHERE E. DEPT_NO = D. DEPT_NO);
(19) SQL statement with an uppercase; for parsing Oracle SQL statements are always the first, the lower case letters converted to uppercase before performing.
(20) in the Java code to minimize the use-character "+" connection string.
(21) to avoid using NOT in the index column is usually, we must avoid the use of the index columns NOT, NOT, and will have the function of the index columns using the same impact. When the Oracle "encounter" NOT, he would stop using the index instead perform a full table scan.
(22) to avoid the use of columns in the index calculation. WHERE clause, if the index column is part of the function. Optimizer will not use the index to use full table scan.
Inefficient: SELECT ... FROM DEPT WHERE SAL * 12> 25000;
Efficient: SELECT ... FROM DEPT WHERE SAL> 25000/12;
(23) with> = instead>:
Efficient: SELECT * FROM EMP WHERE DEPTNO> = 4 inefficient: SELECT * FROM EMP WHERE DEPTNO> 3
The difference is that the former DBMS will skip the first record of DEPT is equal to 4 while the latter will first locate the DEPTNO = 3 records, and scans forward to a record of greater than 3 DEPT.
(24) replaced with a UNION OR (for the index column):
Typically, the WHERE clause with a UNION replace the OR will play better results. Of the index columns by using the OR will result in full table scan. Note that these rules only for the more effective index of the column. If column is not indexed, the query efficiency may be because you have no choice but to reduce OR. In the following example, LOC_ID and REGION are built on the index.
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"
(25) to replace with the IN OR:
This is a simple and easy to remember rules, but the actual implementation of the results have to test, in Oracle8i, the execution path between the two seems to be the same:
SELECT .... FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
SELECT ... FROM LOCATION WHERE LOC_IN IN (10,20,30);
(26) to avoid the use of the index column IS NULL and IS NOT NULL:
Avoid using any index the column is empty, Oracle will not use the index. For a separate index, if the column contains a null value, the index will not exist in this record. For the composite index, if each column is empty, the index does not exist in the same record. If at least one column is not empty, then the record exists in the index. For example: If a unique index based on table column A and column B, and there is a record of table A, B values (123, null), Oracle will not accept the next one with the same A, B values (123 , null) record (insert). However, if all the index columns are empty, Oracle will think the key is null and empty is not equal to null. 1000 so you can insert a record with the same key, of course, they are empty! Because null columns do not exist in the index, so the WHERE clause for indexed columns will be null compared ORACLE disable the index.
Inefficient: (failure index)
SELECT ... FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
Efficient: (index is valid)
SELECT ... FROM DEPARTMENT WHERE DEPT_CODE> = 0;
(27) always use the index of the first column:
If the index is based on multiple columns, only in its first column (leading column) is quoted where clause, the optimizer will choose to use the index. This is a simple but important rules, refer to the index when only the second column, the optimizer uses a full table scan and ignore the index.
(28) replaced with a UNION-ALL UNION (if possible):
When the UNION SQL statement requires two query result sets, the two result sets will be UNION-ALL the way to be merged, then sorted out before the final result. If you use UNION ALL instead UNION, so sorting is not necessary. Therefore, efficiency will be improved. Note that, UNION ALL will repeat the same output result set two records. Therefore, you should start with business needs analysis feasibility of using UNION ALL. UNION will sort the result set, this operation will be used to SORT_AREA_SIZE this memory. For the optimization of this memory is also very important. The following SQL query can be used to sort of consumption:
Inefficient: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE =''31-DEC-95''UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS 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 DEBIT_TRANSACTIONS WHERE TRAN_DATE =''31-DEC-95''
(29) replaced with a WHERE ORDER BY:
ORDER BY clause only under strict conditions in both use of the index.
ORDER BY column must contain all the same and keep the index in the index in the order.
ORDER BY columns must be defined for all non-empty.
WHERE clause uses the index and ORDER BY clause used in the index can not be tied.
For example: table DEPT contains the following:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
Inefficient: (Index not used)
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
Efficient: (using index)
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE> 0
(30) to avoid the type of change in the index column:
When comparing the different data types of data, ORACLE automatically type out a simple conversion. Suppose EMPNO is a value type of the index columns: SELECT ... FROM EMP WHERE EMPNO = '123''. In fact, after the Oracle type conversion, the statement into: SELECT ... FROM EMP WHERE EMPNO = TO_NUMBER ('123'').
Fortunately, the type conversion did not occur in the index column, use the index has not been changed. Now, suppose EMP_TYPE index is a character type column: SELECT ... FROM EMP WHERE EMP_TYPE = 123.
This statement is converted to Oracle: SELECT ... FROM EMP WHERETO_NUMBER (EMP_TYPE) = 123. Because the internal type conversion occurs, the index will not be used! In order to avoid the SQL to Oracle for your implicit type conversion, it is best to use explicit type conversion shown. Note that when comparing the character and values, Oracle will first convert the value type to character type.
(31) need to be careful of the WHERE clause:
Some of the SELECT statement in the WHERE clause does not use indexes. Here are some examples:
(1 )'!='' will not use the index. Remember, the index can only tell you what exists in the table, but can not tell you what does not exist in the table.
(2 )'||'' is the character connection function. Like other functions, as disabled index.
(3 )'+'' a mathematical function. Like other mathematical functions, as disabled index.
(4) the same index column can not be compared with each other, which will enable full table scan.
a. If the amount of data retrieved over 30% of the number of records in the table, use the index will not significantly more efficient.
b. In certain cases, the index may be slower than the full table scan, but it is the same order of magnitude difference. Usually the case, use the index than the full table scan to block several times and even thousands of times!
(33) to avoid resource-intensive operations:
With DISTINCT, UNION, MINUS, INTERSECT, ORDER BY SQL statement starts the SQL engine to perform resource-intensive sorting (SORT) function. DISTINCT requires a sorting operation, while others need to perform at least twice a sort. Typically, with a UNION, MINUS, INTERSECT SQL statement can be rewritten in other ways. If you deploy the database SORT_AREA_SIZE well. Use UNION, MINUS, INTERSECT can be considered, after all, they are very readable.
(34) optimization GROUP BY:
Improve the efficiency of the GROUP BY statement, you can not record in the GROUP BY to filter out before. The following two queries return the same result but the second is obviously on a lot faster.
Inefficient: 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
Previous: efficient Oracle SQL statements Next: explain the Oracle database migration to MySQL