RE: select count(case ...) slow in PL/SQL, any better way?

responses to 2 posts: comments below each post.

> -----Original Message-----
> From: gmei [mailto:gmei_at_incyte.com]
> Sent: Monday, February 24, 2003 4:53 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: select count(case ...) slow in PL/SQL, any better way?
>
>
> Hi:
>
> Just after I sent my original message, it occured to me that
> I could use
> this to "optimize" the sql (gneid is the PK column of the table):
>
> select count(A.geneid), count(B.geneid) into count1, count2
> from isi.nametag A , isi.nametag B where A.geneid=geneid1 and
> B.geneid=geneid2;
>
> Guang
>

This won't be good because it will do a cartesian join of the tables.

SQL> select * from test_user.cps1;

        ID NAME

---------- -------------------- 
         1 blah
         1 blah
         2 blah2
         2 blah2
         2 blah2

SQL> select count(id) from test_user.cps1 where id = 1;

 COUNT(ID)

         2

SQL> select count(a.id), count(b.id)
 from test_user.cps1 a, test_user.cps1 b  where a.id = 1 and b.id = 2;

COUNT(A.ID) COUNT(B.ID)
----------- -----------

          6 6

If it is the primary key you won't get the wrong numbers but that is more or less by accident.

And as Stephane Faroult points out, it will fail altogether if one of the values doesn't exist (but you won't know which one doesn't exist).

> -----Original Message-----
> From: Mark Richard [mailto:mrichard_at_transurban.com.au]
> Sent: Monday, February 24, 2003 5:05 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: select count(case ...) slow in PL/SQL, any better way?
>
>
> Hi,
>
> How about a statement like
>
> select count(decode(geneid, geneid1, 1, 0)),
> count(decode(geneid, geneid2, 1, 0))
> into count1, count2
> from isi.nametag
>
> This should produce the same result I believe, and is one
> single simple SQL
> statement so PL/SQL should eat it up just fine.
>
> Regards,
> Mark.
>

This is more the solution I was going to recommend but I'd do this instead:

select sum(decode(geneid, geneid1, 1, 0)),

         sum(decode(geneid, geneid2, 1, 0)) into count1, count2
from isi.nametag
where geneid in (geneid1, geneid2);

If these are primary key values you are really just looking for existence, in this case 'count' would work just as well, but you still want to avoid the full index scan.

-Chris

LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  INET: csarnows_at_CuraGen.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).