Re: a pl/sql question
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Apr 2004 16:10:25 -0400
I don't know if this will improve performance but here is an example, use
outer join.
select C.TRC_CUENTA,nvl(A.CTS_NOMBRE,B.CTS_NOMBRE) name
from transace C,
( select CTS_CUENTA,CTS_NOMBRE from cuentaE where cts_TRATAMIENTO = 'IN') A,
( select CTS_CUENTA,CTS_NOMBRE from cuentaE where cts_TRATAMIENTO = 'EG') B
WHERE
C.TRC_CUENTA = A.CTS_CUENTA(+) AND C.TRC_CUENTA =B.CTS_CUENTA(+)
If you could create a materialized view from this I think this could really
help.
If you see when there is records in the second query it will return null
Juan Carlos Reyes Pacheco
OCP
Database 9.2 Standard Edition
----- Original Message -----
From: "Guang Mei" <gmei@xxxxxxxxxx>
To: "Oracle-L-freelists" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, April 29, 2004 3:56 PM
Subject: a pl/sql question
Hi:
I have the following pl/sql function code that gets called a lot in our
application. I have looked all the querys and they are running fine (have
proper indexes, etc). I am now trying to see if I could optimize this code
a bit. One thing I am thinking is that the sub pl/sql block within this
function is creating some "overhead". But if I put the code "select abbrev
into ..." in the function directly (instead of in pl/sql block) then how
could I capture it's exception ("when others then select name into jname
from journal where id=jid;")? Please let me know you have some ideas.
Thanks.
Guang
--- here is the code snip:
begin
select title, volume, pages, year, journalid
into ttl, vol, pg, yr, jid from ref where id=rn;
begin
select abbrev into jname from journal2abbrev
where id=jid and medline='Y';
exception
when others then select name into jname from journal where id=jid;
end;
str := str || ' ' || getAuthorString(rn,maxAuthLen) || ' ' || ttl || '
';
str := str || jname || ' ' || to_char(vol) || ', ' || pg;
str := str || ' (' || to_char(yr) || ').';
return str;
exception
when others then return null;
end RefString;
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
