Oracle Hint Usage Rollup ____oracle

Hint is the embodiment of Oracle database flexibility. Because hint has the highest priority, it is possible to hint the optimizer to generate a specified execution plan based on the needs of the user.

Oracle has a wide variety of hint, roughly divided into the following categories:

Optimization methods and objectives: such as rule, CHOOSE, first_rows, all_rows and so on.

Access path: such as INDEX, full, CLUSTER, index_ffs, etc.

Query conversion: such as merge, Use_concat, No_expand and so on.

Connection order: such as ordered and star.

Connection operations: such as USE_NL, Use_hash, Use_merge, etc.

Parallel execution: such as Paralle, Noparallel, Parallel_index, etc.

Other types, such as Append, unnest, cache, etc.

Two ways to use HINT:/*+ HINT * * and--+ HINT

One, use_nl (nested loops join)

In a nested loop join, Oracle reads the first row from the first row source and then compares the data in the second row source. All matching records are placed in the result set, and Oracle reads the next row in the first row source. This way until the row in the first data source is processed. The first record source is usually called an external table , or a driver table , and the corresponding second row source is called an internal table . Using nested loops joins is the quickest way to extract the first batch of records from a connection result.

Nested loops join effects are ideal when the Row source table (that is, the record you are looking for) is small, or if the columns connected to the internal Row source table have a unique index or a highly optional, not unique index . Nested loops joins have an advantage over other join methods, and they can quickly extract the first batch of records from the result set without waiting for the entire result set to be completely finalized. In this way, in an ideal situation, end users can view the first batch of records through the query screen while simultaneously reading other records. The nested loops join is very flexible, regardless of how the condition or pattern of the connection is defined, and any two rows of record sources can use nested loops.

However, nested loop joins are inefficient if the Inner Row source table (the second table that is read) does not contain an index on the connected column, or if the index is not highly selectable. Other connection methods may be more efficient if the drive row source tables (records extracted from the driver table) are very large.

Figure 1-1 illustrates the method that the query executes in Listing 1-1 of the program.

Select/*+ordered*/ename,dept.deptno

From Dept,emp

where Dept.deptno=emp.deptno

Second, use_merge (arrange merge connection)

In arranging a merge join, Oracle sorts the first source table, the second source table by the columns they want to connect to, and then merges the two sorted source tables. If a matching data is found, it is placed in the result set.

When the data is not selective or available, or if the two source tables are too large (over 5% of the record number) , the sorted merge joins are more efficient than the nested loops. However, arranging merge joins can only be used for equivalent connections (where D.deptno=e.dejptno, not where D.deptno>=e.deptno). Arranging a merge join requires a temporary block of memory for sorting (if the sort_area_size is set too small). This will result in more memory and disk I/O in the temporary table space.

Figure 1-2 explains the method that the program listing 1-2 query executes.

Select/*+ordered*/ename,dept.deptno

From Emp,dept

where Dept.deptno=emp.deptno

three, Use_hash (hash connection)

Hash connections are a common choice for Oracle Optimizer when memory can provide enough space. In a hash connection, Oracle accesses a table (usually a larger table) and creates a hash table based on the connection key in memory. It then scans the other tables in the connection (usually large tables) and detects if there is a matching record based on the hash table.

Oracle uses a hash edge connection only if the database initialization parameter hash_join_enabled is set to true and a sufficiently large value is set for the parameter Pga_aggregate_target (Hash_area_ SIZE is a backward-compatible parameter, but hash_area_size should be used in versions prior to oracle9i. This is a bit like a nested loop connection--oracle first create a hash table to facilitate the operation. When you use the ordered prompt, the first table in the FROM clause is used to create a hash table.

A hash connection is more efficient than a nested loop connection when a useful index is missing. a hash join may be quicker than a sorted merge connection, because in this case only one source table needs to be sorted. Hash joins can also be faster than nested loops, because processing in-memory hash tables is quicker than retrieving b_ tree indexes. As with sort merge joins, cluster connections, hash joins can only be used for equivalent connections. As with a sorted merge connection, a hash connection uses memory resources and, when used for low order memory, increases the I/O of the temporary tablespace (which makes the connection method extremely slow). Finally, only the cost-based optimizer can use a hash join.

Figure 1-3 explains the method of executing listing 1-3 queries.

Select/*+ordered*/ename,dept.deptno

From Emp,dept

where Dept.deptno=emp.deptno

Summary of all hint usage:

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

/*+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 from Bsempms B GROUP 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;