Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.)

share|improve this answer
    
thank you man, for the clear answer! –  Flextra Nov 20 '13 at 10:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.