Hi Tom,

Yes, I am talking about PL/SQL.

Even for dataware house queries I find it more appealing to use stored procedures that returns ref cursors.

But the problem is when the number of inputs to the procedure increases, the various possible combinations of the inputs result in large amount of 'if else's. For example for two inputs I've to code

if p_in_1 is not null then
  open p_cur for select ... where p_in_1 =
elsif p_in_2 is not null then
  open p_cur for select ... where p_in_2 =
elsif p_in_1 is not null and p_in_2 is not null then
  open p_cur for select ... where p_in_1 = and p_in_2 = 
else
  open p_cur for select ...
end if;

As a compromise, I tend to code as

  declare
    v_1_selected number := 0;
    v_2_selected number := 0;
  begin
    if p_in_1 is not null then
      v_1_selected := 1;
    end if;
    if p_in_2 is not null then
      v_2_selected := 1;
    end if;

    open p_cur for 
      select ..
      where (v_1_selected = 0 or (v_1_selected = 1 and 
              p_in_1 = ..) and 
             v_2_selected = 0 or (v_2_selected = 1 and 
              p_in_2 = ..))
   end;

But I know that this will not genarate the optimum plan. I wanted to overcome this. Do you have any suggestion?

Regards,

Albert Nelson A.

(Tom, long review ... but something I think you'll find interesting).

I've seen myself tons of pl/sql code in DWH/DSS systems, especially for the data loading/transformation (nightly) process, which is normally much more critical, performance-wise, then the (daily) report process, and so asks for the most accurate plans possible (and for pl/sql when sql is not enough). And I do agree that coding everything using dynamic sql to avoid using bind variables is cumbersome, error-prone and costly.

With bind variable peeking, using literals or bind variables should get the same plan - unless of course you reparse the same statement with different values for the binds.

So the problem can be reformulated (from pl/sql to sql) as "tell the CBO that this SQL statement has to be reparsed if the values of bind variables change"; since the values of the peeked binds are recorded (check v$sql_plan.other_xml for v$sql_plan.id=1 - thanks to Jonathan Lewis for letting me know about this), it shouldn't be neither difficult nor expensive to check it at exec time.

So, why not a new hint such as /*+ reparse_on_bind_change */; it would be a "good" hint, that gives more information to the CBO, similar to "first_rows" or "cardinality".

Alberto Dell'Era has expressed my feelings more clearly. /*+ reparse_on_bind_change */ is what I think is needed.

Thanks for also sharing about info on v$sql_plan.other_xml

Regards,

Albert Nelson A.