Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US