During the field work, when optimizing a SQL statement, we often use hint (prompt), sometimes it will find that DBA has been significantly improved after the use of Hint, and Oracle's performance has improved, it is mainly embedded. To the SQL statement, it is now summarized the usage of Oracle Hint during the SQL statement optimization: 1. / * all_rows * / indicates that the 对 语 选择 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法 方法The resource consumption is minimized. For example: SELECT / * ALL _ROWS * / EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott';
2. / * first_rows * / Indicates the 对 语 选择 基 方法 方法 方法 方法 方法 方法 方法 方法 方法 时间 获得 时间 获得 时间 使 时间 使 时间 使 时间 使. 使. 使. 获得. 获得. 获得. 使. 使. 获得. 使. = 'Scott';
3. / * choose * / Indicates that if there is a statistics of the visiting table in the data dictionary, the optimal throughput will be optimized, and the best throughput is achieved; indicating that if there is no access to the table in the data dictionary, it will be based on rules. Optimization method for overhead; for example: select / * choose * / EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott';
4. / * rule * / Indicates the selection of rules based on rules. For example: SELECT / * RULE * / EMP_NO, EMP_NAM, DAT_IN FROM BSEMPM WHERE EMP_NO = 'Scott';
5. / * Full (Table) * / Indicates the method for selecting a global scan. For example: SELECT / * FULL (A) * / EMP_NO, EMP_NAM from bsempms a where EMP_NO = 'Scott';
6. / * RowID (Table) * / Tip Specification indicates that the specified table is accessed according to ROWID. For example: select / * rowid (bsempms) * / * from bsempms where rowid> = 'AaaaaaaaaaaaaaaaAnd EMP_NO =' Scott ' ;
7. / * Cluster (Table) * / Tip Expand that the access method for selecting a cluster scan for the specified table is only valid for the cluster object. For 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 table selection index. For example: select / * index (bsempms sex_index) Use sex_index because there is feWMALE bsempms * / from bsempms where sex = 'm';
9. / * index_asc (Table index_name) * / Indicates the table selection index ascending scan method. For example: SELECT / * INDEX_ASC (bsempms pk_bsempms) * / from bsempms where dpt_no = 'scott';
10. / * index_combine * / Select bitmap to the specified table, if the index_combine does not provide an index as a parameter, the Boolean combination method of the graph index will be selected. For example: SELECT / * INDEX_COMBINE (bsempms Sal_Bmi HiRedate_BMI .) * / * FROM BSEMPMSWHERE SAL <5000000 AND HIREDATE 12. / * index_desc (Table Index_name) * / Indicates the scan method for the table to select index descending order. For 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 for the specified table, not a full meter scan. For example: SELECT / * INDEX_FFS (BSEMPMS IN_EMPNAM) * / * from bsempms where dpt_no = ' TEC305 '; 14. / * add_equal table index_nam1, index_nam2, ... * / prompts to make the choice of execution plan, combine a few single colony indexes. For 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 behind the WHERE in the query to a combination query of Union All. For example: SELECT / * USE_CONCAT * / * from bsempms where dpt_no = 'TDC506' and SEX = 'M' ; 16. / * NO_EXPAND * / NO_EXPAN for the in-List of the in-list * / for WHERE, NO_EXPAND will prevent it from extension based on the optimizer. For example: SELECT / * NO_EXPAND * / * from bsempms where dpt_no = 'TDC506 'And sex =' m '; 17. / * NOWRITE * / It is forbidden to rewrite the query of the query block. 18. / * rEWRITE * / You can use the view as a parameter. 19. / * Merge * / Ability to merge the respective queries of the view. For example: select / * merge (v) * / a.emp_no, a.emp_nam, b.dpt_no from bsempms A (Slet DPT_NO, AVG (SAL) AS AVG_SAL FROM BSEMPMS B Group BY DPT_NO) V where a.dpt_no = v.dpt_noand a.sal> v.avg_sal; 20. / * no_merge (Table) * / For the combined view no longer merged. For example: SELECT / * NO_MERGE (V) * / a.emp_no, a.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 a.dpt_no = v.dpt_no and a.sal> v.avg_sal; 21. / * Ordered * / Sequence in from FROM according to the table, Ordered Connect Oracle in this order. 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; 22. / * USE_NL (Table) * / Connect the specified table with the row of the nested connection, and use the specified table as the internal table. 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; 23. / * USE_MERGE (TABLE) * / Connect the specified table with other line sources by merges the sort connection. For example: SELECT / * USE_MERGE (BSEMPMS, BSDPTMS) * / * from bsempms, BSDPTMS where bsempms.dpt_no = BSDPTMS.DPT_NO; 24. / * USE_HASH (Table) * / Connect the specified table with other lines. For example: SELECT / * USE_HASH (BSEMPMS, BSDPTMS) * / * from bsempms, bsdptms where bsempms.dpt_no = BSDPTMS.DPT_NO; 25. / * DRIVING_SITE (TABLE) * / Force the table different from the location 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 header in the connection order. 27. / * Cache (TABLE) * / When a full-megaphone is performed, the Cache prompts to place the table's retrieval block in the buffer cache, the most recent use of the minimum list of LRUs, for example: select / * fulll (bsempms) Cahe (bsempms) * / EMP_NAM from bsempms; 28. / * Nocache (TABLE) * / When the full-table scan is performed, the Cache prompts to place the retrieval block of the table in the buffer cache. The most recent list of LRUs in the buffer cache, such as: select / * full (bsempms) Nocahe (bsempms) * / EMP_NAM from bsempms; 29. / * append * / Directly inserted to the table, you can improve the speed. INSERT / * APPEND * / INTO TEST1 SELECT * from Test4; 30. / * noAppend * / By starting the regular insertion in the inserted statement survival .INSERT / * NOAPPEND * / INTO TEST1 SELECT * from Test4;
