General discussion

Locked

Subquery optimization / ORACLE

By avhohlov ·
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<R.D) or BETWEEN decrease query time but only in two times.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Subquery optimization / ORACLE

by Bill_H In reply to Subquery optimization / O ...

(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<R1.D) AND (R2.CUR_ID=R1.CUR_ID) AND (R2.D=R1.P);

takes a long time to process because each time an R1 record matches a D record, the last two 'AND' clauses compare that R1 record to EVERY R2 record - the number of comparisons is roughly the number of R1 records squared. One of the commands in the script file indicates that 23909 records are created in RATE2, and the contents of RATE2 are subsequently inserted into RATES. The number of comparisons would be 23909 * 23909, or 571,640,281...give or take a few.

I tried to simplify the SQL statement to

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

but the results differed from the first. When I examined the script file again, I noticed that one of the effects of the commandUPDATE RATE2 R SET P=(SELECT MAX(D) FROM RATE2 WHERE CUR_ID=R.CUR_ID AND D<R.D);

was to set P to a null value for the record with the earliest date for each currency type. This null value kept that record from being selected by the simplified SQL statement, thus affecting the value returned by the SUM function.

I modified the SQL statement to

SELECT SUM(D.S*R1.R/R1.N)FROM DATA D, RATES R1 WHERE (R1.CUR_ID=D.CUR_ID) AND (NVL(R1.P,'01-Jan-1980')&lt.D) AND (D.D=R1.D);

The NVL function returns either the R1.P date *or* '01-Jan-1980', ensuring that RATES records will not be improperly excluded from the computation. This statement provided the same result as your original SQL statement, but without the R1 and R2 comparisons. Trythis and see if the performance improves.

Collapse -

Subquery optimization / ORACLE

by avhohlov In reply to Subquery optimization / O ...

Poster rated this answer

Collapse -

Subquery optimization / ORACLE

by dim In reply to Subquery optimization / O ...

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

Collapse -

Subquery optimization / ORACLE

by avhohlov In reply to Subquery optimization / O ...

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.

Collapse -

Subquery optimization / ORACLE

by avhohlov In reply to Subquery optimization / O ...

This question was closed by the author

Back to Networks Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums