Oracle SQL statement Optimization

Oracle SQL statement Optimization

Although orm ing is used in the project, the optimization of the Oracle system is very valuable. Here we will summarize the commonly used statements from the perspective of SQL statements.

1. Optimization in the from field:

Oracle installs table data from the right to the left, and puts the tables with the most excluded data to the back (basic table ).

For example, in associated queries, the curriculum is put behind and the curriculum table is put ahead, because the curriculum data is usually relatively small, and some score data can be quickly filtered out during Association.

2. Optimization in where:

Put the most data that can be filtered to the back. The principle is that Oracle executes the order from bottom to top (from right to left.

3. Use column name instead *:

The process of parsing from a dictionary table is omitted:

Oracle SQL statement execution process:

Search for an SQL statement in the Shared Pool

Verify SQL syntax Accuracy

Execute a data dictionary to verify the definition of the table and Column

Obtain the analysis lock of the object so that the object definition does not change during the analysis of the statement.

Check whether the user has the corresponding operation Permissions

Determine the statement execution plan

Save the statement and execution plan to the shared SQL zone.

4. Use? Parameter format:

Try not to use the concatenation string method, because there is? The analysis results are cached in the form of parameters, saving you from the preceding steps.

5. Use the decode function:

Decode does not scan the same record repeatedly or reconnects to the same table to reduce the number of table scans.

6. Replace delete with truncate:

Delete: delete one by one and save the rollback Restore Point. DML (data operation language: insert, update, delete, merge)

Truncate does not save the rollback point. It is a DDL (data definition language, such as drop and alter)

In oracle, DML can be rollback, but DDL cannot.

If the data volume is large, the difference is great.

7. Use commit as much as possible:

Oracle will only submit (different from sqlserver) after commit. If it is not submitted, it will save a lot of data in the memory. Resources released after commit are:

Rollback the records used to restore the database in the previous section

Lock obtained by Program Statements

Space in Redo log buffer

To manage the internal costs of the above three types of resources

8. Replace having clause with the where clause:

Having is used in grouping or group combination.

Where directly filters out the data. having is often used in combination with the group. After the data is retrieved, it sorts and counts the data.

9. Order of on/where/having sorting:

On: Data ing is used to directly filter out useless data during ing.

Where: first perform a full table search and then perform data filtering.

Where is faster than having, and the junk data in having is also computed.

10. Reduce table queries:

In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table.

Select tab_name from tables where (tab_name, db_ver) = (select tab_name, db_ver from tab_columns where version = 604)

11. Use the table alias:

Reduces SQL statement parsing time and syntax errors caused by the same Column name ambiguity in multiple tables

If you do not use an alias, you can go to the dictionary table to check whether the column name is ambiguous.

12. Replace in not exists with exists instead of not in:

Oracle uses the hit-and-return method. in multi-Table link queries, if in is used, the full table traversal of the subquery will be performed, sorted, and merged, in this case, you can use external links or not exists instead.

13. Identify statements with low execution efficiency:

Graphic tools for SQL optimization emerge one after another, but you can write your own SQL tools to solve the problem.

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;

14. Indexing:

The table used for query provides the uniqueness verification of the primary key. The long or long raw data type can be used to index almost all columns.

The index is re-built on a regular basis. The index is not applicable when multiple tables are deleted or modified. The reason is not explained.

Note that the index will not be taken after the index is created. When using the index, you cannot use the index for some calculations. Otherwise, the index will become invalid.

Using indexes is faster than full table scanning, and using indexes in Multi-Table connections improves efficiency.

Avoid using not on indexes:

Will stop the index (not indicates: nothing, but the index indicates: What)

Replace distinct with exitsts:

It is used when you submit a query that contains one-to-multiple table information (department and employee tables. Cause: search is the return principle

Avoid using computation on index columns:

Inefficiency:

Select * from dept where sal * 12> 25000

Efficient:

Select * from dept where sal> 25000/12

Avoid using is null and is not null in the index column:

Because the index does not index the null data type, the index only records

Single-Column indexes are not indexed.

If all columns are null and no index is performed, the index is performed if one column is not empty.

Null is not equal to null in Oracle, so several records with the same key value are inserted, and their values are empty, while null values are not indexed, oracle is used for comparison of null values and the index is stopped.

Inefficiency:

Select .... From dep where dep_code is not null;

Efficient:

Select .... From dep where dep_code> = 0; the null column is also filtered out.

Always use the first column of the index:

If an index is created on multiple columns, the optimizer selects this index only when its first column is referenced by the where clause. When only the second column of the index is used, the optimizer ignores the index and uses the full table index.

15.> = direct positioning:

If you use it, you also need a judgment process.

16. Use union to replace or in the where clause:

Using or for index columns will cause full table scans, which are effective for multiple index Columns

17. in oracle8i, the two execution paths seem to be the same, but do not use or if they can use in.

18. Use union-all:

Union-all: Non-sorting, query all, do not filter duplicate

Nuion: Sort (optimized sort_raea_size memory) to filter duplicate.

19. Use where to limit order:

The index conditions in order by names are harsh.

Sort columns must be included in the same index and keep the Order in the index. All columns in Order by cannot be empty.

20. where clause to be careful:

! = No index will be used

| The character concatenation function disables the index.

+-*/The index is disabled.

The same index Columns cannot be compared with each other; otherwise, full table scan is enabled.

21. Use uppercase letters:

In Oracle, all statements are converted to uppercase,

Some internal tables, for example, determine whether a table exists when querying the table name. If it is valid in upper case, it is invalid in lower case.

22. Adjust the Block Size Based on the disk read/write rate:

In an Oracle database, the concept of tablespace, segment, partition, and data block can be adjusted based on the I/O performance of the server.

Summary:

The points above are very detailed. They are all common rules based on the internal principles of Oracle. Therefore, it is more important to master the principles than to remember these jump frameworks. Common rules are as follows:

Oracle follows the execution sequence from right to left, from bottom to top, from outside to inside

When retrieving data, we often follow the principle of "return ".

The index records the data. Therefore, do not directly use queries that exclude non-existent conditions on the index column, or perform computation on the index.

Oracle considers empty as not empty

The where statement has a high probability of index hit. Generally, the where statement is preferred.

Understanding ArcSDE indexes by Oracle Indexes

How to create an optimal index using Oracle Indexing Technology

Test example of the table where the NULL value of the Oracle index column triggers the execution plan

Oracle index primary key affects query speed

Oracle index Scanning