oracle常用hint添加

1.视图添加索引

SELECT /*+index(VIEW_NAME.TABLE01_NAME INDEX01_NAME)
index(VIEW_NAME.TABLE01_NAME INDEX01_NAME)
index(VIEW_NAME.TABLE02_NAME INDEX01_NAME)
index(VIEW_NAME.TABLE03_NAME INDEX01_NAME)
index(VIEW_NAME.TABLE04_NAME INDEX01_NAME)
*/
      *
  FROM VIEW_OWNER.VIEW
 WHERE ADD_DATE > SYSDATE - 1;

2.多个索引添加hint

SELECT      /*+index(a INDEX_01) index(a INDEX02)*/
      COUNT (*)
  FROM TABLE_OWNER.TABLE_NAME a
 WHERE
CONDITION01 AND CONDITION02;

3.添加cursor_sharing hint

SELECT /*+ CURSOR_SHARING_EXACT */ 
* 
FROM TABLE_OWNER.TABLE_NAME A 
WHERE DECODE (TO_NUMBER (PROPERTY_01), 0, 0) = 0;

4.index range scan hint

SELECT /*+ INDEX_ASC(testtable IX_TESTTABLE_ID) */ count(1) FROM testtable where id=2;

5.禁用动态采样hint

select /*+dynamic_sampling(dysam 0)*/* from dysam where id=10;

6.11g优化器hint

SELECT /*+OPTIMIZER_FEATURES_ENABLE('11.2.0.4')*/A.ID
  FROM MONKEY.TEST01 A,
       MONKEY.TEST02 B,
       MONKEY.TEST03 C
 WHERE     A.ID = B.ID
       AND A.ID = C.ID
       AND A.TEL_NO = ENCRYPT_FN (:B1);

升级到19C之后，如上sql A.TEL_NO = ENCRYPT_FN (:B1)无法使用A表的TEL_NO索引，加上11G的hint即可。

7.表连接相关hint

SELECT /*+ USE_NL(e d) */ e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT /*+ USE_HASH(e d) */ e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT /*+ USE_MERGE(e d) */ e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT /*+ LEADING(e) */ e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT /*+ ORDERED */ e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;