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

This article mainly explains "what are the oracle SQL optimization rules". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what the oracle SQL optimization rules are.

1 > choose the most efficient order of table names:

1. Put the table with few records in the last table of the from clause.

two。 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.

Reason: ORACLE's parser processes the table names in the FROM clause from right to left, so 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. When ORACLE processes multiple tables, it joins them by sorting and merging them. First, scan the first table (the last table in the FROM clause) and sort the records, then scan the second table (the last second table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records in the first table.

2 > the connection order in the WHERE clause:

ORACLE parses the WHERE clause from right to left, 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 needs to convert'* 'into all column names by querying the data dictionary.

4. > 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.

(Column ambiguity means that because different tables in SQL have the same Column name, when the Column appears in the SQL statement, the SQL parser cannot determine the attribution of the Column.)

5. > reduce the number of visits:

When each SQL statement is executed, ORACLE performs a lot of work internally: parsing SQL statements, estimating index utilization, binding variables, reading data blocks, and so on. Thus, reducing the number of visits to the database can actually reduce the workload of ORACLE.

6. > (if possible) replace DELETE with TRUNCATE.

When deleting records in a table, the rollback section (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 delete command.

When using TRUNCATE, the rollback section no longer stores 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.

(TRUNCATE is only applicable when deleting records in the whole table. TRUNCATE is DDL, not DML.)

7. > (if possible) use COMMIT

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

8. > (if possible) replace the HAVING clause with the Where clause

Use the HAVING clause as little as possible. 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.

9. > (in some cases) you can use EXISTS instead of IN. NOT EXISTS replaces NOT IN

Performance comparison:

1.Select * from T1 where x in (select y from T2)

2.select * from T1 where

Exists (select 1 from T2 where T2. Y = t1.x)

It is suitable to use exists when T1 records are few and T2 is large (index is used in most cases of exists), and in is suitable for small subquery recordsets.

Cause analysis:

1.Select * from T1 where x in (select y from T2)

The process of execution is equivalent to:

Select * from T1, (select distinct y from T2) T3 where T1 1.x = t3.y

2. Select * from T1 where exists (select 1 from T2 where t2.y = t1.x)

The process of execution is equivalent to:

For x in (select * from T1) loop

If (exists (select 1 from T2 where t2.y = t1.x)

Then

OUTPUT THE RECORD

End if

End loop

In this way, the table T 1 needs to be scanned completely.

Therefore, it can be concluded that it is suitable to use exists when T1 records are small and T2 is large (index is used in most cases of exists), and in is suitable for small subquery recordsets.

10. > replace EXISTS with table join

An example of improving the ninth dozen optimization rules.

11. > replace DISTINCT with EXISTS

EXISTS makes queries faster, because the RDBMS core module once the conditions of the subquery are met

Return the results immediately. DISTINCT sorts first and then removes the same rows according to the sorted order.

12. > use explicit cursors (CURSOR)

Using an implicit cursor, the operation will be performed twice. The first time to retrieve the record, the second time to check the exception of TOO MANY ROWS. The explicit cursor does not perform the second operation.

11-example:

1. (inefficient) SELECT DISTINCT DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

two。 Efficient:

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS (SELECT *

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO)

13. Use index to improve efficiency

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.

You can index almost all columns except those LONG or LONG RAW data types. Using indexes in large tables is particularly effective.

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 will slow down the query response time.

It is necessary to reconstruct the index on a regular basis.

ALTER INDEX REBUILD

Thank you for your reading, the above is the content of "what are the oracle SQL optimization rules?" after the study of this article, I believe you have a deeper understanding of what oracle SQL optimization rules have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.