30 usages of hint in Oracle

During the SQL statement optimization process, hint is often used,

The following are 30 uses of "HINT" in Oracle Common in SQL optimization

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< Sysdate;

/*+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= ' te junk product ';

/*+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;

This article is from "Allen Lee Oracle" blog, please make sure to keep this source http://lipengfei666666.blog.51cto.com/6384154/1641489

30 usages of hint in Oracle