Seems to be different queries to me:
select count(case ... ) into count1,
count(case ... ) into count2
from isi.nametag
suggests a full scan on isi.nametag whereas the two
separate queries:
select count(*) from isi.nametag where geneid=geneid1
select count(*) from isi.nametag where geneid=geneid2
have the benefit of a predicate. You could always try
select count(case ... ) into count1,
count(case ... ) into count2
from isi.nametag
where geneid in (geneid1,geneid2)
hth
connor
- Darrell Landrum <dlandrum_at_zalecorp.com> 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: <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).
>
>
> --
> 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).
>
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 Tue Feb 25 2003 - 04:23:50 CST
