Re: sub-select in SQL

[CRPence <CRPbottle@xxxxxxxxx>]

On 3/2/11 10:02 AM, hockchai Lim wrote:
> For the SQL below, does any one know if the sub-select in the where
> statement gets execute only once or it gets execute for each row in
> BL1025INP file?

  The answer is in the domain of the optimizer, not of a reviewer of 
the SQL.  Even if known what the optimizer chooses today, does not imply 
what the optimizer will choose tomorrow.  However very probably the 
subselect is not performed, not even as a parameterized query, for every 
row in a full table scan.  Most likely that query is implemented as a 
join on INSEQ# with either an existing index on that column, a temporary 
index on that column, or a full table scan if the cardinality of the 
matches can be inferred or cardinality of the distinct values is low 
according to available statistics.

> update
>     BL1025INP a
>   set
>     a.INERRFLG = 'Y'
>   where
>     a.inseq# in (
>      select inseq#
>       from BL1025INP b
>       where
>         b.inerrFlg = 'Y'
>       group by
>         INSEQ#)
      /* perhaps adding to the above WHERE clause: */
     and a.inerrflg<>'Y' /* no reason to change those.? */

   So... Ask what the optimizer decided, EXPLAIN the statement [query] 
in iNav.  Even the deprecated "optimizer debug messages" are likely to 
explain the query sufficiently to infer the subquery is not run repeatedly.

Regards, Chuck