Re: Bad optimizer choice for a view/outer join query

"Julien Viala" <i_at_hate.spam> wrote in message news:3EC36541.4050603_at_hate.spam...
> Could you help me with a performance problem due to a bad optimizer
> execution plan.
>
> Consider two Oracle users : admin and user.
> 'admin' owns and has direct access on all tables. 'user' accesses tables
> through views with a condition (restraining access) of the form : 'where
> domain = 0 or domain = 1'
>
> My query is :
>
> select a.*, b.* from a, b where a.id = XXX and b.id (+) = a.other_filed;
>
> When launched with 'user', I get a full access on b (or full access on
> b's index if I try a hint) whereas, when launched with 'admin' the index
> on b is used properly _even_ if I add the view condition to the query.
>
> Notice that due to condition on a.id, the query may not return more than
> one row.
>
> Last word : I am working on Oracle V8.1.7 and checked that view merging
> is active.
>
> Any idea ?
>
> Thanks in advance,
>
> Julien Viala
>

>
> The outer join is important as everything's perfect without it.

This phrase doesn't make sense: you basically state you don't need it.

By virtue of the outer join, you will invariable get a full table scan on at least one table (you are searching for *missing* data, and *missing* data can't be found in an index)

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address