Oracle Common SQL Optimization hint statements

Http://www.cnblogs.com/sopost/archive/2010/10/11/2190076.html in the SQL statement optimization process, we often use the hint, now summarize the SQL optimization process common Oracle Usage of hint:

1./*+all_rows*/

Indicates that the cost-based optimization method is selected for the statement block, and the optimal throughput is obtained, minimizing the resource consumption.

For example:

SELECT/*+all+_rows*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

2./*+first_rows*/

This indicates that the cost-based optimization method is selected for the statement block, and the optimal response time is obtained, minimizing the resource consumption.

For example:

SELECT/*+first_rows*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

3./*+choose*/

Indicates that if the data dictionary has access to the table statistics, the cost-based optimization method is used, and the optimal throughput is obtained;

Indicates that if the data dictionary does not have access to the table statistics, the optimization method based on the rule cost is used;

For example:

SELECT/*+choose*/emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

4./*+rule*/

Indicates that the rule-based optimization method is selected for the statement block.

For example:

SELECT/*+ RULE */emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

5./*+full (TABLE) */

Indicates how to select a global scan for a table.

For example:

SELECT/*+full (a) */Emp_no,emp_nam from Bsempms A WHERE emp_no= ' SCOTT ';

6./*+rowid (TABLE) */

The prompt explicitly indicates that the specified table is accessed according to ROWID.

For example:

SELECT/*+rowid (BSEMPMS) */* from Bsempms WHERE rowid>= ' aaaaaaaaaaaaaa '

and emp_no= ' SCOTT ';

7./*+cluster (TABLE) */

The prompt explicitly indicates the access method to select a cluster scan for the specified table, which is only valid for cluster objects.

For example:

SELECT/*+cluster */Bsempms. Emp_no,dpt_no from Bsempms,bsdptms

WHERE dpt_no= ' Tec304′and Bsempms. Dpt_no=bsdptms. Dpt_no;

8./*+index (TABLE index_name) */

Indicates the scan method for the table selection index.

For example:

SELECT/*+index (Bsempms sex_index) use Sex_index BECAUSE there is fewmale Bsempms * * from Bsempms WHERE sex= ' M ';

9./*+INDEX_ASC (TABLE index_name) */

Indicates the scan method for the table selection index ascending.

For example:

SELECT/*+index_asc (Bsempms Pk_bsempms) */from Bsempms WHERE dpt_no= ' SCOTT ';

Ten./*+index_combine*/

Selects a bitmap access path for the specified table, and if no index is provided as a parameter in Index_combine, the Boolean combination of bitmap indexes is selected.

For example:

SELECT/*+index_combine (Bsempms sal_bmi hiredate_bmi) */* from BSEMPMS

WHERE sal<5000000 and HireDate

/*+index_join (TABLE index_name) */

Prompts the explicit command optimizer to use the index as the access path.

For example:

SELECT/*+index_join (Bsempms sal_hmi hiredate_bmi) */sal,hiredate

From Bsempms WHERE sal<60000;

/*+index_desc (TABLE index_name) */

Indicates the scan method for descending the table selection index.

For example:

SELECT/*+index_desc (Bsempms Pk_bsempms) */from Bsempms WHERE dpt_no= ' SCOTT ';

/*+index_ffs (TABLE index_name) */

Performs a fast full-index scan of the specified table instead of a full-table scan.

For example:

SELECT/*+index_ffs (Bsempms in_empnam) */* from Bsempms WHERE dpt_no= ' TEC305 ';

/*+add_equal TABLE index_nam1,index_nam2,... */

Prompts for a clear selection of execution plans, combining several single-column index scans.

For example:

SELECT/*+index_ffs (Bsempms in_dptno,in_empno,in_sex) */* from Bsempms WHERE emp_no= ' SCOTT ' and dpt_no= ' TDC306 ';

/*+use_concat*/.

A combined query that converts the or condition after the where in the query to union ALL.

For example:

SELECT/*+use_concat*/* from Bsempms WHERE dpt_no= ' TDC506 ' and sex= ' M ';

/*+no_expand*/.

For query statements that are followed by or or in-list, No_expand prevents them from being extended based on the optimizer.

For example:

SELECT/*+no_expand*/* from Bsempms WHERE dpt_no= ' TDC506 ' and sex= ' M ';

/*+nowrite*/.

Suppresses query rewrite operations on query blocks.

/*+rewrite*/.

You can use a view as an argument.

/*+merge (TABLE) */

Be able to merge the individual queries of the view accordingly.

For example:

SELECT/*+merge (V) */a.emp_no,a.emp_nam,b.dpt_no from Bsempms A (Selet dpt_no

, AVG (SAL) as avg_sal from Bsempms B GROUP by Dpt_no) V WHERE a.dpt_no=v.dpt_no

and a.sal>v.avg_sal;

/*+no_merge (TABLE) */

Views that have a consolidated view are no longer merged.

For example:

Select/*+no_merge (V) */a.emp_no,a.emp_nam,b.dpt_no from Bsempms A (SELECT dpt_no,avg (SAL) as avg_sal from Bsempms B Grou P by Dpt_no) V WHERE a.dpt_no=v.dpt_no and a.sal>v.avg_sal;

/*+ordered*/.

Based on the order in which the table appears in from, ordered enables Oracle to connect to it in this order.

For example:

SELECT/*+ordered*/a.col1,b.col2,c.col3 from TABLE1 a,table2 b,table3 C WHERE a.col1=b.col1 and b.col1=c.col1;

/*+USE_NL (TABLE) */

Joins the specified table with the row source of the nested connection and takes the specified table as an internal table.

For example:

SELECT/*+ordered use_nl (BSEMPMS) */BSDPTMS. Dpt_no,bsempms. Emp_no,bsempms. Emp_nam from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;

/*+use_merge (TABLE) */

Joins the specified table with other row sources through a merge sort connection.

For example:

SELECT/*+use_merge (BSEMPMS,BSDPTMS) */* from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;

/*+use_hash (TABLE) */

Connects the specified table to another row source by hashing the connection.

For example:

SELECT/*+use_hash (BSEMPMS,BSDPTMS) */* from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;

/*+driving_site (TABLE) */

Enforces query execution on tables that are different from the location selected by Oracle.

For example:

SELECT/*+driving_site (DEPT) */* from Bsempms,[email protected] WHERE Bsempms. Dpt_no=dept. Dpt_no;

/*+leading (TABLE) */

Takes the specified table as the first table in the join order.

/*+cache (TABLE) */

When a full table scan is performed, the cache hint is able to place the retrieved block of the table in the buffer cache most recently used in the least recent list of LRU

For example:

SELECT/*+full (BSEMPMS) Cahe (BSEMPMS) */Emp_nam from Bsempms;

/*+nocache (TABLE) */

When a full table scan is performed, the cache hint is able to place the retrieved block of the table in the buffer cache most recently used in the least recent list of LRU

For example:

SELECT/*+full (BSEMPMS) Nocahe (BSEMPMS) */Emp_nam from Bsempms;

/*+append*/.

You can increase the speed by inserting directly to the end of the table.

Insert/*+append*/to test1 select * from Test4;

/*+noappend*/.

Start a regular insert by stopping the parallel mode during the INSERT statement lifetime.

Insert/*+noappend*/to test1 select * from Test4;

----------------------------------------------------------------------------

Optimization approaches	Access Methods
All_rows	And_equal
CHOOSE	CLUSTER
First RULES	Full
RULE	HASH
Parallel execution	Hash_aj
Append*ordered	HASH_SJ * * *
star**	INDEX
star_transformation*	Index_asc
Join Operations	index_combine*
driving_site*	Index_desc
use_hash**	index_ffs*
Use_merge	merge_aj**
Use_nl	merge_sj***
Additional Hints	row_id
CACHE	Use_concat
NOCACHE	no_expand***
Push_subq	rewrite***
merge***	norewrite***
no_merge*	Join Orders
push_join_pred***
no_push_join_pred***	noappend*
ORDERED predicates***	Noparallel
	PARALLEL
	parallel_index*
	no_parallel_index***

Hint (hint) was introduced from the Oracle7 to compensate for the defect based on the cost optimizer. Hints are often used to change the SQL execution plan to improve execution efficiency.

1. Guidelines to follow when using hints

1) Carefully check the prompt syntax. Try to use the full annotation syntax/*+ hint */

2) Use table aliases. If a table alias is specified in the query, the hint must also use the table alias. For example: Select/*+ Index (E,DEPT_IDX) */* from EMP E;

3) Do not use the pattern name in the prompt: If the owner of the pattern is specified in the prompt, the hint is ignored. For example:

Select/*+ Index (SCOTT.EMP,DEPT_IDX) */* from EMP;

4) Inspection tips. If the prompt specifies an access path that is not available, the hint is ignored.

2. Conditions that cause the prompt to be invalid:

Tips	Ignored condition
Cluster	Use with non-clustered tables
Hash	Use with non-clustered tables
Hash_aj	No subquery exists
Index	The specified index does not exist
Index_combine	Bitmap index does not exist
Merge_aj	No subquery exists
Parallel	The call is not a TABLE ACCESS full plan
Push_subq	No subquery exists
Star	Inappropriate indexes in the fact table
Use_concat	multiple or conditions do not exist in the WHERE clause
Use_nl	Index does not exist in table

3 Main optimization modes:

1) all_rows:all_rows is a cost-based optimization approach designed to provide the best overall throughput and minimum resource consumption. The all_rows tip tends to use full-table scanning and is not suitable for OLTP databases. Use the ALL_ROWS hint to ensure that the tables and indexes involved in the query have statistics that are analyzed using the Analyze command.

2) Rule:rule hints enable Oracle to provide a rule-based optimization pattern for queries. When you suspect that a CBO has generated a non-optimized execution plan, you typically first try to use the rule hint. The rule hint ignores the statistics for tables and indexes and uses the basic heuristics to generate the execution plan.

3) First_rows: This tip is a cost-based optimization method designed to provide the fastest response time. Use the FIRST_ROWS hint to ensure that the tables and indexes involved in the query have statistics that are analyzed using the Analyze command.

4. Connection hints for tables

1) Use_hash Tips

Use_hash prompts for a hash connection to the specified table. A hash join is a method that Oracle uses to drive a table (the smallest table) to load records into the Ram area, which is defined by Hash_area_size. Hash joins are suitable for cases where the intermediate result is relatively large. With Hash joins, the hash_area_size has a very large impact on speed, and if the driver table cannot be loaded into memory at one time, then the temp table space is used, which is slower. This parameter can be dynamically modified at the session level and can be increased temporarily when a hash connection is required, and the speed may increase significantly.

2) Use_merge Tips

Use_merge prompt to force a sort merge operation. A sort merge operation is typically used in conjunction with a parallel query because the sort merge operation tends to be a full table scan. This hint is appropriate for queries that generate large result sets.

3) Use_nl:

The USE_NL hint forces a nested loop join to be performed on the target table. Use_nl hints are rarely used for SQL tuning because CBO and rbo prefer to use circular nesting connections.

4) Star Tips

The star hint enforces the use of a Stars query plan. The premise is that at least three tables in the query, and that there are proper indexes in the fact table

5. Table Anti-Connection tips

SQL anti-join refers to the action that is performed when a statement contains a not or not EXISTS clause.

1) Merge_aj

When using full table access to peso access better, you can use the MERGE_AJ hint in a not in subquery to perform a reverse connection.

2) Hash_aj

The HASH_AJ hint performs a hash-back join when it is used in a not in subquery to want to perform a hash join.

Hash_aj and MERGE_AJ require subquery columns to be non-empty.

6INDEX Tips

1) Index hint Introduction:

The index hint is used to display the specified table name or table name with indexes. If you specify only the table name, the optimizer uses the "best" index in the table. In a permanently optimized SQL statement, it is recommended that you specify tables and indexes.

2) Index_join Tips

The INDEX_JOIN hint explicitly requires the optimizer to use an indexed connection as the access path.

3) And_equal Tips

And_equal hints You can combine indexes with multiple non-unique indexes, and make these index operations as if they were a single continuous index. If the hint is applied, the and-equal is displayed in the query plan

4) Index_asc Tips

INDEX_ASC hints use ascending index. This is the default optimizer behavior

5) No_index Tips

The hint ignores the existence of an index, similar to full

6) Index_combine Tips

The Index_combine hint is used to force the bitmap index to be used as the access path to the table.

7) Index_ffs Tips

An index fast full scan can complete a query without accessing any records.

8) Use_concat Tips

The Use_concat prompt requires that the union all execution plan be used for all or conditions and that the query be re-written as multiple queries. If there are a large number of or conditions in the WHERE clause, you might consider using the USE_CONCAT hint.

7. Summary

1) Because the hint is placed in the comment, it may be ignored if the prompt is not compatible with the existing execution plan, or if the prompt is incorrect.

2) When using Rbo, you can change the specified query to a CBO by prompting. Remember to analyze all the tables and indexes involved in the query

3) When using the CBO, you can start by adding a rule hint or first_rows hint to begin adjusting a SQL statement that you can

4) The hint can be used in a subquery, but the hint of an external query is not brought into the subquery.

5) If you find the Cartesian product (Cartesian) in the query plan, try to solve it.

Oracle Common SQL Optimization hint statements