(1) choose the most efficient sequence table name (only in the rule-based optimizer effectively):
ORACLE 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 in the join order. :
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 parsing 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 a lot of work in-house implementation: 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 parameters, can increase the access of each database to retrieve data, suggest a 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)
For example: instead of using ① ② ③ ④
① select * from s_95598_wkst where app_no = '200912000197 '
② select * from s_retvisit where wkst_no = '200912000197 '
③ select sysdate from dual
④ select a. *, b. *, sysdate from s_95598_wkst a, s_retvisit b
where a.app_no = '200912000197 'and b.wkst_no = '200912000197'
(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 is run, the data can not be restored, so few resources is called, the execution time will very short. Translator's note: 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, because the COMMIT will demand the resources released by reduced: COMMIT released resources:
a. on rollback for recovery data.
b. The procedure to obtain the lock statement
c. redo log buffer in the space
d. ORACLE for the management of these three kinds of resources in the internal cost
(11) replaced with a Where clause, HAVING clause:
Avoid using HAVING clause, HAVING will retrieve all records in the result set after filtering fishes and 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 criteria only after filtering statistics, 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 in connection with the two tables on the So in a table, on the left where 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 where
(12) to reduce the query table:
Containing sub-queries in the SQL statement, paying particular attention to reducing the table query. Examples:
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME, DB_VER) = (SELECT
TAB_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 can 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. In this way, 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 subquery, 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. Efficient:
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;
(17) with an index to improve efficiency:
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, usually through an index query data faster than full table scans when the query and identify implementation ORACLE Update The best path statement, ORACLE optimizer will use index. the same number of tables in a join index can also be used to improve efficiency. Another benefit of using the 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, usually in a large table with an index particularly effective, of course, you will find a small table in the scan, use the index can also improve efficiency. While the use of index query efficiency can be improved, but we must also note its costs. index requires space to store, also need regular maintenance, whenever a record changes in the table or index column is modified, the index itself can 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 query response time becomes slow. 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 will be subquery once the conditions are met, immediately return the results. 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; because the oracle sql statement is always the first resolution, the lower case letters converted to uppercase before performing (20) in java code to minimize the use-character "+" connection string! (21) to avoid using NOT in the index column is usually,
We want to avoid the use of the index columns NOT, NOT, and will produce in the index column in the impact of using the same function when 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> = alternative>
Efficient: SELECT * FROM EMP WHERE DEPTNO> = 4
Inefficient: SELECT * FROM EMP WHERE DEPTNO> 3
(24) replaced with a UNION OR (for the index column)
Typically, the WHERE clause with a UNION replace the OR will play better results. The index columns by using the OR will result in full table scan. Note that the above rules are only valid for multiple index columns if the column has not been index, query performance may be because you have no choice but to reduce OR In the following example, LOC_ID and REGION are built on the index efficiently: 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 OR, it would need to return to record at least the index columns to write first.
(25) to replace the OR with the IN
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. 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) 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 be able to use the index. For a separate index, if the column contains a null value, this record will not exist in the index 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), but if all the index columns are empty, ORACLE will think the key is not equal to the empty space and empty, so you can insert the 1000 records 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: (index of failure) 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, which is a simple but important rules, when only reference index 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 the alternative with the UNION ALL UNION, so sorting is not necessary. Therefore, efficiency will be improved. Note that, UNION ALL will repeat the same output result set two records, so you should start with business needs analysis feasibility of using UNION ALL. UNION will sort the result set, this operation will SORT_AREA_SIZE piece of memory used for the optimization of this memory is also very important. The following SQL query can be used to sort of consumption
SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95 'UNION SELECTACCT_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 side by side, 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 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 is a character type of the index columns. 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 on your implicit type conversion it is best to use explicit type conversion shown Note When character and numeric comparison, 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 the index. 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 any other function as disabled index.
(3) '+' is a mathematical function like any other mathematical functions, as disabled index.
(4) the same index column can not be compared with each other, which will enable full table scan.
(32) a. If the amount of data retrieved over 30% of the number of records in the table using the index will not significantly increase the efficiency. B. In certain cases, the index may be slower than the full table scan, but it is the same order of magnitude the difference, while normally, 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, usually with 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' efficiency:
SELECT JOB, AVG (SAL) FROM EMPWHERE JOB = 'PRESIDENT'OR JOB =' MANAGER 'GROUP JOB