Views, outer joins, and performance

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 - 04:07:23 CST