Oracle SQL statement optimization, oraclesql statement

Oracle SQL statement optimization, oraclesql statement

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 and from bottom to top.

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.

We also recommend some good links:

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

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

Zookeeper
Oracle SQL statement Optimization

We recommend that you partition the table and create a partition index if the data volume of the table is large and you need to perform such queries frequently.
Your SQL statement is simplified, and the amount of data is large. It will be very slow no matter how you tune it.
This is usually because the application requirements are not taken into account when designing tables.

SQL statement optimization oracle

Select/* + leading (a) use_hash (B) */a. a1, a. a2,..., B. name
From ht. hupat a, B
Where a. no = B. no (+)

Try this. The target is to perform full table scan for table a first. Because you have no conditions, the full table scan is the most efficient.
Then B also performs a full table scan. B should be a code table. Table a should be a large table, and each record has too many times of nested loop queries to table B, resulting in a high cost. Therefore, the whole table scans table B,
Finally, perform a hash connection.
You can check the execution plan and get the execution plan described above. Otherwise, you can adjust the hint to obtain the appropriate execution plan.