Em 16/4/2013 11:09, skander_sp escreveu:
> May be obvious, but til now, i don't see how much unoptimized works the
> "field in subselect" syntax.
>
> I'm using it in some procedure, just right til now, after check they work in
> the worse unoptimized mode...
>
> example:
>
> select * from Table1 T1
> where T1.id in (select T2.id
> from Table2 T2
> where T2.Customer=:customer)
>
> of course T2.id is a Primary key and T1.id is a Foreing key
>
> I think they resolve getting the value(s) in the subselect, and then applying
> to the main where, but the performace analisys show he run all the T1 table
> from begin to end and then check with the subselect
>
> of course I can solve (in procedure) with a FOR subselect to get every value
> and check after.... not the nicest way, and pretty ugly and less "human
> readable", but by far much more quick and optimized.
>
> for select T2.id
> from Table2 T2
> where T2.Customer=:customer
> into :id do
> select * from Table1 T1
> where T1.id=:id
>
> There is some way to get a good result using "field in subselect"? some trick
> to get an optimized result using this syntax?
>
>
Internally your query is transformed into:
select
*
from
Table1 T1
where
T1.id exists (select
*
from
Table2 T2
where
T2.id = T1.id and
T2.Customer=:customer)
That's why you see a full table scan on T1. Your non correlated sub-query
becomes a correlated sub-query after the transformation.
an easy way to achieve what you want is to convert it to a simple JOIN
like this:
select
t1.*
from
Table1 T1 join
Table2 T2 on (T2.id = T1.id)
where
T2.Customer=:customer
if the relationship is not 1:1 you would need to avoid duplicates with
something like:
select
distinct t1.*
from
Table1 T1 join
Table2 T2 on (T2.id = T1.id)
where
T2.Customer=:customer
Some handling for null values should be taken into account if aplicable
if it will be faster or not depends on your data.
see you !
