Re: Best SQL Query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 12 Dec 1998 16:37:29 GMT
Message-ID: <36759a16.3166843@192.86.155.100>

A copy of this was sent to techcell_at_my-dejanews.com (if that email address didn't require changing) On Sat, 12 Dec 1998 06:14:08 GMT, you wrote:

>Hi Everybody,
>
>I have a procedure sp1 which receives a parameter p_cust_id.
> If the parameter p_cust_id is null then
>My query has to fetch all the customers.
> If param p_cust_id is not null then
>My query has to fetch only the specified customer record.
>
>I have 3 queries which can do the same but I am foxed regarding the fact which
>the best one.
>Note: p_cust_id is defined as number(4)
>
>They are
>Query 1
>
>SELECT cust_name
>FROM customer
>WHERE cust_id = Nvl(p_cust_id,cust_id)
>
>Query 2
>
>SELECT cust_name
>FROM customer
>WHERE cust_id LIKE Nvl(p_cust_id,'%')
>
>
>Query 3
>
>SELECT cust_name
>FROM customer
>WHERE (cust_id = p_cust_id
> OR p_cust_id IS NULL)
>
>All the three use the index but which is the best?

are you sure?

query 1: cust_id = nvl(p_cust_id, cust_id)

that cannot use an index when p_cust_id is NULL since the result of NVL must be computed for each row. Its a full scan.

query 2: cust_id like NVL( p_cust_id, '%' )

that cannotuse an index since the NVL is returning a character string and LIKE is a string operatoin. this does an implicit conversion on cust_id to a string and since applying a function to a database column makes an index not usable -- will not use an index.

query 3: may or may not use an index. It depends (on optimizer mode, NULLs allowed in the index or not, and so on).

Perhaps a better way to do this in a procedure in 7.3 and up would be:

create or replace procedure testproc( p_cust_id in number ) as

    type refcur is ref cursor;
    l_cursor refcur;
    l_cname customer.cust_name%type; begin

    if ( p_cust_id is not null ) then

        open l_cursor for
            select cust_name from customer where cust_id = p_cust_id;
    else
        open l_cursor for
            select cust_name from customer;

    end if;

    loop

        fetch l_cursor into l_cname;
        exit when l_cursor%notfound;
        /* .... */

    end loop;

    close l_cursor;
end;
/

>
>Regards
>Rajs(TechCell)
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Dec 12 1998 - 10:37:29 CST