Common hint usage in Oracle

Common hint usage in Oracle 1. /* + ALL_ROWS */indicates to select the overhead-Based Optimization Method for the statement block and obtain the optimal throughput to minimize resource consumption. example: SELECT/* + ALL + _ ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott '; www.2cto.com 2. /* + FIRST_ROWS */indicates to select the overhead-Based Optimization Method for the statement block and obtain the optimal response time to minimize resource consumption. example: SELECT/* + FIRST_ROWS */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott '; 3. /* + CHOOSE */indicates that if the data dictionary contains statistics about the access table, the overhead-based optimization method is used to obtain the optimal throughput; it indicates that if no statistical information of the accessed table is in the data dictionary, the optimization method based on the Rule overhead will be used. For example: SELECT/* + CHOOSE */EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott '; 4. /* + 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 '; 5. /* + 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 '; 6. /* + ROWID (TABLE) */The prompt explicitly indicates that the specified TABLE is accessed according to the ROWID. for example: SELECT/* + ROWID (BSEMPMS) */* from bsempms where rowid> = 'aaaaaaaaaaaaa' AND EMP_NO = 'Scott '; 7. /* + CLUSTER (TABLE) */The prompt clearly indicates the access method for the selected CLUSTER scan for the specified TABLE, which is only valid for the CLUSTER object. example: SELECT/* + CLUSTER */BSEMPMS. EMP_NO, DPT_NO from bsempms, BSDPTMSWHERE DPT_NO = 'tec304 'and bsempms. DPT_NO = BSDPTMS. DPT_NO; 8. /* + INDEX (TABLE INDEX_NAME) */indicates the scan method for the selected INDEX on the TABLE. for example: SELECT/* + INDEX (BSEMPMS SEX_INDEX) USE SEX_INDEX because there are fewmale bsempms */from bsempms where sex = 'M'; 9. /* + INDEX_ASC (TABLE INDEX_NAME) */indicates the method used to scan the index in ascending order. example: SELECT/* + INDEX_ASC (BSEMPMS PK_BSEMPMS) */from bsempms where DPT_NO = 'Scott '; 10. /* + INDEX_COMBINE */select a bitmap access path for the specified table. If INDEX_COMBINE does not provide an index as a parameter, a Boolean combination of the index is selected. example: SELECT/* + INDEX_COMBINE (BSEMPMS SAL_BMI HIREDATE_BMI) */* from bsempmswhere sal <5000000 and hiredate <SYSDATE; 11. /* + INDEX_JOIN (TABLE INDEX_NAME) */indicates that the command optimizer uses indexes as the access path. example: SELECT/* + INDEX_JOIN (BSEMPMS SAL_HMI HIREDATE_BMI) */SAL, hiredatefrom bsempms where sal <60000; 12. /* + INDEX_DESC (TABLE INDEX_NAME) */indicates the method used to scan the index in descending order. example: SELECT/* + INDEX_DESC (BSEMPMS PK_BSEMPMS) */from bsempms where DPT_NO = 'Scott '; 13. /* + INDEX_FFS (TABLE INDEX_NAME) */perform a quick full index scan on the specified TABLE, instead of a full TABLE scan. example: SELECT/* + INDEX_FFS (BSEMPMS IN_EMPNAM) */* from bsempms where DPT_NO = 'tec305'; 14. /* + ADD_EQUAL TABLE INDEX_NAM1, INDEX_NAM2 ,... */The system prompts you to clearly select the execution plan and combine the scanning of several single-column indexes. example: SELECT/* + INDEX_FFS (BSEMPMS IN_DPTNO, IN_EMPNO, IN_SEX) */* from bsempms where EMP_NO = 'Scott 'AND DPT_NO = 'tdc306'; 15. /* + USE_CONCAT */converts the OR condition after the WHERE condition in the query to the union all query. example: SELECT/* + USE_CONCAT */* from bsempms where DPT_NO = 'tdc506 'and sex = 'M'; 16. /* + 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. example: SELECT/* + NO_EXPAND */* from bsempms where DPT_NO = 'tdc506 'and sex = 'M'; 17. /* + NOWRITE */the query rewrite operation on the query block is prohibited. 18. /* + REWRITE */views can be used as parameters. 19. /* + MERGE (TABLE) */can MERGE various views. example: SELECT/* + MERGE (V) */. EMP_NO,. 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. DPT_NO = V. DPT_NOAND. SAL> V. AVG_SAL; 20. /* + NO_MERGE (TABLE) */views that can be merged are not merged. example: SELECT/* + NO_MERGE (V) */. EMP_NO,. EMP_NAM, B. DPT_NO from bsempms a (SELECT DPT_NO, AVG (SAL) AS AVG_SAL from bsempms B GROUP BY DPT_NO) V WHERE. DPT_NO = V. DPT_NO and. SAL> V. AVG_SAL; www.2cto.com 21. /* + ORDERED */according to the order in which the table appears in FROM, ORDERED connects ORACLE to the table in this order. example: SELECT/* + ORDERED */. COL1, B. COL2, C. COL3 FROM TABLE1 A, TABLE2 B, TABLE3 C WHERE. COL1 = B. COL1 and B. COL1 = C. COL1; 22. /* + USE_NL (TABLE) */connects the specified TABLE to the nested connected row source and uses the specified TABLE as the internal TABLE. 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; 23. /* + USE_MERGE (TABLE) */connects the specified TABLE with other row sources by means of the combined sorting connection. for example: SELECT/* + USE_MERGE (BSEMPMS, BSDPTMS) */* from bsempms, bsdptms where bsempms. DPT_NO = BSDPTMS. DPT_NO; 24. /* + USE_HASH (TABLE) */connects the specified TABLE with other row sources through the hash connection. for example: SELECT/* + USE_HASH (BSEMPMS, BSDPTMS) */* from bsempms, bsdptms where bsempms. DPT_NO = BSDPTMS. DPT_NO; 25. /* + DRIVING_SITE (TABLE) */forcibly execute a query for a TABLE at a location different from that selected by ORACLE. for example: SELECT/* + DRIVING_SITE (DEPT) */* from bsempms, DEPT @ bsdptms where bsempms. DPT_NO = DEPT. DPT_NO; 26. /* + LEADING (TABLE) */use the specified TABLE as the first TABLE in the connection order. 27. /* + CACHE (TABLE) */when a full TABLE scan is performed, the CACHE prompts that the retrieval block of the TABLE can be placed in the nearest least list of LRU recently used in the buffer CACHE. For example: SELECT/* + FULL (BSEMPMS) CAHE (BSEMPMS) */EMP_NAM from bsempms; 28. /* + NOCACHE (TABLE) */when a full TABLE scan is performed, the CACHE prompts that the retrieval block of the TABLE can be placed in the nearest least list of LRU in the buffer CACHE. For example: SELECT/* + FULL (BSEMPMS) NOCAHE (BSEMPMS) */EMP_NAM from bsempms; 29. /* + APPEND */Insert directly to the end of the table to increase the speed. insert/* + append */into test1 select * from test4; 30. /* + NOAPPEND */start regular insert by stopping the parallel mode during the lifetime of the insert statement. insert/* + noappend */into test1 select * from test4;