Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to optimize the SQL sentence, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

(1) choose the most efficient order of table names (valid only in rule-based optimizers):

ORACLE's parser processes the table names in the FROM clause from right to left, and the last table written in the FROM clause (the underlying table driving table) will be processed first. in the case of multiple tables in the FROM clause, you must select the table with the least number of records as the base table. If there are more than 3 table join queries, you need to select an intersection table as the underlying table, which refers to the table referenced by other tables.

(2) the connection order in the WHERE clause.

ORACLE parses the WHERE clause in a bottom-up order. According to this principle, the join between tables must be written before other WHERE conditions, and 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:

In the process of parsing, ORACLE converts'* 'into all column names in turn, which is done by querying the data dictionary, which means it will take more time.

(4) reduce the number of visits to the database:

ORACLE does 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 retrieval data per 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 joining the same tables.

(7) simple and unrelated database access:

If you have several simple database queries, you can integrate them into a single query (even if there is no relationship between them).

(8) Delete duplicate records:

Examples of the most efficient ways to delete duplicate records (because of the use of ROWID):

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN (X.ROWID))

(9) replace DELETE with TRUNCATE:

When deleting records in a table, the rollback rollback segments is usually used to store information that can be recovered. If you do not have a COMMIT transaction, ORACLE will restore the data to the state it was before the deletion (to be exact, the state before the delete command) and when using TRUNCATE, the rollback segment no longer holds any recoverable information. When the command runs, the data cannot be recovered. As a result, few resources are called and the execution time is very short. (translator's note: TRUNCATE is only applicable for deleting the whole table. TRUNCATE is DDL, not DML).

(10) use COMMIT as much as possible:

Whenever possible, use COMMIT in your program as much as possible, so that the performance of the program is improved and the requirements are reduced because of the resources released by COMMIT:

Resources released by COMMIT:

a. The information used to recover data on the rollback segment.

b. A lock acquired by a program statement

C. Space in redo log buffer

D. ORACLE is used to manage the internal expenses of the above three resources

(11) replace the HAVING clause with Where clause:

Avoid using the HAVING clause, HAVING will filter the result set only after all records have been retrieved. This process requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce the overhead. (in non-oracle) among the three clauses on, where and having that can be conditioned, on is the first to execute, where is the second, and having is the last. Because on first filters the records that do not meet the conditions before counting, it can reduce the data to be processed by intermediate operations, which is supposed to be the fastest, and where should also be faster than having, because it does sum after filtering the data, and on is used only when the two tables are joined. So in a table, only where is left to compare with having. In the case of querying statistics in this single table, if the conditions to be filtered do not involve the fields to be calculated, then their results are the same, except that where can use rushmore technology, but having cannot. If the latter is slow in speed, if the calculated field is involved, it means that the value of this field is uncertain before it is calculated. According to the workflow written in the previous article, the action time of where is completed before calculation. Having works only after calculation, so in this case, the results of the two will be different. On works earlier than where in multi-table join queries. First of all, according to the join conditions between the tables, the system combines several tables into a temporary table, then filters it by where, then calculates it, and then filters it by having after calculation. It can be seen that for the filter condition to work correctly, we must first understand when the condition should work, and then decide where to put it.

(12) reduce queries on tables:

In SQL statements with subqueries, special attention should be paid to reducing queries on tables. Example:

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (SELECT

(13) improve the efficiency of SQL through internal functions.

Complex SQL often sacrifices executive efficiency. To be able to master the above methods of using functions to solve problems is very meaningful in practical work.

(14) use the alias of the table (Alias):

When joining multiple tables in a SQL statement, use the alias of the table and prefix it on each Column. In this way, parsing time and syntax errors caused by Column ambiguity can be reduced.

(15) replace IN with EXISTS and NOT IN with NOT EXISTS:

In many queries based on underlying tables, it is often necessary to join another table in order to meet one condition. In this case, using EXISTS (or NOT EXISTS) will usually improve the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort and merge. In either case, NOT IN is the least efficient (because it performs a full table traversal on the tables in the subquery). To avoid using NOT IN, we can rewrite it as an external connection (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')

(16) identify the SQL statement of 'inefficient execution':

Although there are a variety of graphical tools for SQL optimization, writing your own SQL tool to solve the problem is always the best way:

SELECT EXECUTIONS, DISK_READS, BUFFER_GETS

(17) use indexes to improve efficiency:

Index is a conceptual part of a table, which is used to improve the efficiency of data retrieval. ORACLE uses a complex self-balancing B-tree structure. In general, querying data through an index is faster than a full table scan. When ORACLE finds the best way to execute queries and Update statements, the ORACLE optimizer uses indexes. Similarly, using indexes when joining multiple tables can also improve efficiency. Another benefit of using an index is that it provides unique verification of the primary key (primary key). For those LONG or LONG RAW data types, you can index almost all columns. In general, using indexes in large tables is particularly effective. Of course, you will also find that using indexes can also improve efficiency when scanning small tables. Although the use of indexes can improve query efficiency, we must also pay attention to its cost. The index needs space to store and needs to be maintained regularly. Whenever a record is added or decreased in the table or the index column is modified, the index itself will be modified. This means that the INSERT, DELETE, and UPDATE of each record will pay an extra 4 or 5 times for the disk. Because indexes require additional storage space and processing, unnecessary indexes can slow down query response time. It is necessary to restructure the index regularly.

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 using DISTINCT in the SELECT clause. Generally, we can consider replacing it with EXIST. EXISTS makes the query faster, because the core module of RDBMS will return the results as soon as the conditions of the subquery are met. Example:

(inefficient):

(19) sql statements are in uppercase; because oracle always parses sql statements first, converting lowercase letters to uppercase before execution

(20) try not to use the connector "+" to connect strings in java code!

(21) avoid using NOT on index columns usually

We should avoid using NOT on index columns. NOT will have the same effect as using functions on index columns. When ORACLE encounters NOT, he stops using indexes and performs a full table scan.

(22) avoid using calculations on index columns.

In the WHERE clause, if the index column is part of the function. The optimizer will use a full table scan instead of an index.

For example:

Inefficient:

(23) replace > with > =

Efficient:

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 of DEPTNO=3 and scan forward to the first record with DEPT greater than 3.

(24) replace OR with UNION (for index columns)

In general, replacing OR in the WHERE clause with UNION will have a better effect. Using OR on index columns will result in a full table scan. Note that the above rules are valid only for multiple index columns. If column is not indexed, query efficiency may be reduced because you did not choose OR. In the following example, indexes are built on both LOC_ID and REGION.

Efficient:

If you insist on using OR, you need to return the index column with the least number of records at the top.

(25) replace OR with IN

This is a simple and easy-to-remember rule, but the actual execution effect still needs to be tested. Under ORACLE8i, the two execution paths seem to be the same.

Inefficient:

(26) avoid using IS NULL and IS NOT NULL on index columns

Avoid using any nullable column in the index. ORACLE will not be able to use the index. For a single-column index, if the column contains a null value, the record will not exist in the index. For a composite index, if each column is empty, the record also does not exist in the index. if at least one column is not empty, the record exists in the index. for example: if the uniqueness index is established on columns An and B of the table, and there is a record in the table with a record with a value of 123 (123), ORACLE will not accept the next record (insert) with the same value of A (123). However, if all index columns are empty, ORACLE will assume that the entire key value is empty and empty is not equal to empty. So you can insert 1000 records with the same key value, but of course they are all empty! Because null values do not exist in index columns, comparing index columns with null values in the WHERE clause will cause ORACLE to deactivate the index.

Inefficient: (index invalidation)

(27) always use the first column of the index:

If the index is built on multiple columns, the optimizer will choose to use the index only if its first column (leading column) is referenced by the where clause. This is also a simple but important rule. When referencing only the second column of the index, the optimizer uses a full table scan and ignores the index.

28) replace UNION with UNION-ALL (if possible):

When the SQL statement requires two query result sets to UNION, the two result sets are merged as UNION-ALL, and then sorted before the final result is output. If UNION ALL is used instead of UNION, sorting is not necessary. As a result, efficiency will be improved. It is important to note that UNION ALL will repeatedly output the same records in both result sets. Therefore, you still need to analyze the feasibility of using UNION ALL from the business requirements. UNION will sort the result set, which will use the SORT_AREA_SIZE memory. It is also very important to optimize this piece of memory. The following SQL can be used to query the sorted consumption

Inefficient:

(29) replace ORDER BY with WHERE:

The ORDER BY clause uses the index only under two strict conditions.

All columns in the ORDER BY must be contained in the same index and kept in order in the index.

All columns in the ORDER BY must be defined as non-empty.

Indexes used in WHERE clauses and indexes used in ORDER BY clauses cannot be juxtaposed.

For example:

The table DEPT contains the following:

DEPT_CODE competes for NOT NULL

DEPT_DESC NOT NULL

DEPT_TYPE NULL

Inefficient: (index is not used)

(30) avoid changing the type of index column.

When comparing data of different data types, ORACLE automatically performs simple type conversions on columns.

Suppose EMPNO is an index column of a numeric type.

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, type conversion does not occur on index columns, and the purpose of the index has not been changed.

Now, suppose EMP_TYPE is an index column of character type.

SELECT... FROM EMP WHERE EMP_TYPE = 123

This statement is converted by ORACLE to:

SELECT... FROM EMP WHERETO_NUMBER (EMP_TYPE) = 123

This index will not be used because of the internal type conversion! To prevent ORACLE from implicitly casting your SQL, it's best to show the conversion explicitly. Note that when comparing characters with numeric values, ORACLE gives priority to converting numeric types to character types

(31) the WHERE clause that needs to be careful:

The WHERE clause in some SELECT statements does not use an index. Here are some examples.

In the following example, (1)'! = 'will not use the index. Remember, indexes can only tell you what exists in the table, not what doesn't exist in the table. (2)'| |'is a character concatenation function. Like other functions, the index is disabled. (3)'+'is a mathematical function. Like other mathematical functions, the index is disabled. (4) the same index columns cannot be compared with each other, which will enable full table scanning.

(32) a. If you retrieve the number of records in a table with more than 30% of the data. There will be no significant efficiency gains in the use of indexes.

b. In certain cases, using an index may be slower than a full table scan, but this is the same order of magnitude difference. In general, using an index is several or even thousands of times more block than a full table scan!

(33) avoid resource-consuming operations:

The SQL statement with DISTINCT,UNION,MINUS,INTERSECT,ORDER BY starts the SQL engine

Perform resource-consuming sorting (SORT) functions. DISTINCT requires one sort operation, while others need to perform at least two sort operations. Usually, SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways. If the SORT_AREA_SIZE of your database is well provisioned, 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 unwanted records before GROUP BY. The following two queries return the same results, but the second one is significantly faster.

Inefficient:

Thank you for reading this article carefully. I hope the article "how to optimize SQL sentences" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.