Re: sub-select in SQL

Not to worry. Consider my alluding to the effects of the vagaries of the optimizer to have been a bit overzealous to make a point. I should not have done that. One should expect the effect of the subquery to be a static list of values, irrespective of the implementation used to obtain the results. By review of the EXPLAIN one might see that the effects of the subquery, the list of values, could become rows in a temporary TABLE or elements in a temporary INDEX used for index-only access then used to join with the file named on the UPDATE.

Regards, Chuck

On 3/2/11 12:15 PM, hockchai Lim wrote:

Now you are just making me worry. Because I've another process where
I need to perform a reverse sub-select query, which could give me
different result depending on how the sub-select is executed.

declare BL1025INPCursor cursor for
select * from BL1025INP a
where
not a.inseq# in
(select INSEQ#
from BL1025INP b
where
b.inerrFlg = 'Y'
group by
INSEQ#);

Notice this time I use the "NOT a.inseq# in(...)". For each record I
fetched in my RPG program, I'll need to perform additional
validation, if it failed, I'll then update the INERRFLG to Y. If the
sub-select is execute for each row fetch, it could terminate my fecth
loop prematurely.....

I guess to be safe, may be I need to change it to use with
clause.....

"CRPence"<CRPbottle@xxxxxxxxx> wrote in message

Proof by counter-example can easily show otherwise. One should not
be so sure to discount a possible implementation for the IN
predicate derived from a SELECT. Again, one should ask the
optimizer what plan was built for the given query, to best know the
answer to the question by the OP.

On 3/2/11 10:50 AM, Schutte, Michael D wrote:

The OP original query is NON correlated. There's nothing for the
two files to join on. In that example, the sub query would get
executed once. However, if there was a.fld = b.fld in the sub
query then it would/could/should execute a join, thus repeating
the subquery over and over again for each row from table a.

CRPence on Wednesday, March 02, 2011 1:32 PM wrote:

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.