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

Thanks!

>>> optimaldba_at_yahoo.com 02/24/03 06:28PM >>>
CASE appeared (with little fanfare) in 8.1.6. However, until Oracle9, it was not available in PL/SQL, when they integrated the PL/SQL engine into the kernel.

Darrell Landrum wrote:

>I thought case in PL/SQL was not available until 9i. I'll have to look that up.
>
>
>
>
>>>>netmadcap_at_netzero.com 02/24/03 04:38PM >>>
>>>>
>>>>
>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: Darrell Landrum
  INET: dlandrum_at_zalecorp.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).