(Oracle Performance) Will a query based on a view limit the view using the where clause?
        Posted  
        
            by BestPractices
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by BestPractices
        
        
        
        Published on 2010-03-22T19:03:27Z
        Indexed on 
            2010/03/22
            19:11 UTC
        
        
        Read the original article
        Hit count: 487
        
Oracle
In Oracle (10g), when I use a View (not Materialized View), does Oracle take into account the where clause when it executes the view?
Let's say I have:
MY_VIEW =
SELECT * 
FROM PERSON P, ORDERS O
WHERE P.P_ID = O.P_ID
And I then execute the following:
SELECT * 
FROM MY_VIEW
WHERE MY_VIEW.P_ID = '1234'
When this executes, does oracle first execute the query for the view and THEN filter it based on my where clause (where MY_VIEW.P_ID = '1234') or does it do this filtering as part of the execution of the view? If it does not do the latter, and P_ID had an index, would I also lose out on the indexing capability since Oracle would be executing my query against the view which doesn't have the index rather than the base table which has the index?
© Stack Overflow or respective owner