Hint Detailed _oracle in Oracle

Hints is a mechanism provided by Oracle to tell the optimizer to generate an execution plan in the way we tell it. We can use hints to achieve:

1 The type of optimizer used

2 The optimization goal based on the cost of the optimizer is All_rows or first_rows.

3 The Access path of the table is full table scan, or index scan, or directly using ROWID.

4 The type of connection between tables

5 The connection order 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

3, hint application range

DML statement

Query statement

4. Grammar

Or

If the language (sentence) method is not correct, Oracle will automatically ignore the written hint, no error

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:

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:

4./*+rule*/

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

For example:

5./*+full (TABLE) * *

Indicates a method for selecting a global scan on a table.

For example:

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 ' 
 

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 
 
 

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

Indicates the scanning method for selecting indexes on a table.

For example:

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

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

For example:

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

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

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

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

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

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

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

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

/*+use_merge (TABLE) * *

Joins the specified table with other row sources by merging the sort connections.

For example:

/*+use_hash (TABLE) * *

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

For example:

/*+driving_site (TABLE) * *

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

For example:

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

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

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

No_index: Specify which indexes are not used

/*+ no_index (table [index [index] ...]) * * 

Parallel

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.

such as:/*+ 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 run out, came to ask me how, said that ordinary 2hrs can run after the things run for hours and still sprinkle movement. Check the system performance is also more normal, Cpu,io are not busy, the average read speed in 80m/s about (reluctantly), but the average write speed of only 10M. Wait for the event inside a large number of ' PX Deq credit:send blkd ', which can be seen in parallel to the problem, and finally learned that there is a problem with the parallel usage, 20 minutes after the modification to complete 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 if the data warehouse or a few of the tools used as a DBA facilitate the full utilization of resources in the context of bulk data operations. In fact, there are a number of limitations to PDML, such as not supporting triggers, referential constraints, advanced replication, and distributed transactions, as well as additional space usage, PDDL.

---------

Select COUNT (*) from 
 wid_serv_prod_mon_1100 a 
 where a.acct_month = 201010 and 
  a.partition_id = 
  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 

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.