very slow outer join

From: <gene01_at_smalltime.com>
Date: Fri, 27 Apr 2001 20:55:33 -0000
Message-ID: <tejn65p4rm4g73@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 Fri Apr 27 2001 - 15:55:33 CDT