There is a stored procedure that returns sys_refcursor. The code for this procedure is: (btw, fk and pk are indexed):
procedure GET_SMTH(
res_cv_out out sys_refcursor,
<in parameters>)
is
begin
open res_cv_out for
select
t1.<some fields>, t2.<some fields>, <etc.>
from
table1 t1
left outer join
table2 t2
on t1.pk = t2.fk
left outer join
table3 t3
on t3.pk = t2.fk
<etc. many(~10) tables>
where ( param1 is null
or
( param1 is not null and t2.param1 like '%'||param1||'%' )
)
and ( param2 is null
or
( param2 is not null and nvl(t2.param2, t3.param2) like '%'||param2||'%' )
)
and ( param3 is null
or
( param3 is not null and t3.param3 like '%'||param3||'%' )
)
<etc. many(~15) parameters>;
end;
In the table1 table there are about 500kk rows, in the rest, for example, about 500k rows (in some about 1k). About 0.0005% of table1 rows usually satisfy the condition.
If, for example, there is an index on table3.param3 - and only param3 (other parameters is null) will be passed to the procedure - will it work normally (quickly) (as if only the param3 was in the where section) or not?
That is, for the case described above - Oracle in runtime changes the query above to such (accordingly with the change of the plan) or not? :
select
t1.<some fields>, t2.<some fields>, <etc.>
from
table1 t1
left outer join
table2 t2
on t1.pk = t2.fk
left outer join
table3 t3
on t3.pk = t2.fk
<etc. many(~10) tables>
where t3.param3 like '%'||param3||'%'
How is it better to solve such a task?
