A brief discussion on sql statement optimization

(1) Select the most efficient table name order (only valid in rule-based seo/' target='_blank'> optimizer):
ORACLE's parser processes FROM clauses in order from right to left The table name in the FROM clause (the base table driving table) written last will be processed first. When the FROM clause contains multiple tables, you must choose the table with the smallest number of records as the basis. surface. If there are more than 3 tables to join the query, you need to select the intersection table as the base table. The cross table refers to the table that is referenced by other tables.
(2) The connection sequence in the WHERE clause. :
ORACLE uses a bottom-up order to parse WHERE clauses. According to this principle, connections between tables must be written before other WHERE conditions, and those 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:
ORACLE will convert '*' into all column names in sequence during the parsing process. This work is completed by querying the data dictionary, which means it will consume More time
(4) Reduce the number of accesses to get='_blank'> database:
ORACLE performs a lot of work internally: parsing SQL statements, estimating index utilization, binding variables, reading data blocks, etc.;
(5) Reset the ARRAYSIZE parameter in SQL*Plus, SQL*Forms and Pro*C to increase the amount of retrieved data for each get='_blank'> database access. The recommended value is 200
(6) Use the DECODE function To reduce processing time:
Use the DECODE function to avoid repeatedly scanning the same records or repeatedly connecting the same tables.
(7) Integrate simple, non-associative get='_blank'> database access:
If you have several simple get='_blank'>Database query statements, you can integrate them into one query (even if there is no relationship between them)
(8) Delete duplicate records:
The most efficient method to delete duplicate records (because of the use of ROWID) Example:
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP When recording, under normal circumstances, rollback segments are used to store information that can be recovered. If you do not have a COMMIT transaction, ORACLE will restore the data to the state before deletion (to be precise, restore to the execution of the delete command (Previous situation) When TRUNCATE is used, the rollback segment no longer stores any information that can be recovered. After the command is run, the data cannot be recovered. Therefore, few resources are called and the execution time will be very short. (Translation Editor's note: TRUNCATE is only applicable when deleting the entire table. TRUNCATE is DDL not DML)
(10) Use COMMIT as much as possible:
Whenever possible, use COMMIT as much as possible in the program. In this way, the performance of the program will be improved and the demand will be improved. Reduced by the resources released by COMMIT:
Resources released by COMMIT:
a. Information used to recover data on the rollback segment.
b. Locks obtained by program statements
c. Space in redo log buffer
d. ORACLE manages the internal costs of the above three resources
(11) Replace the HAVING clause with the Where clause:
Avoid using the HAVING clause, HAVING will only filter the result set after retrieving all records. This processing requires Sorting, totaling and other operations. If you can limit the number of records through the WHERE clause, you can reduce the overhead in this area. (In non-Oracle) on, where, and having are three clauses that can add conditions, on is It is executed first, followed by where, and having last. Because on first filters the records that do not meet the conditions and then counts them, it can reduce the data to be processed in the intermediate operation. Logically speaking, it should be the fastest, and so should where. It is faster than having, because it filters the data before summing, and only uses on when two tables are joined, so when a table is used, only where and having are compared. In the case of single-table query statistics, if the conditions to be filtered do not involve fields to be calculated, then their results are the same, except where can use rushmore technology, but having cannot, and the latter is slower in speed. To involve a calculated field, it means that the value of this field is uncertain before calculation. According to the workflow written in the previous article, the action time of where is completed before calculation, while having is done after calculation. works, so in this case, the results of the two will be 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 connection conditions between each table, then filters by where, and then calculates. After calculation, it is filtered by having. It can be seen that if you want the filter condition to play a correct role, you must first understand when the condition should take effect, and then decide where to put it
(12) Reduce table queries:
In SQL statements containing subqueries , pay special attention to reducing 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) Improve SQL efficiency through internal functions.:
Complex SQL often sacrifices execution efficiency. Being able to master the above methods of using functions to solve problems is very meaningful in actual work
(14) Use table aliases (Alias):
When connecting multiple tables in a SQL statement, please use the alias of the table and prefix the alias to each Column. In this way, you can reduce the parsing time and reduce the syntax errors caused by Column ambiguity.
(15 ) Replace IN with EXISTS and NOT IN with NOT EXISTS:
In many queries based on basic tables, in order to satisfy a condition, it is often necessary to join another table. In this case, use EXISTS (or NOT EXISTS) This will generally improve the efficiency of the query. In a subquery, the NOT IN clause will perform an internal sort and merge. In either case, NOT IN is the least efficient (because it performs an internal sort on the table in the subquery) A full table traversal). In order to avoid using NOT IN, we can rewrite it into outer joins (Outer Joins) or NOT EXISTS.
Example:
(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 'inefficient execution' SQL statements:
Although there are currently various graphical tools for SQLseo/' target='_blank'> optimization emerging in an endless stream, but write your own SQL tool Solving 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) Use indexes to improve efficiency:
Indexes are a conceptual part of the table and are used to To improve the efficiency of retrieving data, ORACLE uses a complex self-balancing B-tree structure. Generally, querying data through indexes is faster than a full table scan. When ORACLE finds out the best path to execute queries and Update statements, ORACLEseo/' target='_blank'>The optimizer will use indexes. Using indexes can also improve efficiency when joining multiple tables. Another benefit of using indexes is that it provides uniqueness verification of the primary key. With those LONG or LONG RAW data types, you can index almost any column. Generally, using indexes is particularly effective on large tables. Of course, you will also find that using indexes can also improve efficiency when scanning small tables. Although using indexes Query efficiency can be improved, but we must also pay attention to its cost. Indexes require space for storage and regular maintenance. Whenever records are added or deleted in the table or index columns are modified, the index itself will also be modified. . This means that each record's INSERT, DELETE, and UPDATE will cost 4 or 5 more disk I/Os. Because indexes require additional storage space and processing, those unnecessary indexes will actually shorten the query response time. slow.. Periodic rebuilding of indexes is necessary.:
ALTER INDEX REBUILD
18) Replace DISTINCT with EXISTS:
When submitting a query that contains one-to-many table information (such as department tables and employee tables), avoid placing in the SELECT clause Use DISTINCT. Generally, you can consider replacing EXIST. EXISTS makes the query faster, because the RDBMS core module will return the results immediately once the conditions of the subquery 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) The server/' target='_blank'>sql statement is in uppercase; because Oracle always parses the server/' target='_blank'>sql statement first, converts the lowercase letters into uppercase letters and then executes them
( 20) Use the connector "+" as little as possible to connect strings in java code!
(21) Avoid using NOT on index columns. Generally,
We should avoid using NOT on index columns. NOT will have the same impact as using functions on index columns. When ORACLE "encounters" NOT, it will Stop using indexes and perform full table scans instead.
(22) Avoid using calculations on index columns.
In the WHERE clause, if the index column is part of the function. seo/' target='_blank'>The optimizer will not use indexes but use full table scans.
Example:
Inefficient:
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
Efficient:
SELECT … FROM DEPT WHERE SAL > 25000/12;
(23) Use >= instead of>
Efficient:
SELECT * FROM EMP WHERE DEPTNO >=4
Inefficiency:
SELECT * FROM EMP WHERE DEPTNO >3
The difference between the two is that the former DBMS will jump directly to the first record with DEPT equal to 4 while the latter will first Locate the record with DEPTNO=3 and scan forward to the first record with DEPT greater than 3.
(24) Replace OR with UNION (applicable to index columns)
Normally, replacing OR in the WHERE clause with UNION will will have better results. Using OR on index columns will cause a full table scan. Note that the above rules are only valid for multiple index columns. If there are columns that are not indexed, the query efficiency may be reduced because you did not choose OR. In the example below, there are indexes built on both LOC_ID and REGION.
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, then you need to write the index column with the fewest returned records at the front.
(25) Use IN to replace OR
This is a simple and easy-to-remember rule, but the actual execution effect needs to be tested , under 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 on index columns IS NULL and IS NOT NULL
Avoid using any nullable columns in the index, ORACLE will not be able to use the index. For a single-column index, if a column contains a null value, the record will not exist in the index. For a composite index, if each column is null, the record will also not exist in the index. If at least one column is not null, the record exists. in the index. Example: If a unique index is built on columns A and B of the table, and there is a record in the table with A and B values ​​of (123, null), ORACLE will not accept the next record with the same A and B values ​​(123, null) records (insertion). However, if all index columns are null, ORACLE will consider the entire key value to be null and null is not equal to null. Therefore, you can insert 1000 records with the same key value, and of course they are all null. ! Because the null value does not exist in the index column, a null value comparison of the index column in the WHERE clause will cause ORACLE to deactivate the index.
Inefficiency: (index invalid)
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 built on multiple columns, only the first one in it When a column (leading column) is referenced by a where clause, the seo/' target='_blank'> optimizer will choose to use the index. This is also a simple but important rule, when only the second column of the index is referenced When, seo/' target='_blank'> the optimizer uses a full table scan and ignores the index
28) Replace UNION with UNION-ALL (if possible):
When the SQL statement requires UNION two query result sets When , the two result sets will be merged in a UNION-ALL manner, and then sorted before outputting the final result. If UNION ALL is used instead of UNION, sorting is not necessary. The efficiency will be improved. Things to note Yes, 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 based on business requirements. UNION will sort the result sets, and this operation will use the SORT_AREA_SIZE memory. For this Memory seo/' target='_blank'> optimization is also very important. The following SQL can be used to query the consumption of sorting
Inefficiency:
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) Replace ORDER BY with WHERE:
The ORDER BY clause only uses the index under two strict conditions.
All columns in ORDER BY Must be included in the same index and maintain the sort order in the index.
All columns in ORDER BY must be specified