Paul Raines wrote:
>
> >> Current implementation of IN is very simple. As you see from EXPLAIN
> >> for each row from mdc1_runs server performes SeqScan on mdc1_simu.
> >> Try to create index on mdc1_simu (version) and let's know about results.
> >> Also, you could create index on mdc1_simu (version, runnum) and re-write
> >> your query as
> >>
> >> select distinct runtype from mdc1_runs where
> >> EXISTS (select * from mdc1_runs where version = '...' and
> >> runnum = mdc1_runs.runnum);
> >>
> >> - this can be faster.
> >>
>
> It was about 4 seconds faster. After creating the indices, the
> above took < 3 seconds, as did the original subselect statement.
Please remember us how long query was in Oracle.
Also, as I understand, subselect with EXISTS takes < 3 sec and
original subselect (with IN) takes ~ 7 sec - is this correct ?
Vadim