Is there any difference in optimizing a plan to execute any sql request in case of using a procedure from a package which checks to display row or not and in case of constructing a plain sql query without procedures ?
I mean, should be any difference in sql plans ? And how it might affect the performance of execution.
for instance
select * from atbl t where checkme(t.fld) = 1;
or
select * from atbl t where t.fld = 'OK'
Thank you in advance.
Yes, there can be changes in the plan, and they can be quite drastic in terms of performance.
To be sure in your specific case, there's only one way to find out: look at the plans and check (i.e. measure with real data and up-to-date statistics) the performance.
The potential problem with your query with the function is that it will prevent the optimizer from using an index on atbl.fld if there is one – this can result in drastic performance differences.
If there isn't an index, you'll probably get the same plan.
(And if checkme is deterministic you can create a function-based index to optimize that query if necessary.)
