Bad optimizer choice for a view/outer join query

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.

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

Last word : I am working on Oracle V8.1.7 and checked that view merging is active.

Any idea ?

Thanks in advance,

Julien Viala Received on Thu May 15 2003 - 05:00:33 CDT