Re: same query, different parsing users, different plans ...

From: joel garry <joel-garry_at_home.com>
Date: Thu, 5 Jun 2008 11:21:14 -0700 (PDT)
Message-ID: <c17d016f-28b1-4611-bf15-badefa1208f2@j33g2000pri.googlegroups.com>


On Jun 5, 6:36 am, "stephen O'D" <stephen.odonn..._at_gmail.com> wrote:
> Oracle 10.2.0.2 on HPUX.
>
> I have a fairly simple query that queries from a table T1 and a view
> V2 (which is just a straight select * from T2).  All objects are owned
> by the same user.
>
> If I explain/run my query as the same user that owns the tables and
> view, it picks a 'good' plan.
>
> If I parse/explain it as another user that has select on all three
> views and tables and it consistently picks a different 'bad' plan.
>
> As a further wrinkle, if I change the query to exclude the view and
> use the underlying table directly (no other change to the query except
> the view name) it always picks the correct plan.
>
> Can anyone offer an explanation for this sort of behaviour?

Search with the following in tahiti.oracle.com: Use of Indexes Against Views
using hints with views

jg

--
@home.com is bogus.
You can't fight the borg.  http://www.signonsandiego.com/uniontrib/20080605/news_1b5mpeg.html
Received on Thu Jun 05 2008 - 13:21:14 CDT

Original text of this message