Oracle common Hint (ii)

Hint related to the table connection method

1, Use_merge

Use_merge is a hint for multiple target tables, and it means that the optimizer will concatenate multiple tables that we specify as the driven table with other tables or result sets. The target table specified in Use_merge hint should be the driven table of the sort merge connection, and if the specified table is not a driven table for the sort merge connection, then Oracle either ignores the hint or ignores the table.

The format is as follows:

/*+ use_merge (target table 1 target table 2 ...) Target table N) */

/*+ use_merge (target table 1, target table 2,......, target table N) */

Examples of Use:

Select/*+ use_merge (EMP) */*

From Emp,dept

where Emp.deptno=dept.deptno;

Select/*+ Use_merge (e J D T) */E.ename,j.job,e.sal,d.deptno

From emp e,jobs j,dept d,emp_temp t

where E.empno=j.empno

and E.deptno=d.deptno

and d.loc= ' CHICAGO '

and E.ename=t.ename

Order BY E.ename;

The execution plan to do a table connection between the table EMP, DEPT, JOBS, and emp_temp all goes through a sort merge connection, which means that use_merge hint is already in effect. The table emp specified in hint cannot be a driven table for sorting merge connections, but the other three tables specified in hint above dept, JOBS, and emp_temp are driven tables that can be used as sort merge connections, so Oracle simply ignores the table EMP and does not ignore the Use_ MERGE Hint.

It is because Oracle may ignore Use_merge Hint or ignore the driver tables specified therein, so we usually use leading Hint (or ordered Hint) with Use_merge Hint to get the optimizer out of the execution plan we want.

Select/*+ Leading (e) use_merge (J D T) */E.ename,j.job,e.sal,d.deptno

From emp e,jobs j,dept d,emp_temp t

where E.empno=j.empno

and E.deptno=d.deptno

and d.loc= ' CHICAGO '

and E.ename=t.ename

Order BY E.ename;

2, No_use_merge

No_use_merge is a hint for multiple target tables, which is the hint of use_merge, which means that the optimizer does not allow multiple tables that we specify to be sorted as driven tables and other tables or result sets in a merge connection.

The target table specified in No_use_merge Hint should be the drive table that was originally in the sort merge connection, or Oracle would either ignore the No_use_merge Hint or ignore the table. It is because Oracle may ignore No_use_merge Hint or ignore the driver tables specified therein, so we usually use leading Hint (or ordered Hint) with No_use_merge Hint. To get the optimizer out of the execution plan we want.

The format is as follows:

/*+ no_use_merge (target table 1 target table 2 ...) Target table N) */

/*+ no_use_merge (target table 1, target table 2,......, target table N) */

Examples of Use:

Select/*+ no_use_merge (EMP) */*

From Emp,dept

where Emp.deptno=dept.deptno;

Select/*+ No_use_merge (e J D T) */E.ename,j.job,e.sal,d.deptno

From emp e,jobs j,dept d,emp_temp t

where E.empno=j.empno

and E.deptno=d.deptno

and d.loc= ' CHICAGO '

and E.ename=t.ename

Order BY E.ename;

3, Use_nl

USE_NL is a hint for multiple target tables, meaning that the optimizer will make nested loops of multiple tables We specify as driver tables and other tables or result sets. The target table specified in Use_nl Hint should be the driven table in the nested loop connection, or Oracle will either ignore the USE_NL Hint or ignore the table. It is because Oracle may ignore USE_NL Hint or ignore the driver tables specified therein, so we usually use leading Hint (or ordered Hint) with Use_nl Hint to get the optimizer out of the execution plan we want.

The format is as follows:

/*+ use_nl (target table 1 target table 2 ...) Target table N) */

/*+ use_nl (target table 1, target table 2,......, target table N) */

Examples of Use:

Select/*+ USE_NL (DEPT) */*

From Emp,dept

where Emp.deptno=dept.deptno;

Select/*+ Use_nl (e J D T) */E.ename,j.job,e.sal,d.deptno

From emp e,jobs j,dept d,emp_temp t

where E.empno=j.empno

and E.deptno=d.deptno

and d.loc= ' CHICAGO '

and E.ename=t.ename

Order BY E.ename;

USE_NL hint users and use_merge hint use the same.

4, No_use_nl

NO_USE_NL is a hint for multiple target tables, which is the hint of use_nl, which means that the optimizer does not allow multiple table joins we specify to be nested loops connected to other tables or result sets as driven tables. The target table specified in No_use_nl Hint should be the driven table in the nested loop connection, or Oracle will either ignore the NO_USE_NL Hint or ignore the table. It is because Oracle may ignore NO_USE_NL Hint or ignore the driver tables specified therein, so we usually use leading Hint (or ordered Hint) with No_use_nl Hint to get the optimizer out of the execution plan we want.

The format is as follows:

/*+ no_use_nl (target table 1 target table 2 ...) Target table N) */

/*+ no_use_nl (target table 1, target table 2,......, target table N) */

Examples of Use:

Select/*+ NO_USE_NL (DEPT) */*

From Emp,dept

where Emp.deptno=dept.deptno;

Select/*+ No_use_nl (e J D T) */E.ename,j.job,e.sal,d.deptno

From emp e,jobs j,dept d,emp_temp t

where E.empno=j.empno

and E.deptno=d.deptno

and d.loc= ' CHICAGO '

and E.ename=t.ename

Order BY E.ename;

NO_USE_NL hint users and no_use_merge hint use the same.

5, Use_hash

Use_hash is a hint for multiple target tables, meaning that the optimizer hashes the multiple tables we specify as driven tables with other tables or result sets. The target table specified in Use_hash Hint should be the driven table in the hash connection, or Oracle will either ignore the Use_hash Hint or ignore the table. It is because Oracle may ignore Use_hash Hint or ignore the driver tables specified therein, so we usually use leading Hint (or ordered Hint) with Use_hash Hint to get the optimizer out of the execution plan we want.

The format is as follows:

/*+ Use_hash (target table 1 target table 2 ...) Target table N) */

/*+ Use_hash (target table 1, target table 2,......, target table N) */

Examples of Use:

Select/*+ Use_hash (EMP) */*

From Emp,dept

where Emp.deptno=dept.deptno;

Select/*+ Use_hash (e J D T) */E.ename,j.job,e.sal,d.deptno

From emp e,jobs j,dept d,emp_temp t

where E.empno=j.empno

and E.deptno=d.deptno

and d.loc= ' CHICAGO '

and E.ename=t.ename

Order BY E.ename;

Use_hash hint users and use_merge hint use the same.

6, No_use_hash

No_use_hash is a hint for multiple target tables, which is a hash of the antisense hint, which means that the optimizer does not allow multiple tables we specify to be hashed with other tables or result sets as driven tables. The target table specified in No_use_hash Hint should be the driven table in the hash connection, or Oracle will either ignore the No_use_hash Hint or ignore the table. It is because Oracle may ignore No_use_hash Hint or ignore the driver tables specified therein, so we usually use leading Hint (or ordered Hint) with No_use_hash Hint. To get the optimizer out of the execution plan we want.

The format is as follows:

/*+ No_use_hash (target table 1 target table 2 ...) Target table N) */

/*+ No_use_hash (target table 1, target table 2,......, target table N) */

Examples of Use:

Select/*+ No_use_hash (EMP) */*

From Emp,dept

where Emp.deptno=dept.deptno;

Select/*+ No_use_hash (e J D T) */E.ename,j.job,e.sal,d.deptno

From emp e,jobs j,dept d,emp_temp t

where E.empno=j.empno

and E.deptno=d.deptno

and d.loc= ' CHICAGO '

and E.ename=t.ename

Order BY E.ename;

NO_USE_NL hint users and no_use_merge hint use the same.

7, Merge_aj

Merge_aj is a hint for subqueries, meaning that the optimizer performs a sort merge inverse of the related target table.

The format is as follows:

/*+ Merge_aj */

Examples of Use:

SELECT *

From EMP

where Deptno not in (select/*+ merge_aj */deptno

From Dept

where loc= ' CHICAGO ');

The meaning of hint in the above SQL is to have the optimizer perform a sort merge anti-join on the target table EMP and Dept. MERGE_AJ is hint for subqueries, so/*+ Merge_aj */is located in the query block where the subquery resides.

can also be written

Select/*+ Merge_aj (@zhaoxu) */*

From EMP

where Deptno not in (select/*+ qb_name (ZHAOXU) */deptno

From Dept

where loc= ' CHICAGO ');

8, Nl_aj

Nl_aj is a hint for subqueries, meaning that the optimizer performs a nested loop-back-join on the related target table.

The format is as follows:

/*+ Nl_aj */

Examples of Use:

SELECT *

From EMP

where Deptno not in (select/*+ nl_aj */deptno

From Dept

where loc= ' CHICAGO ');

Nl_aj users have the same usage as MERGE_AJ hint.

9, Hash_aj

Hash_aj is the hint for subqueries, which means that the optimizer performs a hash-back connection to the related target table.

The format is as follows:

/*+ Hash_aj */

Examples of Use:

SELECT *

From EMP

where Deptno not in (select/*+ hash_aj */deptno

From Dept

where loc= ' CHICAGO ');

Hash_aj users have the same usage as MERGE_AJ hint.

10, MERGE_SJ

MERGE_SJ is a hint for subqueries, meaning that the optimizer performs a sort merge semi-join on the related target table.

The format is as follows:

/*+ MERGE_SJ */

Examples of Use:

SELECT *

From Dept D

where exists (select/*+ merge_sj */1

From EMP E

where E.deptno=d.deptno

and e.sal>800);

MERGE_SJ hint uses the same MERGE_AJ hint.

11, NL_SJ

NL_SJ is a hint for subqueries, meaning that the optimizer performs nested loop semi-joins on the related target table.

The format is as follows:

/*+ NL_SJ */

Examples of Use:

SELECT *

From Dept D

where exists (select/*+ nl_sj */1

From EMP E

where E.deptno=d.deptno

and e.sal>800);

NL_SJ hint uses the same MERGE_AJ hint.

12, HASH_SJ

HASH_SJ is the hint for subqueries, which means that the optimizer performs a hash half-connection on the related target table.

The format is as follows:

/*+ HASH_SJ */

Examples of Use:

SELECT *

From Dept D

where exists (select/*+ hash_sj */1

From EMP E

where E.deptno=d.deptno

and e.sal>800);

HASH_SJ hint uses the same MERGE_AJ hint.

II. hint related to parallelism

1, PARALLEL

Prior to Oracle 11GR2, parallel was a hint for a single target table, meaning that the optimizer could access the target table in parallel with the specified or system-computed degree of parallelism. Starting with Oracle 11GR2, Oracle has introduced automatic parallelism, and accordingly, PARALLEL hint has changed.

In Oracle 11GR2, the scope and usage of PARALLEL hint have changed, and PARALLEL hint in Oracle 11GR2 is hint for the entire target SQL, The implication is that the optimizer will execute the execution plan of the target SQL in parallel with the specified or computed parallelism to perform all the steps that can be executed in parallel. Of course, the old parallel hint for a single target table can still be used in Oracle 11GR2, but its priority will be lower than the new parallel hint for the entire target SQL, i.e. if both old and new formats are present in the target SQL parallel Hint,oracle will select the new parallel Hint for the entire target SQL, ignoring the old parallel Hint for a single target table.

The new parallel hint for the entire target SQL has the following 4 formats:

Format 1/*+ PARALLEL */

Format 2/*+ PARALLEL (AUTO) */

Format 3/*+ PARALLEL (MANUAL) */

Format 4/*+ PARALLEL (specified degree of parallelism) */

Target SQL using format 1 is always executed in parallel, and Oracle calculates a degree of parallelism, which is always greater than or equal to 2.

Using the target sql,oracle of format 2 will calculate a degree of parallelism, but the computed parallelism may be 1, so using target SQL is not always performed in parallel.

With the target SQL of format 3, the ability to perform parallel execution depends entirely on the degree of parallelism of the related objects in the target SQL setting. For example, if the degree of parallelism of the target table is set greater than 1 o'clock, the target SQL executes in parallel, and parallel execution is equal to the degree of parallelism on the target table. If the target table has a degree of parallelism of 1, it is executed serially.

A target SQL that uses format 4 always executes the target SQL with the degree of parallelism specified in the hint.

The old parallel hint for a single target table has the following 2 formats:

Format 1/*+ PARALLEL (the degree of parallelism specified by the target table) */or/*+ PARALLEL (target table, specified degree of parallelism) */

Format 2/*+ PARALLEL (target table default) */or/*+ PARALLEL (target table, default) */

Target SQL using Format 1 always accesses the target table with the degree of parallelism specified in the hint.

Target SQL using format 2 will always access the target table in parallel based on the default parallelism computed by the relevant system parameters.

Examples of the use of parallel hint for the entire target sql:

Select/*+ Parallel */ename from EMP;

Select/*+ Parallel (Auto) */ename from EMP;

Select/*+ Parallel (manual) */ename from EMP;

Select/*+ Parallel (6) */ename from EMP;

Example of the use of parallel hint for a single target table:

Select/*+ Parallel (EMP 2) */ename from EMP;

Select/*+ Parallel (emp default) */ename from EMP;

You can view the details of the parallel subprocess used by the current system in parallel execution from V$pq_slave, and the Sessions field in the view represents the total number of sessions using parallel sub-processes, even if the same session, if it uses a parallel subprocess multiple times, The value of the field sessions for the corresponding record in the view is also incremented for that parallel child process. In certain cases, through this field I can analyze the actual degree of parallelism in the execution of the target SQL.

Parallel hint can also be used in global temporary tables in Oracle 11GR2.

2, No_parallel

In Oracle 11GR2, like the PARALLEL hint, the scope and usage of no_parallel hint have changed, and 11GR2 in Oracle No_parallel is hint for the entire target composition, which is PARALLEL Hint's antisense hint, which means that the optimizer does not allow parallel execution of the execution plan of the target SQL that can be executed in parallel. Of course, the old no_parallel hint for a single target table can still be used in Oracle 11GR2.

The new no_parallel format for the entire target SQL is as follows:

/*+ No_parallel */

The old no_parallel format for a single target table is as follows

/*+ no_parallel (target table) */

No_parallel usage examples for the entire target sql:

Select/*+ No_parallel */* from EMP;

No_parallel usage examples for a single target table:

Select/*+ no_parallel (EMP) */* from EMP;

3, Parallel_index

Parallel_index is a hint for a single target table, which means that the optimizer performs a parallel index scan of the target partition index on the target table by specifying or calculating the degree of parallelism.

The following 5 types of formats are used:

Format 1/*+ parallel_index (the degree of parallelism specified by the target table target partition Index) */

Format 2/*+ parallel_index (target table target partition index DEFAULT) */

Format 3/*+ parallel_index (target table target partition index 1 target partition index 2 ...) Target partition index n the degree of parallelism of the target partition index 1 for the target partition index 2. The degree of parallelism of the target partition index N) */

Format 4/*+ parallel_index (target table target partition index 1 target partition index 2 ...) Target partition index n default default ... DEFAULT) */

Format 5/*+ parallel_index (target table) */

Target SQL using Format 1 always accesses the target partition index on the target table in parallel with the degree of parallelism specified in format 1

Target SQL using format 2 will always access the target partition index on the target table in parallel with the default parallelism calculated based on the relevant system parameters. Here the optimizer may make some adjustments to the computed default parallelism, that is, the actual degree of parallelism of the target SQL using format 2 is not necessarily the default degree of parallelism for the current system.

You can specify multiple target indexes in Parallel_index hint and specify their respective degrees of parallelism (format 3), or uniformly specify their respective parallelism as the default value (format 4) computed by Oracle, or only the target table (format 5, Indicates that all existing indexes on the target table are specified at the same time, Oracle calculates the cost of each parallel scan separately, and selects one of the lowest cost values as the target index for the parallel scan.

The delimiter in the hint can also be used ",".

Examples of Use:

Select/*+ Parallel_index (emp_par idx_par 3) */EMP from Emp_par;

Select/*+ parallel_index (emp_par idx_par default) */EMP from Emp_par;

Select/*+ Index (Emp_par idx_par_1) parallel_index (Emp_par idx_par_1 idx_par_2 3 3) */EMP from Emp_par;

4, No_parallel_index

No_parallel_index is a hint for a single target table, which is an inverse hint of parallel_index hint, meaning that the optimizer does not allow a parallel index scan operation on the target partition index specified by hint on the target table.

The format is as follows:

Format 1/*+ no_parallel_index (target table target partition Index) */

Format 2/*+ no_parallel_index (target table target partition index 1 target partition index 2 ...) Target partition index N) */

Format 3/*+ no_parallel_index (target table) */

Separators can also be used ",".

Examples of Use:

Select/*+ no_parallel_index (emp_par idx_par) */empno from Emp_par;

Select/*+ no_parallel_index (emp_par idx_par1 idx_par_2) */empno from Emp_par;

Select/*+ No_parallel_index (emp_par) */empno from Emp_par;

III. hint related to query conversions

1, Use_concat

Use_concat is a hint for the entire target SQL, meaning that the optimizer uses In-list extensions (In-list Expansion) or or extensions (or Expansion) for the target SQL.

The format is as follows:

/*+ Use_concat */

Examples of Use:

Select/*+ Use_concat */emp,ename from EMP where empno in (7654,7698);

Select/*+ Use_concat */* from EMP where mgr=7902 and deptno=20;

The corresponding keyword in the execution plan is "concatenation".

2, No_expand

No_expand is a hint for the entire target SQL, which is a use_concat hint, meaning that the optimizer is not allowed to use in-list extensions or or extensions for target SQL.

The format is as follows:

/*+ No_expand */

Examples of Use:

Select/*+ No_expand */emp,ename from EMP where empno in (7654,7698);

Select/*+ No_expand */* from EMP where mgr=7902 and deptno=20;

The keywords that do not appear in the execution plan are "concatenation".

3. MERGE

The merge is a hint for a single target view, meaning that the optimizer performs a view merge on the target view (view mergeing).

Use format:

/*+ MERGE (target view) */

If the target view is an inline view, the merge hint can also appear in the query block where its view definition statement is located, except that the name of the inline view should not be taken in the hint, which should be formatted as/*+ MERGE */

Examples of Use:

Select/*+ Merge (dept_view) */Empno,ename,dname

From Emp,dept_view

where Emp.deptno=dept_view.deptno;

Select Empno,ename,dname

From EMP,

(Select/*+ Merge */*

From Dept

where local= ' CHICAGO ') dept_view_inline

where Emp.deptno=dept_view_inline.deptno;

4, No_merge

No_merge is a hint for a single target view, which is the inverse of the merge hint, meaning that the view merge is not performed on the target view by the optimizer.

The format is as follows:

/*+ No_merge (target view) */

If the target view is an inline view, the MERGE hint can also appear in the query block where its view definition statement is located, except that the name of the inline view should not be in the hint, and should be formatted as/*+ No_merge */

Examples of Use:

Select/*+ No_merge (dept_view) */Empno,ename,dname

From Emp,dept_view

where Emp.deptno=dept_view.deptno;

Select Empno,ename,dname

From EMP,

(Select/*+ no_merge */*

From Dept

where local= ' CHICAGO ') dept_view_inline

where Emp.deptno=dept_view_inline.deptno;

5, UNNEST

Unnest is a hinit for subqueries, meaning that the optimizer performs a subquery expansion (subquery unnesting) on the subquery of the target SQL.

The format is as follows:

/*+ UNNEST */

Examples of Use:

SELECT *

From EMP

where Deptno not in (select/*+ unnest */Deptno

From Dept

where loc= ' CHICAGO ');

The location of/*+ unnest */should be in the query block where the subquery resides.

6, No_unnest

No_unnest is a hint for subqueries, which is a unnest hint, meaning that the optimizer is not allowed to use subqueries to expand on subqueries in the target SQL.

The format is as follows:

/*+ No_unnest */

Examples of Use:

SELECT *

From EMP

where Deptno not in (select/*+ no_unnest */Deptno

From Dept

where loc= ' CHICAGO ');

/*+ no_unnest * * is located in the query block where the subquery is located.

7, Expand_table

Expand_table is a hint for a single target table, meaning that the optimizer performs a table extension on the target table in the target table SQL without considering the cost.

The format is as follows:

/*+ expand_table (target table) */

Examples of Use:

Select/*+ expand_table (T1) */t2.cust_city,sum (t1.amount_sold) amount_sold_total

From sales t1,customers t2,products t3,channels T4

where t1.cust_id=t2.cust_id

and t1.prod_id=t3.prod_id

and t1.channel_id=t4.channel_id

and t2.country_id=52771

and T3.prod_name= ' Mouse Pad '

and t4.channel_desc= ' Internet '

and time_id between To_date (' 2000-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ') and To_date (' 2004-01-01 00:00:00 ', ' Syyyy-mm-dd HH24:MI:SS ');

8, No_expand_table

No_expand_table is a hint for a single target table, which is a expand_table hint, meaning that the optimizer is not allowed to use table extensions for target tables in the target SQL.

The format is as follows:

/*+ no_expand_table (target table) */

Examples of Use:

Select/*+ no_expand_table (T1) */t2.cust_city,sum (t1.amount_sold) amount_sold_total

From sales t1,customers t2,products t3,channels T4

where t1.cust_id=t2.cust_id

and t1.prod_id=t3.prod_id

and t1.channel_id=t4.channel_id

and t2.country_id=52771

and T3.prod_name= ' Mouse Pad '

and t4.channel_desc= ' Internet '

and time_id between To_date (' 2000-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ') and To_date (' 2004-01-01 00:00:00 ', ' Syyyy-mm-dd HH24:MI:SS ');

Refer to Oracle-based SQL optimization

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1908650

Oracle common Hint (ii)