× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.