Hi Maurizio!

Maurizio P. wrote:

> hi ,
> is it possible to make nested select faster ?
>
> something like this :
>
> SELECT OD.ornumord , OD.ordatord , OD.ortipord , OD.ordescli ,
> OD.oridscar , OD.orrifpal , OD.paragsoc
> FROM tordmaster OD , torddetaildetail OT
> WHERE OD.oridordi = OT.otkeymas
> AND OT.otidriga IN
> (
> SELECT DDD.dv_idord
> FROM tdettvend DDD
> where DDD.dv__key2 = 1670
> )
> GROUP BY OD.ornumord , OD.ordatord , OD.ortipord , OD.ordescli ,
> OD.oridscar , OD.orrifpal , OD.paragsoc

Here are at least two different ways to do something similar:

SELECT OD.ornumord, OD.ordatord, OD.ortipord, OD.ordescli,
OD.oridscar, OD.orrifpal, OD.paragsoc
FROM tordmaster OD
where exists(
select * from torddetaildetail OT
join tdettvend DDD on OT.otidriga = DDD.dv_idord
WHERE OD.oridordi = OT.otkeymas
and DDD.dv__key2 = 1670)

SELECT DISTINCT OD.ornumord, OD.ordatord, OD.ortipord, OD.ordescli,
OD.oridscar, OD.orrifpal, OD.paragsoc
FROM tordmaster OD
join torddetaildetail OT on OD.oridordi = OT.otkeymas
join tdettvend DDD on OT.otidriga = DDD.dv_idord
WHERE DDD.dv__key2 = 1670

Though without knowing anything about the size and contents of your
tables, selectivity/existence of indexes and PLANs - no-one can do
anything better than guessing.

Well, that is, any big table without an indexable where (or join) clause
will be slow, so I can tell you that the first of the above suggestion
will be slowish in all cases if tordmaster contains millions of records,
whereas the other tables only contain a handful. Hence, I'd normally go
for my second suggestion.

HTH,
Set