RE: SQL oddity

I've seen this a lot when using sub selects in a statement.  Consider:

Select * from item_master
where vendor in (select vnd
                        from vndmst
                        where crt_date = current_date)

I believe (conjecture based on observation) that if the sub-query returns a
small result set, say 30 rows, then the SQL optimizer will just scan the
result set for a match on each item_master record.  For 1 row of
item_master, if it did not find a match in the sub-query, the number of
records process would show 31.


Eric DeLong
Sally Beauty Company
MIS-Sr. Programmer/Analyst
940-898-7863 or ext. 1863



-----Original Message-----
From: Vernon Hamberg [mailto:vhamberg@attbi.com]
Sent: Tuesday, March 12, 2002 7:53 PM
To: midrange-l@midrange.com
Subject: Re: SQL oddity


By default, every time you move around in the display of the results of an
SQL select statement, the data is refreshed. You can change this behavior
with the REFRESH parameter of the STRSQL command, or in F13.

 From the help text:

REFRESH

Specifies when the display select output data is refreshed.

*ALWAYS: Data is normally refreshed during forward and backward scrolling.

*FORWARD: Data is refreshed only during forward scrolling to the end of the
data for the first time.
When scrolling backward, a copy of the data already viewed is shown.

But this doesn't seem enough to explain the high numbers. A number that
high usually means some kind of extreme join.

At 04:35 PM 3/12/02 -0800, you wrote:
>I'm looking at a file, INP95 that has 277206 records and 10662 deleted
>records.
>STRSQL
>select * from INP95 where URID95 = 'PRISM     '
>I get a list of 14000 odd records, which is right.
>I type B in the Position to line
>It finally finishes after processing 41,552,000 odd records.  Where did the
>other records come from, or just what in the world was SQL doing?
>
>This was an adhoc query, so it probably doesn't matter, I'm just curious,
>what records was SQL processing?
>
>Regards,
>
>Jim Langston
>
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.