Re: Views, outer joins, and performance

Dag

I had a similar problem on RDBMS 7.3.3.3. It used to take hours rather than 10 minutes.

I dont think it is the outer join.

It is joining ANALYZED tables and views and using CBO(cost based optimizer).

CBO is the default optimizer mode even if 1 joined table has analyzed statistics. RBO is the default for non-ANAYZED table joins.

What I ended up doing was to use the following command to delete the statistics on all the analyzed tables that were joined and using RBO(rule based optimizer).

analyze table_name delete statistics;

What I remember was, that if even one joined table has analyzed statistics, then all tables get analyzed at run time for the SELECT command and this could take quite a while for large tables and thats what was my problem. Also, if even one table is analyzed then CBO is used.

I would stick with RBO for the time being.

Let me know if you find anything else.

Hope this helps.

Oracleguru
www.oraclguru.net
oracleguru_at_mailcity.com

Dag Arne Matre <dam_at_visma.no> wrote in article <01be5026$1cd764d0$4101030a_at_sandeid>...
> We experience bad performance on outer joins to views.
>
> Consider the following scenario:
>
> create table t (tid, ...., primary key (tid))
> create table u (uid, tid fk (t.tid) not null, ...., pk (uid))
> create view v as select uid, .... from t, u where t.tid = u.tid
> create table w (wid, uid fk(u.uid) null, c, .... , pk (wid))
>
> This select takes "forever":
> select ... from w, v where w.uid = v.uid(+) and w.c = '123'
> Removing the outer join takes "no time", but does not give the wanted
> result set.
> Accessing the tables takes no time:
> select ... from w, t, u where w.uid = u.uid(+) and u.tid = t.tid(+)
> All columns in the where clauses are indexed
>
> "forever": 30-50 seconds
> "no time": fractions of a second (0.05 - 0.5)
> According to explain plan the problem is table scan on u.
>
> Could someone share your thoughts on this area? Experienced the same
> problems? Is outer joins to views in general a bad idea?
>
> Have played with alter session set optimizer_mode, analyze table t,u,w,
..
> Oracle 8.0.4 on NT.
>
>
> TIA,
> D A
>
Received on Thu Feb 04 1999 - 09:12:18 CST