1. /*+ALL_ROWS*/
It shows that the cost-based optimization method is selected for statement blocks, and the best throughput is obtained to minimize resource consumption.
For example:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2. /*+FIRST_ROWS*/
It shows that the cost-based optimization method is selected for statement blocks, and the best response time is obtained to minimize resource consumption.
For example:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
3. /*+CHOOSE*/
It indicates that if there is statistical information about the access table in the data dictionary, the optimization method will be based on the cost and the best throughput will be obtained; if there is no statistical information about the access table in the data dictionary, the optimization method will be based on the rule cost;
For example:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMMPMS WHERE EMP_NO='SCOTT';
4. /*+RULE*/
Indicates the selection of rule-based optimization methods for statement blocks.
For example:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMMPMS WHERE EMP_NO='SCOTT';
5. /*+FULL(TABLE)*/
Indicates how to select a global scan for the table.
For example:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
6. /*+ROWID(TABLE)*/
The prompt clearly indicates that the specified table is accessed based on ROWID.
For example:
SELECT /*+ROWID(BSEMMPMS)*/ * FROM BSEMMPMS WHERE ROWID>='AAAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';
7. /*+CLUSTER(TABLE)*/
The prompt clearly indicates that the access method of cluster scan is selected for the specified table, which is only valid for cluster objects.
For example:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /*+INDEX(TABLE INDEX_NAME)*/
Indicates the scan method for table selection indexes.
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 a scan method that selects indexes on the table in ascending order.
For example:
SELECT /*+INDEX_ASC(BSEMMPMS PK_BSEMPMS) */ FROM BSEMMPMS WHERE DPT_NO='SCOTT';
10. /*+INDEX_COMBINE*/
Select the bitmap access path for the specified table. If no index is provided as a parameter in INDEX_COMBINE, the 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
11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
The prompt explicitly instructs the optimizer to use the index as the access path.
For example:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMMPMS WHERE SAL<60000;
12. /*+INDEX_DESC(TABLE INDEX_NAME)*/
Indicates a scan method that selects indexes on the table in descending order.
For example:
SELECT /*+INDEX_DESC(BSEMMPMS PK_BSEMPMS) */ FROM BSEMMPMS WHERE DPT_NO='SCOTT';
13. /*+INDEX_FFS(TABLE INDEX_NAME)*/
Perform a fast full index scan on the specified table instead of a full table scan.
For example:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
Prompts you to explicitly select the execution plan and combine the scans of several single-column 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*/
Convert the OR condition after WHERE in the query to a combined query of UNION ALL.
For example:
SELECT /*+USE_CONCAT*/ * FROM BSEMMPMS WHERE DPT_NO='TDC506' AND SEX='M';
16. /*+NO_EXPAND*/
For the OR or IN-LIST query statements after WHERE, NO_EXPAND will prevent them from being expanded based on the optimizer.
For example:
SELECT /*+NO_EXPAND*/ * FROM BSEMMPMS WHERE DPT_NO='TDC506' AND SEX='M';
17. /*+NOWRITE*/
Query rewrite operations on query blocks are prohibited.
18. /*+REWRITE*/
Views can be passed as parameters.
19. /*+MERGE(TABLE)*/
Able to merge the various queries of the view accordingly.
For example:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
20. /*+NO_MERGE(TABLE)*/
Views that can be merged are 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*/
According to the order in which the tables appear in FROM, ORDERED causes ORACLE to connect to them in that 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)*/
Joins the specified table with the row source of the nested join, using the specified table as the inner 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)*/
Joins the specified table with other row sources using a merge sort join.
For example:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
24. /*+USE_HASH(TABLE)*/
Joins the specified table with other row sources using a hash join.
For example:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
25. /*+DRIVING_SITE(TABLE)*/
Forces query execution to a table that is 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 first table in the join order.
27. /*+CACHE(TABLE)*/
When performing a full table scan, the CACHE hint can place the retrieval block of the table in the most recently used end of the least recent list LRU in the buffer cache. For example:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
28. /*+NOCACHE(TABLE)*/
When performing a full table scan, the CACHE hint can place the retrieval block of the table in the most recently used end of the least recent list LRU in the buffer cache. For example:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
29. /*+APPEND*/
Inserting directly into the end of the table can improve speed.
For example:
insert /*+append*/ into test1 select * from test4 ;
30. /*+NOAPPEND*/
Starts regular inserts by stopping parallel mode for the lifetime of the insert statement.
For example:
insert /*+noappend*/ into test1 select * from