|
At 04:52 PM 4/18/2006, you wrote:
Vern, On 4/18/06, vhamberg@xxxxxxxxxxx <vhamberg@xxxxxxxxxxx> wrote:> Are you sure there's no where clause? It's in the subquery (where exists...), I think.>> The point is that "where exists" does not do a full table scan to return a result, therefore it is very fast.I meant, no where clause in the subselect inside the where exists().
Ah, I did not remember you want to know if a selection has any records - but the beauty of EXISTS is that the SQL engine is optimized to get out early as soon as the EXISTS predicate is successful. So it should be as fast as possible. One hopes. ;-)
mine had a LIKE '____xxxx___' compare in the subselect, which runs much slower, especially if the first match is deep into the access path it chooses. I tested it - if the first match was towards the beginning of the chosen access path, it found it sub-second. near the end, it was more like 6-10 secs.> I'm not sure whether the select into is faster - probably no noticeable difference.no, I don't think select '1' is any faster, I just like it better. it returns 1 to my indicator variable when successful, and I can test for (not $found). I tried both ways and found no noticable difference.
I must have misread what you posted - I did not see the apostrophes around the digit. I like your idea here, too, and the host variable i could just as well have been an indicator type. Then your (not i) works, too. Nice!
> To explain further, the "select 1 from..." is working against a single-row table, hence it > returns a single value and can be assigned to the host variable (I think that makes > sense). It'd probably fail if the "select 1 from..." returned multiple rows.my first attempt (without the sysdummy1 file) did fail. I'd never heard of the sysdummy1 file.
Yeah, that file is part of the work IBM did to make the iSeries version of DB2 more like the rest of the family. Library SYSIBM was added somewhen, I forget. There will be a collection of that name in all the other DB2 flavors. SYSDUMMY1 just happens to be a single-record table for the purpose of getting back a single row. An alternative is to use DISTINCT on whatever file you use in the outer SELECT, but this is guaranteed to be 1 row, hence minimal effort. The single column is a VARCHAR of length 1. The SYSIBM library is intended to make it easier to port from other DB2 to iSeries.
There is another single-record file, QSYS2/QSQPTABL, that has a single 4-byte integer field.
> The idea is to test for records in a file, so that file name goes into the innermost select.> > HTH it does, thanks! Rick -- 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.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
