Search code examples
oracle-databaseplsqloracle11ghint

Use hint only if condition is met

My PL/SQL function does an insert into a table:

procedure MY_PROC(n in number default null) is
begin
    insert into MY_TABLE T
    select ...
    from ...;
end;

My problem is: if n is null, the query is very slow and I found that using the materialize hint (yes I know it's undocumented) solves the problem. BUT if n is not null then the query, which was fast, is now slow because of this hint !

So is there a way to use a hint only if a condition is met ? I tought about using dynamic SQL (execute immediate) but I would like to avoid this.

Thanks !

Solution

  • You have to write two insert statements, one with the hint and one without it, and seperate them with a 

    if n is null then
   ... statement with hint
else
   ... statement without hint
end if;