Re: very slow outer join

Try to do explain plan for both cases - with outer join and with regular join. I'm sure you will see where is the problem. You need to do regular query performance tuning.
Artem

<gene01_at_smalltime.com> wrote in message
news:tejn65p4rm4g73_at_corp.supernews.com...
> I am doing an SQL query which uses an outer join. The
> query takes forever to run (forever = more than I'm willing
> to wait). If I change the outer join to a regular join,
> it takes just over four seconds. I'm looking for some
> advice on how I can improve this situation. (This is
> on Oracle 8i (8.1.5.0.0).
>
> Here is the actual query:
>
> SELECT SPOT_VIEW.ratio_1,
> SPOT_VIEW.intensity_1_exp,
> SPOT_VIEW.intensity_1_ref,
> SPOT_VIEW.quality,
> SPOT_VIEW.gene_name,
> SPOT_VIEW.accession,
> EXPERIMENT_VIEW.x_treatment,
> SEQ_CLUSTER_VIEW.name,
> SEQUENCE_VIEW.database_location
> FROM EXPERIMENT_VIEW,
> SPOT_VIEW,
> SEQUENCE_VIEW,
> SEQ_CLUSTER_VIEW
> WHERE SPOT_VIEW.experiment_id in (426,427,428,64,65) AND
> SPOT_VIEW.ratio_1 >= 5 AND
> SPOT_VIEW.quality >= 90 AND
> SPOT_VIEW.database_id NOT IN (3) AND
> SEQ_CLUSTER_VIEW.cluster_id(+) = SEQUENCE_VIEW.cluster_id AND
> SPOT_VIEW.database_id = SEQUENCE_VIEW.database_id AND
> SPOT_VIEW.accession = SEQUENCE_VIEW.accession AND
> SPOT_VIEW.experiment_id = EXPERIMENT_VIEW.id
>
> The outer join is the fourth line from the end:
> SEQ_CLUSTER_VIEW.cluster_id(+) = SEQUENCE_VIEW.cluster_id
> if I change that to
> SEQ_CLUSTER_VIEW.cluster_id = SEQUENCE_VIEW.cluster_id
> it works fine, but, obviously, doesn't give me the results that I need.
>
>
Received on Sat Apr 28 2001 - 18:06:25 CDT