Oracle's SQL statement optimization

Although the ORM mapping is used in the project, the optimization of the Oracle system is valuable, which summarizes the commonly used statements from the perspective of SQL statements.

1. Optimizations in the From field:

Oracle Yasuteru Load table data from right to left, and the tables that can exclude the most data should be placed behind (the underlying table).

For example, in the context of the query, put the curriculum back, the score table in front, because the curriculum data is generally relatively small, associated with the time can quickly filter out some of the performance data.

2, where the optimization:

For the most data that can be filtered, the principle is the order in which Oracle executes from bottom to top (right to left).

3. Use the column name instead of *:

Omit the process of parsing from the dictionary table:

Oracle execution SQL statement procedure:

Whether the search for SQL statements in the shared pool already exists

Verify that SQL is syntactically accurate

To perform a data dictionary validation table and column definition

Gets the parse lock of the object so that the definition of the object does not change during the parsing of the statement

Check if the user has appropriate permissions to operate

Determine statement best execution plan

Saves the statement and execution scheme to the shared SQL area.

4, use? Parameter form:

Try not to use the concatenation of strings, because with? The Parameter form caches the analysis results, eliminating many of the above steps.

5. Use the Decode function:

Decode does not repeatedly scan the same record or repeatedly connect to the same table, reducing the number of scans on the table.

6. Replace Delete with truncate:

Delete: Deletes each and saves the rollback restore point. DML (Data Manipulation language Insert,update,delete,merge)

Truncate does not save rollback point, is DDL (data definition language, such as Drop,alter)

In Oracle, DML can be rollback, and DDL is not.

If the amount of data is large, the difference is very large.

7. Use commit as much as possible:

Oracle commits only after commit (as distinct from SQL Server), and if not committed, it saves a lot of data in memory. The resources released after commit are:

Rollback the record information on the previous segment for recovering a database

Locks obtained by program statements

Redo space in the log buffer

To manage the internal costs of the above 3 resources

8. Use the WHERE statement to replace the HAVING clause:

When grouping or cooperating with group, you will use having.

Where will directly filter out the data, the use of having often cooperate with group, retrieve data, will take the data to sort, statistics and so on.

9, on/where/having Order use:

On: Do the data mapping, when the mapping, the useless data directly filtered out

Where: A full-table search is performed before data filtering

Where is faster than having to have the garbage data in the same also do the calculation

10, reduce the query of the table:

In the SQL statement that contains the subquery, pay particular attention to reducing the query on 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 Table Aliases:

Reduce the time to parse SQL statements and reduce syntax errors due to the same column name ambiguity as multiple tables

If you do not use aliases, you will look in the dictionary table to determine if there is a column name ambiguity.

12. Replace not exists with exists instead of in:

Oracle uses a hit-to-return approach, when a multi-link query is used, if you use in to cause the full table traversal of the table in the subquery, and sort, merge, this time you can use the outer links or not exists overrides.

13. Identify low-efficiency execution statements:

A variety of SQL-optimized graphics tools abound, 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. Use Index:

Table used for querying, provides uniqueness validation for primary key, long or long raw data type, almost all columns can be indexed

Periodically rebuilding the index, in deleting and modifying many tables, does not apply the index, reason, does not explain.

Note that the index does not have to be indexed after the index is created, and it is not possible to use the index for some calculations, otherwise it will fail

Using indexes faster than full table scans, using indexes in multi-table joins increases efficiency.

Avoid using not on the index:

Stops the index (not means: Nothing, and the index represents: what)

Replace distinct with exitsts:

used when submitting a query that contains one-to-many table information (Department and employee tables). Reason: Lookup is the principle of return

To avoid using calculations on indexed columns:

Low efficiency:

Select * FROM dept where Sal * >25000

Efficient:

Select * FROM dept where sal> 25000/12

Avoid using is null and is not NULL on an indexed column:

Because the index does not index the null data type, the index is just a record of what

Not indexed for single-column indexes

For qualifying indexes, if all the columns are empty, the index is not indexed, as long as there is a column that is not empty.

The Oracle Hollow is not equal to NULL, so a number of records with the same key value are inserted, and their values are empty, and the null value is not indexed, so when a null comparison is made, Oracle is used to stop using the index.

Low efficiency:

Select .... From DEP where dep_code are NOT null;

Efficient:

Select .... From DEP where Dep_code >=0; also filters out null columns

Always use the first column of an index:

If the index is based on more than one column, the optimizer chooses to use the index only if his first column is referenced by where, and when only the second column of the index is used, the optimizer ignores the index and uses the full table index.

15, >= is direct positioning:

If you use > you need a process of judgment.

16. Use union instead of the or in the WHERE clause:

Using or with an indexed column results in a full table scan and is valid for multiple indexed columns

17, under Oracle8i, the two execution paths seem to be the same, but can use in the Do not use or.

18. Using Union-all:

Union-all: Do not sort, query all, do not filter duplicate

Nuion: Sort (optimize sort_raea_size this memory), filter duplicate.

19. Order BY with where limit:

Index in order by name is more demanding

The row sequence must be contained in the same index and keep the order in the index, and all columns in the order by cannot be defined as empty.

20. Where clause to be careful:

! = will not use the index

|| Character join function, the index is deactivated

+-*/Deactivate Index

The same index columns cannot be compared to each other, otherwise full table scanning is enabled

21. Use Uppercase:

All statements are converted to uppercase in Oracle,

Some internal tables, such as querying table names, determine if a table exists, and if uppercase is valid, lowercase is invalid

22. Adjust the block size according to the disk read and write rate:

In an Oracle database, the concept of table space, segments, extents, and blocks of data can be resized based on the server's I/O performance.

Summary :

The points above are very thin, they are based on the internal principles of Oracle summed up the common rules, so, mastering the principle is more important than remembering these jumping frames, commonly used rules are:

Oracle execution order from right to left, bottom to top, from outside to inside

When retrieving data, the principle of return is often followed

Index, which records what data, so do not directly use queries that exclude non-existent conditions on indexed columns, and do not calculate on indexes

Oracle considers null not equal to NULL

Where the chance to hit the index is higher, typically, the preferred option is to use where.

Understanding ARCSDE Index http://www.linuxidc.com/Linux/2012-10/72184.htm by Oracle Index

How Oracle Indexing Technology builds the best index http://www.linuxidc.com/Linux/2012-09/70996.htm

Oracle index column NULL value raises execution plan test example for this table http://www.linuxidc.com/Linux/2012-09/69938.htm

Oracle Index primary key affects query speed http://www.linuxidc.com/Linux/2011-12/48588.htm

Oracle Index Scan http://www.linuxidc.com/Linux/2012-03/56644.htm

See Oracle Feature page for more information on Oracle HTTP://WWW.LINUXIDC.COM/TOPICNEWS.ASPX?TID=12

This article permanently updates the link address : http://www.linuxidc.com/Linux/2014-08/106005.htm

Oracle's SQL statement optimization