× 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.



Thanks, Vern,
I don't know how to do that. But I think I understand : isn't it the fact that my LF contains OMIT on a 3rd field, and this 3rd field isn't excluded in my SQL statement?

I just created the index advised by VE. Copied the existing DDS of the LF and took off the OMIT. Now, (according to VE ) the statement uses the index. I note also that I can't delete the LF again as it's locked by QZDASOINIT. VE? The lock only went when I closed down all my SQL script windows.

What I don't get now, is how is the statement so fast when it DOESN'T use the index? I have about 1000000 records but the result shows almost immediately.

David FOXWELL
Service Informatique
Tél : 03 90 23 91 63
david.foxwell@xxxxxxxxx

P*Pensez à l'environnement avant d'imprimer ce message

-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Vern Hamberg
Envoyé : lundi 19 janvier 2009 14:28
À : Midrange Systems Technical Discussion
Objet : Re: SQL to indicate existance of at least one line

David

I think you want the one that performs best - can you list the things that Visual Explain shows? There might be a way to see it in text form, as I recall.

Both should be able to use an index, if it exists.

By the way, you could also use

select distinct '1' from mytable where field1 = 'A' and field2='000002';

I don't know if that would require a table scan = VE would say what happened.

But please give us the details from VE - we can't speak to this without more information - look for a way t

David FOXWELL wrote:
Hi,

I wanted to initialize a boolean to indicate the presence of at least one line in my table.
I compared these 2 statements in Visual Explain, as I was unsure which to use in my RPG program.

select '1' from sysibm.sysdummy1
where exists (select * from mytable where field1 = 'A' and
field2='000002')

select '1' from mytable where field1 = 'A' and field2='000002' fetch
first row only;

The second statement gives a much simpler graphic, but I suspect that all the records that satisfy the test are copied into a temporary table. I don't understand the image produced for the first example.

Also, in my example, as I have an LF with a key field1, field2 and with OMIT on another field. I'm getting the message from VE that indexes exist but can't be used. The statement is however, very rapidly executed. Could this be that VE isn't working properly?

I prefer the second statement purely for the reason that I don't like having to make references to sysdummy1. Although, obviously, that's not a very good reason!


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

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.