Chuck,
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.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, March 02, 2011 1:32 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: sub-select in SQL
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.
midrange.com
