Re: SQL to only check for existence:

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

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.