Home » RDBMS Server » Performance Tuning » sql query optimization
sql query optimization [message #272479] Fri, 05 October 2007 04:00 Go to next message
Messages: 3
Registered: September 2006
Junior Member
Os. Win 2003 server

I have this 2 table :
Create table cst(codcli varchar(10) not null,codprod varchar(10) not null,qta numeric);
Create table prodprice(codprod varchar(10) not null,data date not null,price numeric);
create index prodpriceka (codprod,data);

i want select for every customer and product in table cst the last price in table prodprice.
The table prodprice contains millions of rows, a row for every day for every product for a lot of years.
i try :
select codcli,codprod,qta,(select /*+ INDEX_DESC(PRODPRICE PRODPRICEKA) */ price from prodprice
where codprod=t.codprod and data<=trunc(sysdate) and rownum=1) myprice
from cst t

there is a better solution ?

Re: sql query optimization [message #272488 is a reply to message #272479] Fri, 05 October 2007 04:35 Go to previous message
Michel Cadot
Messages: 65137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use join and not scalar subquery.
Having "rownum=1" in a scalar subquery means:
1/ This must not be used
2/ You have a problem in your design and/or data

Move to Performances Tuning forum, follow the sticky of this forum.

Previous Topic: Performance Tuning
Next Topic: Help reading TOAD Explain Plan
Goto Forum:

Current Time: Thu Aug 17 01:11:12 CDT 2017

Total time taken to generate the page: 0.08782 seconds