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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
