RE: SQL Oddity

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

The messages will be in your joblog for interactive. I forget whether yours
or the serviced job,if another job.

At 01:27 PM 3/13/02 -0600, you wrote:
>Execute STRDBG. Then run your query. If it's in another job, do strsrvjob
>first. If a batch job, hold the job queue first, then strsrvjob, then
>release the job.
>
>You'll get messages that tell you what index is being used, etc. Also lots
>of other info. Would be interesting to know..
>
>At 11:11 AM 3/13/02 -0800, you wrote:
>>Eric,
>>
>>This I would expect from two files.  The only thing was, I had only one file
>>I was selecting from.  The exact SQL statement I used was:
>>SELECT * FROM INP95 WHERE URID95 = 'PRISM     '
>>
>>I just can't figure out how SQL got so confused to look at each record 200+
>>or so times each to figure out if it was 'PRISM     ' or not.  Perhaps SQL
>>was using some weird logical file it had found.  I just don't know.
>>
>>Regards,
>>
>>Jim Langston
>>
>>From: "DeLong, Eric" <EDeLong@Sallybeauty.com>
>>
>>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.
>>_______________________________________________
>>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.