Обсуждение: query evaluation

Hi all,

I have the following query.

select part_id, consume_part(part_id) from part where in_use = false;

does it:
    a/  iterate over all parts, calling consume on them and then show the records where in_use is false (i.e. all parts have been consumed by the SP consume_part) or

    b/ filter the list using in_use = false and apply consume_part SP to those part_id's ?

It is very _scary_ to imagine the first prospect, worse is if it did a mix of a/ and b/ ... where it does not apply consume_part on all parts but a partial set of parts that are exclusive of the set provided by SP, because then it would be unknown how the query worked (atleast if it consumed all parts then you could do something).

--
:wq

On Wed, Nov 26, 2008 at 6:36 PM, Ravi Chemudugunta
<chemuduguntar@gmail.com> wrote:
> Hi all,
>
> I have the following query.
>
> select part_id, consume_part(part_id) from part where in_use = false;
>
> does it:
>     a/  iterate over all parts, calling consume on them and then show the
> records where in_use is false (i.e. all parts have been consumed by the SP
> consume_part) or
>
>     b/ filter the list using in_use = false and apply consume_part SP to
> those part_id's ?
>
> It is very _scary_ to imagine the first prospect, worse is if it did a mix
> of a/ and b/ ... where it does not apply consume_part on all parts but a
> partial set of parts that are exclusive of the set provided by SP, because
> then it would be unknown how the query worked (atleast if it consumed all
> parts then you could do something).

I'm quite sure it fires the where clause first, but to prove it to
yourself, run it with explain analyze and look at the query plan.