Re: View vs Underlying Query Performance Question

DA Morgan wrote:
> Soup wrote:
> > I have a query that runs fast (second or less). I put this query into a
> > view minus one condition (userid = '<the users userid>'). When I query
> > the view and add the condition, the query (see 1 below) is slow
> > (minutes). I tried running the view without the condition (see 2 below)
> > and found that it ran in about the same amount of time and the explain
> > plans were the same. The only difference was the results.
> >
> > 1) SELECT * FROM myview WHERE userid = '<the users userid>'
> > 2) SELECT * FROM myview
> >
> > Is this normal behavior? Doesn't Oracle include my condition before
> > running the view's query? I assumed that since a view is a stored query
> > that it would include my condition before processing the request. That
> > isn't what is happening here. Also, I have used this approach before
> > and haven't had an issue.
> >
> > How should Oracle handle this situation? Will it always run the stored
> > query first before narrowing the results by the conditions added
> > outside of the view? If it should be including my condition what could
> > prevent it from happening?
> >
> > Thanks!
>
> Oracle version number?
> It would have been very helpful if you had included the EXPLAIN PLANs
> created with DBMS_XPLAN.
> Is USERID a string or a number?
> --

What I really would like to see is the underlying _query_ ... well the Oracle version number is always nice also ;)

cheers,
Martin Received on Fri Sep 08 2006 - 09:10:04 CDT