Using Outline for RULE Based optimizer system

From: Tanmoy <tanmoydc@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Date: Sun, 24 Jul 2005 03:26:03 -0400

Hi,

  I have an oracle HRMS system running on 11o and the database version
is 8.1.7. The optimizer mode is RULE.

To tune couple of long running queries i was thinking of using stored
outlines. However the queries i am goining to be tuned uses bind
variable.

Such cases how to use the outline ? 

<Original Query>

select pev.screen_entry_value amount,
         ncr.add_or_subtract add_or_subtract,
         TO_DATE(pev1.screen_entry_value, 'DD-MON-YYYY') effective_date
  from   pay_accrual_plans pap,
    pay_net_calculation_rules ncr,
    pay_element_entry_values_f pev,
         pay_element_entry_values_f pev1,
    pay_element_entries_f pee
  where  pap.accrual_plan_id = p_plan_id
  and    pee.assignment_id = p_assignment_id
  and    pee.element_entry_id = pev.element_entry_id
  and    pev.input_value_id+0 = ncr.input_value_id
  and    pap.accrual_plan_id = ncr.accrual_plan_id
  and    ncr.input_value_id <> pap.co_input_value_id
  and    ncr.input_value_id <> pap.pto_input_value_id
  and    pev1.element_entry_id = pee.element_entry_id
  and    pev1.input_value_id+0 = ncr.date_input_value_id
  and    ((p_input_value_id is NOT NULL and
           p_input_value_id = ncr.input_value_id) or
           p_input_value_id is NULL);


<Outline Query Should be >

I want to use /*+ CHOOSE */ hint to the above query. How to overcome's
the parameter (p_input_value_id ,p_plan_id,p_assignment_id) in this
case ?

I also heard that queries having OR condition has some caveats in 8.1.7.

Does the outline query have to be verbatim with the original one?

I did go through metalink, and dbazine artile by Jonathan Lewis. But
did'nt find any direct answer.

Is there any step by step guide for outline available anywhere ? 


-- 
Thanks,
Tanmoy 

-- "Time is the coin of life. Only you can determine how it will be spent."
--
//www.freelists.org/webpage/oracle-l

Using Outline for RULE Based optimizer system

Other related posts: