Oracle Hints

SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;

SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM employees e
  WHERE last_name LIKE :b1;
 
 
  SELECT /*+ROWID(employees)*/ *
FROM employees
WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;

SELECT /*+ INDEX(patients sex_index) use sex_index because there are few
   male patients  */ name, height, weight
FROM patients
WHERE sex = 'm';


SELECT /*+ INDEX_FFS ( o order_pk ) */ COUNT(*)
FROM order_items l, orders o
WHERE l.order_id > 50
     AND l.order_id = o.order_id;


SELECT /*+ ORDERED USE_NL(customers) to get first row faster */
    accounts.balance, customers.last_name, customers.first_name
FROM accounts, customers
WHERE accounts.customer_id = customers.customer_id;


SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
  FROM orders h ,order_items l
 WHERE l.order_id = h.order_id;


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



SELECT /*+USE_HASH(l l2) */ l.order_date, l.order_id, l2.product_id,
                            SUM(l2.unit_price*quantity)
  FROM orders l, order_items l2
 WHERE l.order_id = l2.order_id
GROUP BY l2.product_id, l.order_date, l.order_id;


SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name
FROM hr.employees hr_emp;

SELECT /*+ NOPARALLEL(hr_emp) */ last_name
FROM hr.employees hr_emp;

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


INSERT /*+ APPEND */ INTO dest_tab SELECT * FROM source_tab;



SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
FROM hr.employees hr_emp;


optimizer_dynamic_sampling
SELECT /*+ dynamic_sampling(employees 1) */ *
FROM employees
WHERE ..,