Re: Explain Plan For Query

Well, I put in the (obvious) hint for it to use the primary key, which is based on the column VECTOR_ID, and that fixes it.

In general, I've always been unsure whether to put hints in all my embedded queries. Sometimes, hints seem to lead the optimizer astray. Sometimes, as in this case, they're necessary. Wah-boo-dong??

Salaam

yitbsal_at_yahoo.com (Salaam Yitbarek) wrote in message news:<77439c33.0204110715.12487d1d_at_posting.google.com>...
> Hi,
>
> We're running 8.1.7 on a Solaris 5.7 box.
>
> I have the following simple cursor in a stored procedure. It fetches,
> at most, 2 records. I only want the first.
>
> CURSOR cur_Terminated IS
> SELECT TERMINATED
> FROM VECTOR
> WHERE VECTOR_ID = VectorId_in
> ORDER BY VERSION DESC;
> ...
> OPEN cur_Terminated;
> FETCH cur_Terminated INTO var_Terminated;
> CLOSE cur_Terminated;
> ...
>
> It takes over 4 minutes to run.
>
> The same query, run from the sql prompt, takes milliseconds, and has a
> good explain plan.
>
> Why is it taking so long when run from a stored procedure? Anyone had
> this happen to them before? Help?!!
>
> Thanks,
> Salaam
Received on Fri Apr 12 2002 - 07:40:09 CDT