Networks

Ask something
Discussion Locked

Subquery optimization / ORACLE

0 helpful
5 replies

by avhohlov 2 views

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have two tables, DATA (D DATE, P NUNBER -- price, CUR_ID CHAR(3) -- currency code), second table RATES (CUR_ID CHAR(3) -- currency code, D DATE, R NUMBER -- currency rate). I need calculate total price in one fixed currency. Next select stmt are correct, but query time is very long

SELECT SUM(D.P*R.R/R.N)
FROM DATA D, RATES R
WHERE R.CUD_ID=D.CUR_ID
AND R.DATE=(SELECT MAX(D) FROM RATES R2 WHERE R2.CUR_ID=D.CUR_ID AND R2.D<=D.D);

All indexes are present. How transform this query to decrease query time? Including pred date in RATES table and using range scan (R.PRED<=D.D AND D.D

5 replies

by bill_h

(Pursuant to our e-mail conversations):
Your original SQL statement

SELECT SUM(D.S*R2.R/R2.N) FROM DATA D, RATES R1, RATES R2 WHERE (R1.CUR_ID=D.CUR_ID) AND (R1.P<=D.D) AND (D.D

by dim

Hi,
I used at least 3 methods to boost queries like this
1. Simpliest and very efficient. Table RATES shouldn't have any holes. Even if rate didn't change - insert record with the same rate.
2. Create function like this
create function myrate(dt date, cur number) return number
cursor C is select R from rates where cur_id=cur and d <= dt order by d desc;
mr number;
begin
open c;
fetch c into mr;
----- handle errors
close c;
return(mr);
end;
/
and then
sum(d.p/myrate(d.d, d.cur_id))
This is one of that rare case where plsql function in a query is an efficient solution

3. least efficient
create index i on rates(d, cur_id, n);
- get rid of table scan rowid
Good luck

by avhohlov

Most acceptable for me method 2 (using pl/sql function). it ~ 4 times slower than method 1, but method 1 requires additional starage space (currency calculation is simplified example, in real system I also need make another things). Creating index on (D,CUR_ID,N) in my database and explicit using it not increase performance.

This discussion is closed to new replies.