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

From: <netmadcap_at_netzero.com>
Date: Mon, 24 Feb 2003 14:38:14 -0800
Message-ID: <F001.00557B27.20030224143814@fatcity.com>

I too faced the problem of case not working in pl/sql & procedures. So I created a view.

Would creating a view work for you ?

-----Original Message-----
Sent: Monday, February 24, 2003 3:07 PM
To: Multiple recipients of list ORACLE-L

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: <netmadcap_at_netzero.com
  INET: netmadcap_at_netzero.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).

Received on Mon Feb 24 2003 - 16:38:14 CST