Optimization techniques of SQL statements in Oracle database _oracle

In the SQL statement optimization process, we often use hint, and now summarize the use of Oracle hint in the SQL optimization process:

1./*+all_rows*/

It is shown that the cost-based optimization method is chosen for the statement block, and the optimal throughput is achieved to minimize resource consumption.

For example:

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

2./*+first_rows*/

It is shown that the cost-based optimization method is chosen for the statement block, and the optimal response time is obtained to minimize 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 statistical information of the table, it will be based on the cost optimization method and obtain the best throughput;

Indicates that if there is no statistical information in the data dictionary to access the table, an optimization method based on rule cost is proposed.

For example:

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

4./*+rule*/

It shows that the rule-based optimization method is chosen for the statement block.

For example:

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

5./*+full (TABLE) * *

Indicates a method for selecting a global scan on 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) * *

Tip explicitly indicates the access method that selects a cluster scan for a specified table, which is only valid for a cluster object.

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 scanning method for selecting indexes on a table.

For example:

SELECT/*+index (Bsempms sex_index) use sex_index because THERE ARE fewmale the Bsempms/from Bsempms WHERE sex= ' M ';

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

Indicates a scan method that selects index ascending for the table.

For example:

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

/*+index_combine*/

Select a bitmap access path for the specified table, and select a Boolean combination of bitmap indexes if Index_combine does not provide an index as a parameter.

For example:

SELECT/*+index_combine (Bsempms sal_bmi hiredate_bmi) * from Bsempms 
WHERE sal< 5000000 and hiredate< SYSDATE ;

/*+index_join (TABLE index_name) * *

Prompt to explicitly command the 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 that selects the index descending from the table.

For example:

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

/*+index_ffs (TABLE index_name) * *

Performs a quick full index scan of the specified table, rather than 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,... * *

It is indicated that the selection of execution plan is clear, and the scans of several Single-column indexes are combined.

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 behind a where in the query to union ALL.

For example:

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

/*+no_expand*/

For a query statement that is followed by or or in-list, No_expand prevents it from expanding based on the optimizer.

For example:

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

/*+nowrite*/

Prevents query rewrite operations on query blocks.

/*+rewrite*/

You can use a view as a parameter.

/*+merge (TABLE) * *

It is possible to merge the individual queries on 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 and Bsempms B GRO Up by Dpt_no) V WHERE a.dpt_no=v.dpt_no and 
a.sal>v.avg_sal;

/*+no_merge (TABLE) * *

For views that can be merged, they 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 to Bsempms B Grou P by Dpt_no) V WHERE a.dpt_no=v.dpt_no and a.sal>v.avg_sal;

/*+ordered*/.

Depending 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) * *

Connects the specified table to 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 by merging the sort connections.

For example:

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

/*+use_hash (TABLE) * *

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

For example:

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

/*+driving_site (TABLE) * *

Force query execution on tables that are different from the location chosen by Oracle.

For example:

SELECT/*+driving_site (DEPT) * * from Bsempms,dept@bsdptms 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 can place the retrieved block of the table in the buffer cache with the most recent LRU of the least list

For example:

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

/*+nocache (TABLE) * *

When a full table scan is performed, the cache hint can place the retrieved block of the table in the buffer cache with the most recent LRU of the least list

For example:

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

/*+append*/.

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

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

/*+noappend*/.

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

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

The above is a small set to introduce the Oracle database in the optimization of SQL statements techniques, hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!