Re: Getting the first matching record???

From: John Hough <q6y_at_ornl.gov>
Date: 1997/04/09
Message-ID: <334BEA19.1FD2@ornl.gov>#1/1

Z. Martinez wrote:
>
> I have a large table (about 4 million rows) that I need to search
> through. Although the query I'm using can result to multiple rows, I
> only need the first matching row. This query will be executed probably
> a hundred thousand times. So I really need to optimize it
>
> What is the most efficient way of getting the first matching record
> PL/SQL? and Pro*C?
>
> Thanks in advance.
>
> Please send your response to zlm101_at_psu.edu

Z:

I don't know if this is the most efficient but here is the way I performed a similar function in PL/SQL;

---

DECLARE
    v_username dba_users.username%TYPE;
    v_counts number;

    cursor c_nm_dbauser is

        select username from dba_users where length(username) > 3
          minus
        select name from system.non_managed

    ;

BEGIN     v_counts := 0;
    OPEN c_nm_dbauser;
    LOOP

       FETCH c_nm_dbauser into v_username;
       EXIT WHEN c_nm_dbauser%NOTFOUND;
--     Any time a record is found we want to exit the loop
--     so we will add one to the value v_counts and exit.  We
--     use this cursor instead of an equivalent select with
--     a nested inner select because of performance considerations 
       v_counts := v_counts + 1;
       EXIT WHEN v_counts > 0;

    END LOOP;
    CLOSE c_nm_dbauser;

    if v_counts > 0 then

        dbms_output.put_line('No users found);     end if;

END;
/

Hope this helps,

John Hough Received on Wed Apr 09 1997 - 00:00:00 CDT