1 Avoid unplanned full mete scan
Full-mesh scanning as follows:
- This table has no index
- Returned unintendible and restricted conditions (no where clause)
- No restrictions on index primary column (the first column of index)
- The conditions for the main columns of the index are included in the expression.
- The restriction condition for the main column of the index is IS (not) null or! =
- The restriction condition for the main column of the index is that the value of the LIKE is a value of a bind variable or% head.
2 Use the selective index
The selectivity of the index is that the ratio of the number of records records in the number of records in the column, and the selectivity is the only index of non-empty columns to 1.0.
The order of the order in the composite index:
1 The most frequently used columns in limited conditions should be the master column
2 The most selective column (that is, the clearest column) should be the master column
If 1 and 2 are inconsistent, consider establishing multiple indexes.
Select in a composite index and multiple individual indexes:
Consider selecting the number of times of reading indexes Consider and-equal operation
3 Manage Multi-Table Connections (NESTED LOOPS, MERGE JOINS, and Hash Joins) Optimized Joint Operation
Merge Joins is a collection operation NESTED LOOPS and Hash Joins is the record operation returns the first batch of records
Merge Join operates for batch operations, huge tables and remote queries
1 full table scan -> 2 Sorting -> 3 Comparison and consolidation performance overhead mainly two steps
Applicable to the full surface scan, all apply Merge Joins operations (which is valid than NESTED LOOPS).
Improve 1 efficiency: Optimize I / O, improve the ability to use Oracle multiple readings, use parallel query options
Improve 1 efficiency: Improve the value of sort_area_size, use Sort Direct Writes to provide dedicated table space for the temporal session
4 Manage SQL statements that contain views
There are two ways to perform the SQL statement containing the view:
- Execute the view first, complete all the result set, then use the rest of the query criteria to perform the query
- Integrate view text to the query
The view containing the Group By clause cannot be integrated into a large query.
Using Union in the view, the SQL integration of the view does not prevent the SQL integration into the syntax of the query.
5 optimized subqueries
6 use compound keys / star inquiry
7 Appropriate index connect BY operation
8 Restrict access to remote tables
9 Visit very huge giant table
- Administrative Data Proximats Record Sedon in the table in the table Sort by column scan in the table Schemes Supreme storage data helps range scan, especially for large tables.
- Avoid unhealthful index scanning When the data set returned is large, use the index to the SGA's data block cache, affecting other users; full table scans can also get from Oracle's multi-block read mechanisms and "consistency acquisition / Each "feature benefits.
- Create a fully indexed table enable access index to read more comprehensive data to establish multiple indexes of different columns
- Create a Hash cluster
- Create segmentation tables and views
- Using parallel options
10 Use Union All instead of Union
UNION ALL operation does not include Sort UNIQUE operations, the first row retrieval is fast, and in most cases, the operation is not used.
The view established by UNION ALL can be integrated into the syntax of the query to improve efficiency in the query.
11 Avoid using PL / SQL function calls in SQL
12 Bind Variable
Use Bind Variable and Execute Using way to change Like: Name || '%' to Between: Name and: Name || CHAR (225), has avoided full mete scan, but uses an index.
13 return visit optimization process
After the data changes, re-examine the optimization
