Oracle10g common hint usage

Address: http://www.examda.com/oracle/jishu/20100828/112332400.html

 

Syntax

 

:
The prompt is case insensitive. Multiple prompts are separated by spaces, for example, select/* + hint1 (tab1) hint2 (TAB1 idx1) */col1, col2 from tab1 where col1 = 'xxx ';
If the table uses an alias, the alias must also be used in the prompt, for example, select/* + hint1 (t1) */col1, col2 from tab1 t1 where col1 = 'xxx ';
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;
Bytes ----------------------------------------------------------------------------------------------------
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 quits scanning the 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 ');
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.