Optimizer hints can be used with SQL statements to alter execution plans. This chapter explains how to use hints to force various approaches.
/************||| Hints for Optimization approach and goals|||***/
select /*+ all_rows */ employee_id , last_name , first_name from
employees
order by employee_id;
select /*+ first_rows */ employee_id , last_name , first_name from
employees
order by employee_id;
create table emp2
as select * from employees;
select /*+ choose */ employee_id , last_name , first_name from
emp2
order by employee_id;
select /*+ rule */ employee_id , last_name , first_name from
employees
order by employee_id;
/************||| Hints for enabling optimizer features|||***/
select /*+ optimizer_features_enable('11.1.0.1') */ employee_id , last_name , first_name from
employees
order by employee_id;
/************||| Hints for access paths|||***/
-----full
select employee_id , last_name , first_name from
employees
order by employee_id;
select /*+ Full(e)*/ employee_id , last_name , first_name from
employees e where employee_id<300
order by employee_id;
---hash
select /*+ hash(e)*/ employee_id , last_name , first_name from
employees e employee_id<300
order by employee_id;
---index
select /*+ index( e emp_department_ix)*/ employee_id , last_name , first_name from
employees e employee_id<300
order by employee_id;
---index_asc
select /*+ index_asc( e emp_department_ix)*/ employee_id , last_name , first_name from
employees e employee_id<300
order by employee_id;
---index_cobine
select /*+ index_cobine( e manager_id emp_department_ix)*/ employee_id , last_name , first_name from
employees e manager_id=108 or department_id=110
order by employee_id;
---index_join
select /*+ index_join( e employee_id emp_department_ix)*/ employee_id , last_name , first_name from
employees e employee_id<108 and department_id<=110
order by employee_id;
---index_ffs
select /*+ index_ffs( e employee_id)*/ employee_id , last_name , first_name from
employees e
order by employee_id;
---index_ss
select /*+ index_asc( e employee_id_id emp_department_ix)*/ employee_id , last_name , first_name from
employees e last_name='king'
order by employee_id;
---no_index
select /*+ index_asc( e)*/ employee_id , last_name , first_name from
employees e last_name='king'
order by employee_id;
---use_hash
select employee_id , last_name , first_name, department_name from
employees e , departments where e.department_id=d.department_id
order by employee_id;
select /*+ use_hash( e d)*/ employee_id , last_name , first_name, department_name from
employees e , departments where e.department_id=d.department_id
order by employee_id;
---use_no_hash
select employee_id , last_name , first_name, department_name from
employees e , departments where e.department_id=d.department_id
order by employee_id;
select /*+ use_no_hash( e d)*/ employee_id , last_name , first_name, department_name from
employees e , departments where e.department_id=d.department_id
order by employee_id;
---use_nl
select * from
employees e , departments where e.department_id=d.department_id
order by employee_id;
select /*+ use_nl( e d)*/ * from
employees e , departments where e.department_id=d.department_id
order by employee_id;
---use_nl_with_index
select /*+ use_nl_with_index(e employee_department_indes*/ * from
employees e , departments where e.department_id=d.department_id
and e.department_id<=40
order by employee_id;
---use merge
select * from
employees e , departments where e.department_id=d.department_id
order by employee_id;
select /*+ use_merge(e d) */ * from
employees e , departments d where e.department_id=d.department_id
order by employee_id;
---use_leading
select * from
employees e , departments d , job_history j where e.department_id=d.department_id
and e.hire_date=j.start_date
order by employee_id;
select /*+ use_leading(e j) */ * from
employees e , departments d , job_history j where e.department_id=d.department_id
and e.hire_date=j.start_date
order by employee_id;
-----parallel----
select /* parallel(auto) */ * from emp1;
select /* parallel(manual) */ * from emp1;
select /* parallel(e, 3) */ * from emp1 e;
-----parallel_index----
select /* parallel_index(e, emp_id, 3) */ * from emp1 where employee_id<50;
alter table empl parallel 4;
select /* no_parallel(e) */ * from empl e;
--------cache
select /* + full (j) cache(j) */ * from jobs j;
--------append
insert /*+ append */ into empl_bkp select * from emp;
----------result_cahce
select /*+ result_cache */ * from
employees e , departments d , job_history j where e.department_id=d.department_id
and e.hire_date=j.start_date
select /*+ result_cache */ * from
employees e , departments d , job_history j where e.department_id=d.department_id
and e.hire_date=j.start_date
====================
SELECT iu.ship_product_id
AS part_number, iu.merge_id AS fc_code,
oa.country_code, COUNT (1) quantity
FROM shipping.shippable sh,
shipping.order_address oa,
shipping.item_unit iu
WHERE
sh.manifest_date >
TRUNC (SYSTIMESTAMP AT TIME ZONE 'GMT')- (7 * 13)
AND oa.order_id = sh.order_id
AND oa.addr_type_code = 'S‘
AND iu.order_id = oa.order_id
AND iu.merge_id IS NOT NULL
GROUP BY iu.ship_product_id, iu.merge_id, oa.country_code
