Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: very slow outer join
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