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

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Mon, 24 Feb 2003 14:04:41 -0800
Message-ID: <F001.005579D5.20030224140441@fatcity.com>

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.

                                                                                                                   
                    "gmei"                                                                                         
                    <gmei_at_incyte.c       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    om>                  cc:                                                                       
                    Sent by:             Subject:     select count(case ...) slow in PL/SQL, any better way?       
                    root_at_fatcity.c                                                                                 
                    om                                                                                             
                                                                                                                   
                                                                                                                   
                    25/02/2003                                                                                     
                    08:07                                                                                          
                    Please respond                                                                                 
                    to 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).






<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

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:04:41 CST