Help needed optimize code

From: Jeff Wyant <wyant_at_wcoil.com>
Date: 1998/03/27
Message-ID: <6fergv$pfe$0@208.10.2.109>#1/1

Below is a sample of the code I am trying to execute. The problem seems   to be with the between statements in the where clause. With the between
  statements the way they are this codes will take about a minute to   execute. If I take the between statements out it will run about 2   seconds. Then here is the weird part. If I put the between statements in
  and use literals instead of variables it will also run about 2 seconds.
  Why is it taking so much longer with I use variables instead of literals?
  Your suggestions are appreciated.

  declare

     ls_curr_day   char(4);
     ls_curr_month char(2);
     ls_curr_year  char(4);
     ls_from_product      char(2);
     ls_to_product        char(2);
     ls_not_from_product  char(2);
     ls_not_to_product    char(2);
     ls_process_group    varchar2(10);
     ls_product_desc     varchar2(10);

  begin

  ls_curr_day := '8132';
  ls_curr_month := '03';
  ls_curr_year := '1998';
  ls_process_group := 'Jumbo';
  ls_from_product := '40';
  ls_to_product := '49';
  ls_not_from_product := '0';
  ls_not_to_product := '0';

  set transaction read write;
  insert into rpt_sbd

       select
          16,
          sum( decode(date_code,ls_curr_day, qty_reject, 0)) day_reject,

          sum( decode(fiscal_month, ls_curr_month, qty_reject, 0))
month_reject,
          sum(qty_reject) year_reject
      from ref_calendar,
           trn_cnts,
           ref_process
     where


       ( ref_process.process_code = trn_cnts.process_code ) and

       ( rtrim(ref_process.process_group) = ls_process_group ) and

           ( trn_cnts.gldate = ref_calendar.date_code ) and
           ( trn_cnts.error  <> '0000' ) and


       ( trn_cnts.error not in ('4444','5555','6666','9999') ) and

       ( ref_calendar.fiscal_year = '1998' ) and

           ( trn_cnts.product_type between
             ls_from_product and ls_to_product ) AND
           ( trn_cnts.product_type not between
                    ls_not_from_product and ls_not_to_product );

  end;
  / Received on Fri Mar 27 1998 - 00:00:00 CST