Re: delete performance from a standalone sql versus the same call from a procedure

> Hi,
>
> Database Oracle 10.2.0.4 running on Sun Solaris.
>
> I am facing a peculiar issue. I have a package with a procedure that
> handles deletes off a particular table. The delete runs in sub seconds
> for about 10000 rows when I execute it standalone from sqlplus. But
> the very same statement from inside a stored procedure (of the
> package) indicates a full table scan and takes forever to run.
>
> What is it that I may be doing that causes the very same delete to run
> with different plans from a stored procedure as opposed to an
> anonymous sql block?
>
> Thanking you all for your response.
>
> Regards,

You probably need to show us both the procedure and the "anonymous SQL block" (which in itself is a little confusing - if its just a delete, why is it in a anonymous block or do you mean just SQL executed at the sqlplus prompt).

Often, what appears to be equivalent SQL isn't really equivalent. Are you using bind variables? Are you using parameters in one and not the other, does one use a function and the other doesn't in a where clause etc etc.

Tim

-- 
tcross (at) rapttech dot com dot au