Oracle's SQL statement optimization

Although the ORM mapping is used in the project, the optimization of the Oracle system is very valuable, and here is a summary of the frequently used statements from the perspective of the SQL statement.

1. Optimizations in the From field:
Oracle Yasuteru Loading table data from right to left, you should put the table that can exclude the most data to the back (the underlying table).

Example. In the associated query. Put the curriculum in the back. The score table is put in front. Because the curriculum data is generally relatively small, associated with the time can be high-speed filtering out some of the performance data.

2, where the optimization:
For the most filtered data, the principle is the order in which Oracle runs from bottom to top (right-to-left).

3. Use the column name instead of *:

Omit the process of parsing from the dictionary table:
Oracle runs the SQL statement procedure:
Whether the search for SQL statements in the shared pool already exists
Verify that SQL is syntactically accurate
Run the Data Dictionary validation table and column definitions
Gets the parse lock for 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 for the operation
Determine statement best run plan
Save the statement and the run scenario to a shared SQL area.

4, use? form of participation:
Try not to use the concatenation string. As with? The result of the analysis is cached by the number of parameters. Omit a very many steps above.

5. Use the Decode function:
Decode does not repeatedly scan the same record or repeatedly joins the same table, reducing the number of scans on the table.

6. Replace Delete with truncate:
Delete: Remove each. and save 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 is capable of rollback, and DDL is not able to

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

7. Use commit as much as possible:
Oracle commits only after a commit (different from SQL Server). Assuming there is no commit, very much data is saved 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 the data is filtered out directly. The use of having will often match group. After retrieving the data, it will be sorted, counted and so on with the data.
9, on/where/having Order use:
On: Doing is a data map, when 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 same 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 special 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:

Reduces the time to parse SQL statements and reduces syntax errors caused by the same column name ambiguity of multiple tables
Assuming that you do not use aliases, you look in the dictionary table to infer whether there is a column name ambiguity.

12. Replace not exists with exists instead of in:
Oracle uses hit-to-return mode. When multi-link queries are used, it is assumed that using in causes the full table traversal of the table in the subquery, and sorts, merges, which can be substituted using an outer or not exists.

13, identify the low-efficiency operation of the statement:
A variety of graphical tools for SQL optimization abound. But being able to write your own SQL tools to solve this 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:

The table used for the query. Provides uniqueness validation of primary keys, long or long raw data type, nearly able to index all columns
Periodically rebuild the index, deleting and altering many tables. The index is not applicable. Reason, not explained.

Note that the index is not necessarily taken after the index is created. When using an index, you cannot use the index for some calculations. otherwise invalid
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 includes 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 what is recorded
For single-column indexes. is not indexed
For compliant indexes. Assuming that all columns are empty and not indexed, only one column is not empty. On the index.

The Oracle Hollow is not equal to NULL, so a number of records with the same key values are inserted. And their values are empty, and null values are not indexed, so when a null value is compared. will use Oracle. 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:

Assume that the index is built on more than one column. There was only one of his first columns. When referenced by where, the optimizer chooses to use the index, and when the second column with only the index is used, the optimizer ignores the index and uses the full table index.

15, >= is direct positioning:

It is assumed that the use of > requires an inference process.
16. Use union instead of the or in the WHERE clause:
Using or with an indexed column causes a full table scan. Valid for multiple indexed columns

17, under the Oracle8i, both the running path seems the same, but can use in the Do not use or.
18. Using Union-all:

Union-all: Do not sort, query all, just filter repeatedly
Nuion: Sort (optimize sort_raea_size this block of memory), filter repeatedly.
19. Order BY with where limit:

The condition of index in order by name is more stringent
The sequence of rows must be included in the same index, and the order of Arrangement in the index is maintained. All columns in Order by cannot be defined as empty.

20. Where clause to be careful:
! = will not use the index
|| The 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. When querying a table name, infer whether a table exists. If uppercase is valid, lowercase is invalid.

22, according to the disk read-write rate adjustment block size:

An Oracle database. The concept of table spaces, segments, extents, and blocks of data that can resize blocks based on the I/O performance of the server.

Summarize:

The points above are very thin, they are based on the internal principles of Oracle summed up the regular use of law, so it is more important to master the principle than to remember these jumping frames. Frequently used rules are:

Oracle runs in order from right to left, bottom to top

When retrieving data, the principle of return is often followed

index, which data is recorded, so. Do not use queries that exclude non-existent conditions directly on the index column, and do not calculate on the index

Oracle feels empty is not equal to NULL

Where the chance to hit the index is relatively high, usually. Prefer to use where.

Some good links are also recommended:

http://blog.csdn.net/tianlesoftware/article/details/7008801

http://blog.csdn.net/lk_blog/article/details/7585540



Oracle's SQL statement optimization