Re: View vs Underlying Query Performance Question

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?

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group