-- PL/SQL implementation of the modified Jonathan Lewis' standard formula
-- fringe cases of "range completely inside one of the bands" not implemented
-- tested against the CBO estimates - perfect fit +- 1 due to CBO rounding policy (formula doesn't round)
-- tested on 10.2.0.2 only 

set echo on

create or replace function range_sel_formula (
  low#         varchar2,
  low_x        number,
  high#        varchar2,
  high_x       number,
  min_x        number,
  max_x        number,
  num_rows     number,
  num_distinct number
)
return number
is
  B                            number  := (max_x - min_x) / num_distinct;
  low_x_effective              number  := low_x;
  high_x_effective             number  := high_x;
  low_x_inside_left_band       boolean :=  low_x < min_x + B;
  high_x_inside_right_band     boolean := high_x > max_x - B;
  correction_for_or_equal_oper number  := 0;
  correction_for_special_case  number  := 0;
  height_ramp                  number  := num_rows / num_distinct;
  l_cardinality                number;
begin
  -- input parameters sanity checks
  if    low# in ('>=', '>') and high# in ('<=', '<')
    and low_x  >= min_x and low_x  <= max_x
    and high_x >= min_x and high_x <= max_x
    and low_x < high_x
    and min_x < max_x
    and num_rows >= 0
    and num_distinct >= 1
  then 
    null;
  else
    raise_application_error (-20001, 'range_sel_formula() : illegal parameters');
  end if;

  -- completely in left band
  if low_x < min_x + B and high_x < min_x + B then
    -- not implemented
    return -10;
  end if;

  -- completely in right band
  if low_x > max_x - B and high_x > max_x - B then
    -- not implemented
    return -11;
  end if;

  -- low_x_effective and high_x_effective
  if low# = '>=' and low_x_inside_left_band then
    low_x_effective := min_x + B;
  end if;

  if high# = '<=' and high_x_inside_right_band then
    high_x_effective := max_x - B;
  end if;
  
  -- correction_for_or_equal_oper
  correction_for_or_equal_oper := (1 / num_distinct) 
    * ( (case when low#  = '>=' then 1 else 0 end) 
      + (case when high# = '<=' then 1 else 0 end)
      );

  -- correction_for_special_case
  if low_x  = min_x and low#  = '>' then
    correction_for_special_case := correction_for_special_case + (1 / num_distinct);
  end if;

  if high_x = max_x and high# = '<' then
    correction_for_special_case := correction_for_special_case + (1 / num_distinct);
  end if;

  l_cardinality := num_rows * (high_x_effective - low_x_effective) / (max_x - min_x)
                 + num_rows * correction_for_or_equal_oper
                 - num_rows * correction_for_special_case;
  
  -- round only to compensate for numerical errors
  return round (l_cardinality, 9);
end range_sel_formula;
/
show errors;

select range_sel_formula ('>', 0.1, '<', 7, 0, 10, 4000000, 4) from dual;