Optimization of stored procedures and SQL statements in Oracle

Oracle DatabaseIt is a very functional database management system and remains a major share of the database market so far. The following sections will explain OracleStorageProcess andSQL statementAnd hope to help you.

1. Full table scan and index Scan

The big data table tries its best to avoid full table scans. All scans Scan each record in sequence, which has a great impact on data tables larger than 1 million.

In Oracle, RowID is the fastest way to access data.

Function Conversion of fields, or prefix fuzzy query, will cause failure to apply indexes for full table scan.

The SQL statements in the Oracle shared pool and buffer must be case-insensitive before they can be matched.

2. Order Problems

Oracle parses data tables from right to left. Therefore, the table at the end of the From statement is the base table. Generally, the table with the least number of records must be selected as the base table.

For the order of Where conditions, the conditions for filtering the maximum number of query records must be written at the end of Where conditions.

When using a complex function for the Where condition, you must note that you must write it to the beginning of the Where condition.

3. Indexing

A table with a small number of records can be retained with a primary key index. Do not create other indexes. The full table scan is fast.

It is best to create a tablespace for the index separately and re-build the index if necessary.

Function indexes can be used if necessary, but are not recommended.

Views in Oracle can also be indexed, but it is generally not recommended

When a large number of functions are used in SQL statements, many indexes cannot be used.

4. Others

Avoid using Select, because the system needs to help you convert to all column names, which requires additional data dictionary queries.

Count (1) and Count () are not much different.

Use the Decode function to convert code and name to reduce table Association.

Use Truncate instead of delete to delete records. However, Truncate data does not record logs and cannot be rolled back.

For complex stored procedures, data that can be submitted multiple times must be committed multiple times. Otherwise, long transactions will have a great impact on system performance.

Distinct and Having clauses are time-consuming operations and should be used as little as possible

Union All is used to replace Union when repeat record merging is not required.

Explicit cursors are used instead of implicit cursors, especially when the data volume is large.

Whether to use functions

Use Direct table Association instead of Exist. Use Exist or Not Exists to represent In. Sub-queries In are inefficient.

5. SQL Statement Analysis

Use the set trace function in SQLPLUS to analyze the performance of SQL statements

Analyze the statement performance and index usage through Toad or PL/SQL Developer

Hint can be forcibly used if you are not satisfied with Oracle's default optimization, but it is not recommended.

It is generally not recommended to create an index for fields such as Flag that only store yes or no information. Bitmap indexes are required.

If there is a recursive query, too many associated tables will have a great impact on the performance, it is often recommended to use a temporary Table into a step-by-step operation to improve performance

Try to use table join queries instead of using functions, but it is suitable for use when it involves a code table that needs to be repeatedly joined to retrieve data for multiple times.

I have explained in detail the key points of optimizing the stored procedures and SQL statements in Oracle. I hope everyone can gain some benefits from this, similar problems can be easily solved in future work.