Hints-based oracle optimization solution

Here we only talk about hints-Based oracle Optimization solutions. In fact, Oracle optimizer has two Optimization methods Based on Rules (Rule-Based Optimization, RBO for short) cost-Based Optimization (CBO)

Hints is a mechanism provided by oracle to tell the optimizer to generate an execution plan as we tell it. We can use hints to implement:

1. Type of optimizer used

2. The optimization goal of the cost-based optimizer is all_rows or first_rows.

3. The access path of a table is full table scan, index scan, or rowid.

4. connection types between tables

5. Connection sequence between tables

6. Parallel statement degree

In addition to the "RULE" prompt, once other prompts are used, the statement will be automatically changed to the CBO optimizer. If your data dictionary does not contain statistics, the default statistical data is used. Therefore, it is recommended that you perform regular analysis on tables and indexes if you use the CBO or HINTS prompt.

How to Use hints:

Hints only applies to the SQL statement block (statement block, identified by the select, update, and delete keywords) where they are located. Hints does not affect other SQL statements or statements. For example, if two SQL statements that use the union operation have hints on only one SQL statement, the hints will not affect the other SQL statement.

We can use comment to add hints to a statement. A statement block can only have one comment, and the comment can only be placed behind the SELECT, UPDATE, or DELETE keyword.

Use hints Syntax:

{DELETE | INSERT | SELECT | UPDATE}/* + hint [text] [hint [text]... */or {DELETE | INSERT | SELECT | UPDATE} -- + hint [text] [hint [text]... note:

1. DELETE, INSERT, SELECT, and UPDATE are keywords that identify the beginning of a statement block. Comments containing prompts can only appear behind these keywords; otherwise, the prompt is invalid.

2. "+" indicates that the comment is a hints, And the plus sign must be immediately followed by "/*" without spaces in the middle.

3. hint is one of the specific prompts described below. If there are multiple prompts, one or more spaces are required to separate each prompt.

4. text is other explanatory text about hint.

If you do not specify hints correctly, Oracle will ignore this hints and will not give any errors.

With this hint, you can ignore the Compilation Time Optimization rules for workbooks such as detailed graph analysis. Other optimizations, such as creating filters to selectively locate the workbook access structure and restrict the revision rules, are available.

Because the number of rules is very large, the workbook analysis will be very long. This prompt helps us reduce the resulting compilation time by hundreds of hours.

Example:
SELECT/* + SPREAD_MIN_ANALYSIS */...

2. spread_no_analysis

With this hint, No workbook analysis is possible. Similarly, this hint can be used to ignore the revision rules and filter the generation. If a spreadsheet analysis exists, the Compilation Time can be minimized.

Example:
SELECT/* + SPREAD_NO_ANALYSIS */...

3. use_nl_with_index

This hint allows CBO to add a specific table to another original row through nested loops. A specific table is used as an internal table only in the following cases: if no label is specified, CBO must be able to use some labels, and at least one of these labels can be used as the index key value for judgment; otherwise, CBO must be able to add at least one tag as the index key value to the judgment.

Example:
SELECT/* + USE_NL_WITH_INDEX (polrecpolrind )*/...

4. CARDINALITY

This hint defines the base number returned by the query or query. Note that if no table is defined, the base number is the total number of rows returned by the entire query.

Example:
SELECT/* + CARDINALITY ([tablespec] card )*/

5. selecti.pdf

This hint defines a selective evaluation of the query or query. If only one table is defined, the row part that satisfies all single table judgments in the defined table is optional. If a series of tables are defined, selectivity refers to the row section in the result after all tables that meet all available judgments in any order are merged.

Example:
SELECT/* + selectispec ([tablespec] sel )*/

However, note that if both hints CARDINALITY and SELECTIVITY are defined in the same batch of tables, both are ignored.

6. no_use_nl

Hint no_use_nl enables CBO to execute loop nesting. By using the specified table as an internal table, each specified table is connected to another original row. With this hint, only hash join and sort-merge joins are considered for the specified table.

Example:
SELECT/* + NO_USE_NL (employees )*/...

7. no_use_merge

This hint allows CBO to reject sort-merge from adding each specified table to another original row by using the specified table as an internal table.

Example:
SELECT/* + NO_USE_MERGE (employees dept )*/...

8. no_use_hash

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

Example:
SELECT/* + NO_USE_HASH (employees dept )*/...

9. no_index_ffs

This hint denies CBO from performing fast full-index scan on the specified tags in the specified table.
Syntax:/* + NO_INDEX_FFS (tablespecindexspec )*/

Common HINT usage during SQL optimization (the first 10 are commonly used, and the first three are the most commonly used ):

1. /* + INDEX */and/* + INDEX (TABLE INDEX1, index2) */and/* + INDEX (tab1.col1 tab2.col2) */and/* + NO_INDEX (TABLE INDEX1, index2 )*/

Indicates the method used to scan the selected index on the table. the first option is to allow oracle to compare available indexes in the table and select an optimal index. The second option is to specify the index name and specify Multiple indexes. The third option is to start with 10 Gb, specifies the column name, and the table name does not need an alias. The fourth method is full table scan. The fifth method indicates that an index is disabled. This method is especially suitable for evaluation operations before you prepare to delete an index. if both INDEX and NO_INDEX are used, both prompts are ignored.
For example: SELECT/* + INDEX (BSEMPMS SEX_INDEX) USE SEX_INDEX because there are fewmale bsempms */from bsempms where sex = 'M ';

2./* + ORDERED */
By default, the last table in the FROM clause is the driver table, and ORDERED uses the first table in the from clause as the driver table. This is especially suitable for attempts when multi-table join is very slow.
Example: SELECT/* + ORDERED */. COL1, B. COL2, C. COL3 FROM TABLE1 A, TABLE2 B, TABLE3 C WHERE. COL1 = B. COL1 and B. COL1 = C. COL1;

3./* + PARALLEL (table1, DEGREE) */and/* + NO_PARALLEL (table1 )*/
This prompt divides the query that needs to perform full table scan into multiple parts (Parallelism) for execution, and then processes each part in different operating system processes. this prompt can also be used for DML statements. if there are sorting operations in the SQL statement, the number of processes will double. In addition, there are also processes responsible for combining these parts one by one. The following example will generate nine processes. if DEGREE is not specified in the prompt, the default value for table creation is used. the APPEND prompt is used by default. NO_PARALLEL is used to prohibit parallel operations. Otherwise, the statement will use parallel processing generated by defining parallel objects.
Example: select/* + PARALLEL (tab_test, 4) */col1, col2 from tab_test order by col2;

4./* + FIRST_ROWS */and/* + FIRST_ROWS (n )*/
It means to get 1st/n rows at the fastest speed, get the best response time, and minimize resource consumption.
The update and delete statements are ignored. group statements such as group by/distinct/intersect/minus/union are also ignored.
Example: SELECT/* + FIRST_ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

5./* + RULE */
Indicates the rule-based Optimization Method for the statement block.
Example: SELECT/* + RULE */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

6./* + FULL (TABLE )*/
Indicates the method used to globally scan the table.
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 connection order.

8./* + USE_NL (TABLE1, TABLE2 )*/
Concatenates the specified table and the nested connected row source to return the first row and reconnect as quickly as possible. This is the opposite of USE_MERGE.
Example: SELECT/* + ORDERED USE_NL (BSEMPMS) */BSDPTMS. Sort, BSEMPMS. EMP_NO, BSEMPMS. EMP_NAM from bsempms, bsdptms where bsempms. Sort = BSDPTMS. Sort;

9./* + APPEND */and/* + NOAPPEND */
Insert directly to the end of the table. This prompt does not check whether there is block space required for the insert operation. Instead, it is directly added to the new block, so the speed can be improved. of course, it will also waste some space, because it will not use the block space that has performed the delete operation. the NOAPPEND prompt is the opposite, so the default APPEND prompt for PARALLEL will be canceled.
Example: insert/* + append */into test1 select * from test4;
Insert/* + parallel (test1) noappend */into test1 select * from test4;

10./* + USE_HASH (TABLE1, table2 )*/
Connects the specified table to other row sources by means of hash connections. provides the best response time for a large result set. similar to traversing the nested loop of each result in each table in the results of the connected table, the specified hash table will be placed in the memory, so there must be enough memory (hash_area_size or pga_aggregate_target) the statement can be correctly executed. Otherwise, the statement will be executed on the disk.
Example: SELECT/* + USE_HASH (BSEMPMS, BSDPTMS) */* from bsempms, bsdptms where bsempms. DPT_NO = BSDPTMS. DPT_NO;

---------------------------------------------------------------------

11./* + USE_MERGE (TABLE )*/
The specified table is connected to other row sources by means of merged sort connections. it is particularly suitable for queries that perform set operations on a large number of rows in multiple tables. It sorts all rows retrieved from a specified table and then merges them. This is the opposite of USE_NL.
Example: SELECT/* + USE_MERGE (BSEMPMS, BSDPTMS) */* from bsempms, bsdptms where bsempms. DPT_NO = BSDPTMS. DPT_NO;

12./* + ALL_ROWS */
It indicates that the overhead-based optimization method is selected for the statement block and the optimal throughput is obtained to minimize the resource consumption, which may limit the use of some indexes.
Example: SELECT/* + ALL + _ ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';

13./* + CLUSTER (TABLE )*/
The prompt clearly indicates the access method to select a cluster scan for the specified table. If you frequently access the connected table but seldom modify it, use the cluster prompt.
Example: SELECT/* + CLUSTER */BSEMPMS. EMP_NO, DPT_NO from bsempms, bsdptms where DPT_NO = 'tec304 'and bsempms. DPT_NO = BSDPTMS. DPT_NO;

14./* + INDEX_ASC (TABLE INDEX1, INDEX2 )*/
Indicates the method used to scan the index in ascending order. starting from 8i, this prompt is the same as the INDEX prompt function, because oracle scans the INDEX in ascending order by default, unless oracle also releases an INDEX in descending order in the future.
Example: SELECT/* + INDEX_ASC (BSEMPMS PK_BSEMPMS) */from bsempms where DPT_NO = 'Scott ';

15./* + INDEX_COMBINE (TABLE INDEX1, INDEX2 )*/
Specify multiple Bitmap indexes. If INDEX is not provided in INDEX_COMBINE, a Boolean combination of the INDEX is selected.
Example: SELECT/* + INDEX_COMBINE (BSEMPMS SAL_BMI HIREDATE_BMI) */* from bsempms where sal <5000000 and hiredate <SYSDATE;

16./* + INDEX_JOIN (TABLE INDEX1, INDEX2 )*/
Merge indexes. All data is already included in these two indexes and will not access the table any more. This is five times faster than using indexes and scanning the table with rowid.
Example: SELECT/* + INDEX_JOIN (BSEMPMS SAL_HMI HIREDATE_BMI) */SAL, hiredate from bsempms where sal <60000;

17./* + INDEX_DESC (TABLE INDEX1, INDEX2 )*/
Indicates the method used to scan the table in descending order of indexes.
Example: SELECT/* + INDEX_DESC (BSEMPMS PK_BSEMPMS) */from bsempms where DPT_NO = 'Scott ';

18./* + INDEX_FFS (TABLE INDEX_NAME )*/
Perform a quick full index scan for the specified table, instead of a full table scan. The column to be retrieved must be in the index. This prompt is especially applicable if many columns exist in the table.
For example: SELECT/* + INDEX_FFS (BSEMPMS IN_EMPNAM) */* from bsempms where DPT_NO = 'tec305 ';

19./* + NO_EXPAND */
For the or in-LIST query statement after the WHERE clause, NO_EXPAND will prevent it from being extended based on the optimizer and shorten the parsing time.
For example: SELECT/* + NO_EXPAND */* from bsempms where DPT_NO = 'tdc506 'and sex = 'M ';

20./* + DRIVING_SITE (TABLE )*/
It is applicable to the remote tables connected by dblink.
For example: SELECT/* + DRIVING_SITE (DEPT) */* from bsempms, DEPT @ bsdptms dept where bsempms. DPT_NO = DEPT. DPT_NO;

21./* + CACHE (TABLE) */and/* + NOCACHE (TABLE )*/
When a full table scan is performed, the CACHE prompts that all tables can be cached in the memory, so that users accessing the same table can directly search for data in the memory. it is suitable for tables with small data volumes but frequently accessed. You can also specify the cache option when creating a table so that the table can be cached during the first access. NOCACHE indicates that the table with the specified CACHE option is not cached.
Example: SELECT/* + FULL (BSEMPMS) CAHE (BSEMPMS) */EMP_NAM from bsempms;

22./* + PUSH_SUBQ */
When a subquery is used in the SQL statement and a relatively small number of rows are returned, this prompt can be evaluated as early as possible to improve performance, and is not applicable to merge connections or connections with remote tables.
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 ');
Remotely connect to other databases. Check whether the database is started or whether a required table exists. Otherwise, an error occurs.

23./* + INDEX_SS (TABLE INDEX1, INDEX2 )*/
Indicates the use of skip scanning for the index of a specific table. That is, when the first column of the composite index is not in the where clause, the index is used.