Oracle数据库Hint大全，31个使用案例，速来下载！

SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_idFROM employeesWHERE employee_id = 107;

SELECT /*+ CONTAINERS(DEFAULT_PDB_HINT='NO_PARALLEL') */(CASE WHEN COUNT(*) < 10000THEN 'Less than 10,000'ELSE '10,000 or more' END) "Number of Tables"FROM CONTAINERS(DBA_TABLES);

SELECT /*+ DRIVING_SITE(departments) */ *FROM employees, departments@rsiteWHERE employees.department_id = departments.department_id;

SELECT /*+ DYNAMIC_SAMPLING(e 1) */ count(*)FROM employees e;
SELECT /*+ DYNAMIC_SAMPLING(employees 1) */ *FROM employeesWHERE ...

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_idFROM employeesWHERE department_id = 20;

SELECT /*+ FULL(e) */ employee_id, last_nameFROM hr.employees eWHERE last_name LIKE :b1;

SELECT PREDICTION(/*+ GROUPING */my_model USING *) pred FROM <input table>;

SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_idFROM employeesWHERE department_id > 50;

SELECT /*+ INDEX_ASC (employees emp_department_ix)*/ employee_id, department_idFROM employeesWHERE department_id > 50;

SELECT /*+ INDEX_DESC(e emp_name_ix) */ *FROM employees e;

SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *FROM employees eWHERE manager_id = 108OR department_id = 110;

SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_nameFROM employees e;

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_idFROM employees eWHERE manager_id < 110AND department_id < 50;

SELECT /*+ INDEX_SS(e emp_name_ix) */ last_nameFROM employees eWHERE first_name = 'Steven';INDEX_SS_ASC Hint

SELECT /*+ INDEX_SS_DESC(e emp_name_ix) */ last_nameFROM employees eWHERE first_name = 'Steven';

SELECT /*+ INMEMORY */ last_nameFROM employees eWHERE first_name = 'Steven';

SELECT /*+ LEADING(e j) */ *FROM employees e, departments d, job_history jWHERE e.department_id = d.department_idAND e.hire_date = j.start_date;

SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salaryFROM employees e1,(SELECT department_id, avg(salary) avg_salaryFROM employees e2GROUP BY department_id) vWHERE e1.department_id = v.department_idAND e1.salary > v.avg_salaryORDER BY e1.last_name;

SELECT /*+ NO_INDEX(employees emp_empid) */ employee_idFROM employeesWHERE employee_id > 200;

NO_INDEX_FFS Hint ，NO_INDEX_SS Hint ，NO_INMEMORY Hint，NO_MERGE Hint ，NO_PARALLEL Hint ，NO_PUSH_PRED Hint ，NO_REWRITE Hint ，NOREWRITE Hint，NO_STAR_TRANSFORMATION Hint ，NO_UNNEST Hint ，NO_USE_BAND Hint ，NO_USE_HASH Hint ，NO_USE_MERGE Hint ，NO_USE_NL Hint。

APPROX_FOR_AGGREGATION, APPROX_FOR_COUNT_DISTINCT, APPROX_FOR_PERCENTILE, OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and STAR_TRANSFORMATION_ENABLED.

SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ *FROM ... ;

SELECT  /*+ ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantityFROM customers c, order_items l, orders oWHERE c.cust_last_name = 'Taylor'AND o.customer_id = c.customer_idAND o.order_id = l.order_id;

SELECT /*+ PARALLEL */ last_nameFROM employees;
SELECT /*+ PARALLEL (AUTO) */ last_nameFROM employees;
CREATE TABLE parallel_table (col1 number, col2 VARCHAR2(10)) PARALLEL 5;
SELECT /*+ PARALLEL (MANUAL) */ col2FROM parallel_table;
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_nameFROM employees hr_emp;
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_nameFROM employees hr_emp;

SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */

SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *FROM employees e,(SELECT manager_idFROM employees) vWHERE e.manager_id = v.manager_id(+)AND e.employee_id = 100;

SELECT /*+ STAR_TRANSFORMATION */ s.time_id, s.prod_id, s.channel_idFROM sales s, times t, products p, channels cWHERE s.time_id = t.time_idAND s.prod_id = p.prod_idAND s.channel_id = c.channel_idAND c.channel_desc = 'Tele Sales';

SELECT /*+ USE_BAND(e1 e2) */e1.last_name|| ' has salary between 100 less and 100 more than '|| e2.last_name AS "SALARY COMPARISON"FROM employees e1, employees e2WHERE e1.salary BETWEEN e2.salary - 100 AND e2.salary + 100;

SELECT /*+ USE_CONCAT */ *FROM employees eWHERE manager_id = 108OR department_id = 110;

SELECT /*+ USE_HASH(l h) */ *FROM orders h, order_items lWHERE l.order_id = h.order_idAND l.order_id > 2400;

SELECT /*+ USE_MERGE(employees departments) */ *FROM employees, departmentsWHERE employees.department_id = departments.department_id;

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantityFROM orders h, order_items lWHERE l.order_id = h.order_id;

select /*+ LEADING(t2) USE_NL(t1) */ sum(t1.a),sum(t2.a)from   t1 , t2where   t1.b = t2.b;select * from table(dbms_xplan.display_cursor()) ;

SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *FROM orders h, order_items lWHERE l.order_id = h.order_idAND l.order_id > 2400;

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E