Before giving you a detailed introduction to Oracle Hints, let us know what Oracle Hints is and then fully introduce Oracle Hints, which we hope will be useful to you. The cost-based optimizer is smart, and in most cases it chooses the right optimizer, reducing the burden on the DBA. But sometimes it's smart and smart, and you choose a poor execution plan that makes execution of a statement incredibly slow.
The DBA is required to intervene manually, telling the optimizer to generate the execution plan using the Access path or connection type that we specify, so that the statement runs efficiently. For example, if we think that performing a full table scan is more efficient than performing an index scan for a particular statement, we can instruct the optimizer to use a full table scan. In Oracle, the optimization of the intervention optimizer is achieved by adding Hints (hint) to the statement.
Oracle hints is a mechanism that tells the optimizer to generate an execution plan in the way we tell it. We can use Oracle hints to achieve:
1) type of optimizer used
2) The optimization goal of the cost-based optimizer is all_rows or first_rows.
3) The Access path to the table, whether it is a full table scan, an index scan, or a direct use of ROWID.
4) Type of connection between tables
5) Order of connections between tables
6) The degree of parallelism of the statement
2, hint can be based on the following rules to produce a role
Table join order, table join method, Access path, degree of parallelism
In addition to the "RULE" hint, once the other hints are used, the statement is automatically changed to use the CBO optimizer, which will use the default statistics if there is no statistics in your data dictionary. So if you use the CBO or hints hints, it's a good idea to do regular analysis of the tables and indexes.
How to use hints:
Hints are only applied to their SQL statement block (statement block, identified by the SELECT, INSERT, UPDATE, delete keyword), and have no effect on other parts of the SQL statement or statement. For example, if you have hints on only one SQL statement for the 2 SQL statements that use the Union operation, the hints will not affect the other SQL statement.
We can use annotations (comment) to add hints to a statement, a statement block can have only one comment, and comments can only be placed after the Select, UPDATE, or delete keyword
Syntax for using Oracle hints:
{delete| Insert| Select| UPDATE}/*+ hint [text] [Hint[text]] ... */
Or
{delete| Insert| Select| UPDATE}--+ hint [text] [Hint[text]] ...
Annotations:
1) DELETE, INSERT, select, and update are keywords that identify the beginning of a block of statements, and comments that contain hints can only appear after those keywords, otherwise the prompt is invalid.
2) the "+" sign indicates that the comment is a hints, and the plus must immediately follow the "/*", with no spaces in the middle.
3) Hint is one of the specific tips described below, and if you have multiple hints, you need to separate each hint with one or more spaces.
4) text is the annotated text of other explanatory hint
If you do not specify the hints,oracle correctly, the hints will be ignored and no error will be given.
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;
No_index: Specify which indexes are not used
/*+ no_index (table [index [index] ...]) */
Select/*+ no_index (EMP ind_emp_sal ind_emp_deptno) */* from EMP where deptno=200 and sal>300;
Parallel.
Select/*+ Parallel (emp,4) */* from EMP where deptno=200 and sal>300;
Another: Each select/insert/update/delete command can only have a/*+ * *, but the hint content can have multiple, can be separated by commas, spaces can also.
Example:/*+ ordered index () use_nl () */
---------
A statement similar to the following: INSERT INTO XXXX select/*+parallel (a) */* from XXX A; the amount of data around 75G, the brother from the morning to the afternoon has not finished running, come to ask me what the matter, Said the usual 2hrs can run out of things for several hours still scattered. Check the system performance is also relatively normal, cpu,io are not busy, the average read speed around 80m/s (reluctantly), but the average write speed of only 10M. Wait for the event inside a large number of ' PX Deq credit:send blkd ', here can see the parallel problem, and finally learned that there is a problem in parallel usage, modified 20 minutes after the completion of the operation. The right approach should be:
Alter session enable DML parallel;
Insert/*+parallel (xxxx,4) */into XXXX select/*+parallel (a) */* from XXX A;
Because Oracle does not open PDML by default, the DML statement must be manually enabled. It is also important to say that parallelism is not an extensible feature, and that it is very prudent to use parallelism in an OLTP environment only when the data warehouse or a few tools such as a DBA facilitate the full utilization of resources in bulk data operations. In fact, Pdml still has a lot of limitations, such as unsupported triggers, referential constraints, advanced replication, and distributed transactions, as well as additional footprint, Pddl. For parallel excution, you can refer to the official documentation, which is also described in Thomas Kyte's new book, Expert Oracle Database architecture.
---------
Select COUNT (*)
From Wid_serv_prod_mon_1100 A
where a.acct_month = 201010
and a.partition_id = 10
and Serv_state not in (' 2HB ', ' 2HL ', ' 2HJ ', ' 2HP ', ' 2HF ')
and Online_flag in (0)
and incr_product_id in (2000020)
and product_id in (2020966, 2020972, 2100297, 2021116)
and billing_mode_id = 1
and Exp_date > To_date (' 201010 ', ' yyyymm ')
And NOT EXISTS (select/*+no_index (b idx_w_cdr_mon_serv_id_1100) */
1
From wid_cdr_mon_1100 b
where b.acct_month = 201010
and B.ana_event_type_4 in
(' 10201010201 ', ' 10202010201 ', ' 10203010201 ', ' 10203010202 ', ' 10203030201 ', ' 10203030202 ', ' 10204010201 ', ' 10204010202 ', ' 10204030201 ')
and a.serv_id = b.serv_id)
Oracle hints Detailed
