Oracle optimization solution based on hints

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. Types of optimizer used

2. Cost-based optimizer optimization objective is all_rows or first_rows.

3. Table access path, whether full table scan, or index scan, or direct use of ROWID.

4. Type of connection between tables

5. Sequence of joins between tables

6. The degree of parallelism of the statement

In addition to the rule hint, once the other prompts are used, the statement automatically changes to the CBO optimizer, and the default statistics are used if there is no statistical data in your data dictionary. So if you use CBO or hints hints, it's a good idea to analyze your tables and indexes regularly.

How to use hints:

Hints are only applied to their SQL statement blocks (statement block, identified by the Select, Update, and delete keywords) and have no effect on other SQL statements or other parts of the statement. For example, if you have hints on only one SQL statement for 2 SQL statements that use the Union operation, the hints does not affect another SQL statement.

We can use annotations (comment) to add hints for a statement, one statement block can have only one comment, and annotations can only be placed after the Select, UPDATE, or delete keyword

Syntax for using 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 statement block, and comments that contain hints can appear only after those keywords, otherwise the prompts are invalid.

2. The "+" number means that the annotation is a hints, 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 include multiple prompts, you need to separate each hint with one or more spaces.

4.text is an annotated version of the other description hint

If you do not specify the correct hints,oracle the hints is ignored and no error is given.

Using this hint, you can ignore some compile-time optimization rules for spreadsheets such as detailed relational dependency graph analysis. Other optimizations, such as creating filters to selectively locate the spreadsheet access structure and restricting revision rules, are continued.

Because of the very large number of rules, spreadsheet analysis can be very long. This tip can help us reduce the number of compile times that are generated in hundreds of hours.

Cases:
SELECT/*+ spread_min_analysis * ...

2, Spread_no_analysis

With this hint, no spreadsheet analysis can be made possible. Similarly, using this hint can ignore revision rules and filter generation. If there is a spreadsheet analysis, compile time can be minimized.

Cases:
SELECT/*+ spread_no_analysis * ...

3, Use_nl_with_index

This hint enables the CBO to add specific tables to another original row through nested loops. Only in the following cases does it use a specific table as an internal table: If you do not specify a label, the CBO must be able to use a number of tags, and at least one of those tags will be judged as an index key value;

Cases:
SELECT/*+ Use_nl_with_index (polrecpolrind) * * ...

4, cardinality

This hint defines the evaluation of the cardinality returned by the query or query part. Note If you do not define a table, the cardinality is the total number of rows returned by the entire query.

Cases:
SELECT/*+ cardinality ([TABLESPEC] card) * *

5, selectivity

This hint defines the evaluation of the partial selectivity of the query or query. If only one table is defined, the selectivity is the portion of the row that satisfies all the single table judgments in the defined table. If you define a series of tables, selectivity refers to the line portion of the resulting result after merging all the tables that satisfy all available judgments in any order.

Cases:
SELECT/*+ selectivity ([TABLESPEC] sel) */

Note, however, that if both hints cardinality and selectivity are defined in the same batch of tables, both will be ignored.

6, No_use_nl

Hint NO_USE_NL enables the CBO to execute loops nesting, connecting each specified table to another original row by using the specified table as an internal table. With this hint, only hash join and Sort-merge joins are considered for the specified table.

Cases:
SELECT/*+ NO_USE_NL (Employees) * * ...

7, No_use_merge

This hint enables the CBO to reject Sort-merge to add each specified table to another original row by using the specified table as an internal table.

Cases:
SELECT/*+ No_use_merge (Employees dept) * * ...

8, No_use_hash

This hint enables the CBO to reject the hash joins to add each specified table to another original row by using the specified table as an internal table.

Cases:
SELECT/*+ No_use_hash (Employees dept) * * ...

9, No_index_ffs

This hint enables the CBO to reject the fast Full-index scan for the specified label for the specified table.
Syntax:/*+ no_index_ffs (TABLESPECINDEXSPEC) * *

Common hint usage in SQL optimization (Top 10 Common, first 3 most common):

1./*+ Index */and/*+ index (TABLE INDEX1, INDEX2) * * and/*+ index (tab1.col1 tab2.col2) * * and/*+ no_index * * and/*+ No_index ( TABLE INDEX1, index2) * *

Indicates the scanning method for selecting indexes on a table. The first unspecified index name is to have Oracle compare the available indexes in the table and select a best index; The second is to specify the index name and multiple indexes can be specified; The third is the beginning of 10g, specify the column name, and the table name can not alias; The fourth kind is the whole table scan; The fifth means that an index is disabled, especially if it is intended to be evaluated before an index is deleted. If both the index and the no_index are used, two prompts will be ignored.
For example: SELECT/*+ INDEX (Bsempms sex_index) use sex_index because THERE ARE fewmale Bsempms */from Bsempms WHERE sex= ' M ';

2./*+ ORDERED * *
The default last table in the FROM clause is the driver table, and ordered the first table in the FROM clause as the driver table. Especially suitable for multiple table joins that are very slow to try.
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;

3./*+ PARALLEL (table1,degree) * * and/*+ no_parallel (table1) * *
The hint divides the query that needs to perform a full table scan into parts (parallelism) and then processes each part in a different operating system process. This hint can also be used for DML statements. If there is a sort operation in SQL, the number of processes doubles, and there is a process that is responsible for combining those parts, as in the following example, 9 processes are generated. If you do not specify degree in the prompt, the default value when creating the table is used. The hint uses the append prompt by default. No_parallel is a ban on parallel operations, or the statement uses parallel processing resulting from the definition of a parallel object.
For example: Select/*+ PARALLEL (tab_test,4) * * col1, col2 from Tab_test order by col2;

4./*+ first_rows * * and/*+ first_rows (n) * *
The 1/n line is obtained with the fastest speed to obtain the best response time and minimize the resource consumption.
Are ignored in the update and DELETE statements, and are ignored when grouping statements such as group by/distinct/intersect/minus/union are used.
For example: SELECT/*+ first_rows * * emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

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

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

7./*+ Leading (TABLE) * *
Similar to the ordered prompt, the specified table is used as the driver table in the join order.

8./*+ USE_NL (table1,table2) * *
Connects the specified table to the row source of the nested connection, returning the first row and then connecting at the fastest speed, just the opposite of Use_merge.
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;

9./*+ APPEND * * and/*+ noappend * *
Inserted directly into the end of the table, the hint does not check whether there is currently a block space required for the insert operation but adds it directly to the new block, so you can increase the speed. Of course, it would be a waste of space because it would not use the block space that did the delete operation. Noappend prompts are reversed, so the default append hint for parallel prompts is canceled.
For example: Insert/*+ Append */Into the Test1 select * from Test4;
Insert/*+ Parallel (test1) noappend/into Test1 select * from Test4;

/*+ Use_hash (table1,table2) * *
Joins the specified table with other row sources through a hash connection. Provides the best response time for a larger result set. Similar to the nested loops that traverse each result of each table in the result of a join table, the specified hash table is put into memory, so there is sufficient memory (Hash_area_size or PGA _aggregate_target) To ensure that the statement executes correctly, otherwise it will be done on disk.
For example: SELECT/*+ use_hash (BSEMPMS,BSDPTMS) * * 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. Particularly suited to a query that sets operations on a large number of rows on multiple tables, it will sort all rows retrieved by the specified table and then be merged, just as opposed to USE_NL.
For example: SELECT/*+ use_merge (BSEMPMS,BSDPTMS) * * from Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;

/*+ 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. Some indexes may be restricted for use.
For example: SELECT/*+ all+_rows * * emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';

/*+ CLUSTER (TABLE) * *
Prompts an explicit indication of the access method that selects the cluster scan for the specified table. If you frequently access a connection table but rarely modifies it, use cluster hints.
For example: SELECT/*+ CLUSTER * * Bsempms. Emp_no,dpt_no from Bsempms,bsdptms WHERE dpt_no= ' TEC304 ' and Bsempms. Dpt_no=bsdptms. Dpt_no;

/*+ Index_asc (TABLE INDEX1, INDEX2) * *
Indicates a scan method that selects index ascending for the table. Starting at 8i, this hint is the same as the index hint, because the default Oracle scans the index in ascending order unless Oracle also launches descending scan indexes in the future.
For example: SELECT/*+ index_asc (Bsempms Pk_bsempms) */from Bsempms WHERE dpt_no= ' SCOTT ';

/*+ Index_combine (TABLE INDEX1, INDEX2) * *
Specifies multiple bitmap indexes, and the index is used for B-tree indexes, and if the index is not provided as a parameter in Index_combine, a Boolean combination of bitmap indexes will be selected.
For example: SELECT/*+ index_combine (Bsempms sal_bmi hiredate_bmi) * from Bsempms WHERE sal<5000000 and hiredate<sysdate;

/*+ Index_join (TABLE INDEX1, INDEX2) * *
Merge indexes, all of which are already included in both indexes, and no access to the table, 5 times times faster than using the index and scanning the table through ROWID.
For example: SELECT/*+ index_join (Bsempms sal_hmi hiredate_bmi) * * sal,hiredate from Bsempms WHERE sal<60000;

/*+ Index_desc (TABLE INDEX1, INDEX2) * *
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. The columns required for retrieval are in the index, especially if the table has many columns.
For example: SELECT/*+ index_ffs (Bsempms in_empnam)/* from Bsempms WHERE dpt_no= ' TEC305 ';

/*+ No_expand * *
For a query statement that is followed by or or in-list, No_expand prevents it from expanding based on the optimizer to shorten the parsing time.
For example: SELECT/*+ no_expand * * from Bsempms WHERE dpt_no= ' TDC506 ' and sex= ' M ';

/*+ Driving_site (TABLE) * *
Force query execution on tables that are different from the location chosen by Oracle. It is particularly useful for remote tables connected by Dblink.
For example: SELECT/*+ driving_site (DEPT) * * from Bsempms,dept@bsdptms DEPT WHERE Bsempms. Dpt_no=dept. Dpt_no;

/*+ CACHE (table) * * * and/*+ nocache (table) * *
When a full table scan is performed, the cache hint can cache all the tables in memory, so that users who access the same table can find the data directly in memory. Compare to tables that are small but often accessed, or you can specify cache options when you build a table so that you can cache them the first time you access them. NoCache indicates that the table that has the cache option specified is not cached.
For example: SELECT/*+ Full (BSEMPMS) Cahe (BSEMPMS) * * Emp_nam from Bsempms;

/*+ PUSH_SUBQ * *
When a subquery is used in SQL and a relatively small number of rows are returned, the hint can be evaluated as early as possible to improve performance and not apply to merge connections or connections with remote tables.
For example: Select/*+ PUSH_SUBQ */Emp.empno, emp.ename, ItemNo from EMP, orders where Emp.empno = Orders.empno and Emp.deptno = ( Select Deptno from dept where loc= ' XXX ');
Connect to another database remotely, to see if the database is started, or if you have a table that you need, or you can make an error

/*+ index_ss (TABLE index1,index2) * *
Indicates that a jump scan is used for the index of a particular table, that is, when the first column of the combined index is not in the WHERE clause, let it use the index