Specification for optimizing oracle SQL statements and compiling oraclesql statements

Specification for optimizing oracle SQL statements and compiling oraclesql statements

1. In principle, the partition key must be used as a condition for partition table queries;

2. Disable the use of functions in the left column of the WHERE clause, for example, trunc (create_date) = trunc (sysdate );

Cause: This will invalidate the index

3. Key SQL statements should be simplified as much as possible and should not contain too many layers to avoid the possibility of execution plan errors. In principle, there should be no more than two layers;

4. Use as few HINT information as possible in SQL. If necessary, we recommend that you use hint to fix the execution plan of key SQL statements. In principle, you must use hint to specify indexes for Batch jobs;

Hint usage: oracle uses HINT to force the index

5. SQL statements connected to large tables may cause execution plan errors due to index statistics errors and ease of use. To achieve stable efficiency, split the SQL statements into multiple single-table queries as much as possible;

6. The values assigned to the number and varchar types in SQL must be consistent with the data type to avoid deteriorating SQL Performance during invisible data conversion;

7. we recommend that you minimize the use of in and replace = for equality queries. If one of the two tables is small and the other is large, the subquery table is large with exists, if the subquery table is small, in is used. No matter which table is large, not exists is faster than not in;

8. If there is no mixed data logic when using union, we suggest replacing it with union all;

9. note the usage of like in SQL. For example, "% variable %" and "% variable" cannot be indexed, and "variable %" must be within the query range, "%" indicates that the greater the index range, the closer the index is to be placed;

10. In principle, the commit statement cannot be submitted in one step. We recommend that you submit one commit at a time between and (the batch business transaction commit volume should be configurable and adjustable as parameters. By default, the batch transaction commit volume should be );

11. The principle of snapshot too old error is that it has nothing to do with the database, most of which are caused by low select efficiency;

Explanation:

The snapshot too old error is unique to oracle and does not appear in mysql.

Assume that Your emp table is large and you are running it.

Select * from emp;

The output result of this statement depends only on the data status of the emp table at, but in fact, because the emp table is very large, you may need to run this statement for 10 minutes before you can see the output result.

At, someone made an update on the emp table, and the update was done directly on the table. How can you make the select statement you run at still see the previous data? Oracle uses the method of querying the ing, that is, to store the data before update in the rollback segment so that your select statement can query the block, will jump to find the ing, so that your select can run correctly.

However, this may happen because the update operator commit. Therefore, the forward projection may be overwritten before being accessed by your select statement. If this happens, the snapshot too old error occurs. You can set the UNDO_RETENTION parameter to tell Oracle how long it will take to retain undo (rollback data, the snapshot too old error will not occur as long as the query time exceeds your limit.

12. bind variables as much as possible to reduce HARD parsing in the database; use dynamic SQL as little as possible to generate a large number of HARD PARSE and cause library cache pin wait;

13. The SELECT statement cannot use numeric constants as aliases for query fields;

14. If the result set obtained by the group by statement requires sort, we recommend that you add order;

15. for update is prohibited in SQL. during routine maintenance, select t. * And rowid from tabname t must be used to replace select * from tabname for update;

16. SQL prohibits the appearance of DELETE and UPDATE statements without any conditions;

17. When Maintaining Tables and indexes, you must specify tablespaces. Other objects with Storage Properties must also specify tablespaces;

18. opening the cursor in the loop body of the application may result in an error that exceeds the maximum number of cursors. We recommend that you move the cursor outside the loop body to avoid opening the cursor too many times. You must close the cursor after the cursor is used;

19. In principle, the length of an SQL statement cannot exceed 4 k;

20. similar SQL statements are the slowest to be executed for the first time, and the execution speed will be faster in the future, because Oracle directly caches the SQL statement through the sharing pool in SGA, then, when the next execution of similar statements directly calls the statements that have been parsed in the cache.

21. complex Logical Structure SQL statements are prone to execution plan changes in background jobs, which is not conducive to later optimization. Try to split complex SQL statements into simple SQL statements, implement logic control in application code (for example, a complex SQL statement with a three-layer structure is modified into three simple SQL statements, and the application program controls the execution logic of these three simple SQL statements ), in this way, the execution logic and the execution plan can be fixed.

22. Use as few functions as possible in the query statement, such as SYSDATE. Instead, use an application to give a value to avoid calling a function every time for each record.

23. for computing SQL statements (such as AVG and SUM) that consume a large amount of CPU (more than 20% for a single operation) and memory (more than 50 MB for a single operation ), the database may impose resource restrictions on such SQL statements. It is recommended that the application extract the required data and process and compute the raw data through middleware (or temporary tables, if it is a temporary table, only necessary data is extracted to the temporary table to reduce the table data volume and improve efficiency. If it is a middleware, all the computing tasks are assigned to the middleware, reduces the burden on the database.