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

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of gmei
> Sent: Monday, February 24, 2003 4:07 PM
> To: Multiple recipients of list ORACLE-L
> Subject: select count(case ...) slow in PL/SQL, any better way?
>
>
> Hi:
>
> Oracle 8173 on Sun Solaris 2.8. I am trying to "optimize" the
> follwoing code
> (in an PL/SQL package) into one table call (instead of two)
>
> select count(1) into count1 from isi.nametag where geneid=geneid1;
> select count(1) into count2 from isi.nametag where geneid=geneid2;
>
> The following code works in sqlplus, but not in PL/SQL:
>
> select count(case when geneid=geneid1 then 1 else null end )
> into count1,
> count(case when geneid=geneid2 then 1 else null
> end ) into count2
> from isi.nametag ;
>
> I have to use dynamic sql to get around this problem. But
> it's perofrmance
> is horrible.
>
> SQL> set serveroutput on
> SQL> declare
> 2 i number:=0;
> 3 str varchar2(200);
> 4 count1 number;
> 5 count2 number;
> 6 begin
> 7 str := 'select count(case when geneid=:x1 then 1 else
> null end ) ,
> 8 count(case when geneid=:x2 then 1 else null end )
> 9 from isi.nametag';
> 10 for x1 in 1 .. 10 Loop
> 11 for x2 in 20 .. 30 Loop
> 12 i := i +1;
> 13 EXECUTE IMMEDIATE str INTO count1, count2 USING x1, x2;
> 14 end loop;
> 15 end loop;
> 16 dbms_output.put_line('i =' || i);
> 17 end;
> 18 /
> i =110
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:10.96
>
> SQL> declare
> 2 i number:=0;
> 3 count1 number;
> 4 count2 number;
> 5 begin
> 6 for x1 in 1 .. 100 Loop
> 7 for x2 in 200 .. 300 Loop
> 8 i := i +1;
> 9 select count(1) into count1 from isi.nametag where
> geneid=x1;
> 10 select count(1) into count2 from isi.nametag where
> geneid=x2;
> 11 end loop;
> 12 end loop;
> 13 dbms_output.put_line('i =' || i);
> 14 end;
> 15 /
> i =10100
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:04.06
>
> Is there a better way to optimize the orginal code? TIA.
>
> Guang Mei
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: gmei
> INET: gmei_at_incyte.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: gmei
  INET: gmei_at_incyte.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).