Re: Simple SQL request

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Fri, 14 Jan 2005 17:55:34 +0100
Message-ID: <cs8tg7$q4q$1@news5.zwoll1.ov.home.nl>

Jean-Baptiste > wrote:

> Mark C. Stock wrote:
> 

>> "Bavoux Jean-Baptiste" <jbb1_at_laposte.net> wrote in message
>> news:41e7ac4a$0$10272$626a14ce_at_news.free.fr...
>>
>>> My need is to search if there is at least one recordset in a table.
>>> (from a procdure in Oracle)
>>>
>>> My solution is:
>>>
>>> my_count PLS_INTEGER;
>>>
>>> ...
>>>
>>> SELECT COUNT(*) INTO my_count FROM my_table WHERE (my_filters ...);
>>> IF (my_count >= 1) THEN
>>> -- ok
>>> ELSE
>>> -- nok
>>> ENDIF
>>>
>>> The probleme is that i don't need to count them all.
>>> Il my table has 1 000 0000 recorset and the half match my filters
>>> it will be very slow even if the first recorset matchs my filters.
>>>
>>> I ve try to do thinks like:
>>>
>>> CURSOR my_cursor IS
>>> SELECT 1
>>> FROM my_table
>>> WHERE (my_filters ...);
>>>
>>> BEGIN
>>>
>>> OPEN EMP_CUR; -- open the cursor
>>> FETCH EMP_CUR INTO v_FoundOne;
>>> CLOSE EMP_CUR;
>>>
>>> IF (v_FoundOne IS NOT NULL) THEN
>>> -- ok
>>> ELSE
>>> -- nok
>>> ENDIF
>>>
>>> It works but I find that the code i not very simple.
>>>
>>> Is there another solution?
>>> Something like SELECT FIRST(*) FROM my_Table WHERE ...
>>
>>
>>
>> look into cursor for loops or 'where exists' sub-queries
>>
>> ++ mcs
>>

> 
> 
> temp PLS_INTEGER;
> BEGIN
>      SELECT 1 INTO temp FROM DUAL
>      WHERE EXISTS
>     ( SELECT * FROM my_table WHERE (my_filters...);)
>     IF (temp IS NULL) THEN
>         ...
>     ELSE
>         ...
> 
> This solution seems as fast as the CURSOR method.
> The syntax is simplier than CURSOR method,
> (not obliged to declare a cursor)
>  but still complicated... (may be it can be simplified)
> I would have prefer
>     IF EXISTS ( SELECT * FROM my_table ..;)
> but this doesn't work.
> 
> The method with ROWNUM doesn't work because ROWNUM is the row number 
> from the table and not from the result.

rownum is a pseudo column, designating the row number *within* the result set. Test it's performance!

As Mark and I said: code the "OK' part as: update table_x where exists (select * from table_a where....)

-- 
Regards,
Frank van Bortel

Received on Fri Jan 14 2005 - 10:55:34 CST