Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: very slow outer join

Re: very slow outer join

From: Chris Wiss <chris_at_purecarbonnospam.com>
Date: Sun, 29 Apr 2001 02:54:15 -0400
Message-ID: <9chk9i$13ck$1@msunews.cl.msu.edu>

Make sure you have analyzed your schema before using explain plan. Often this fixes the problem.

Chris Weiss
Chief Scientist
PureCarbon, Inc.

"Artem Duvanov" <artem_duvanov_at_mail.ru> wrote in message news:9cfiev$r37$1_at_mail.comset.net...
> 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 Sun Apr 29 2001 - 01:54:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US