tags:

views:

57

answers:

2

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?

+5  A: 

It will not execute the query first. If you have a index on P_ID, it will be used.

Execution plan is the same as if you would merge both view-code and WHERE-clause into a single select statement.

You can try this for yourself:

EXPLAIN PLAN FOR
SELECT * 
FROM MY_VIEW
WHERE MY_VIEW.P_ID = '1234'

followed by

SELECT * FROM TABLE( dbms_xplan.display );

---------------------------------------------------------------------------------
|Id | Operation                    | Name   |Rows| Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |        |  1 |    52 |     2   (0)| 00:00:01|
| 1 |  NESTED LOOPS                |        |  1 |    52 |     2   (0)| 00:00:01|
| 2 |   TABLE ACCESS BY INDEX ROWID| PERSON |  1 |    26 |     2   (0)| 00:00:01|
| 3 |    INDEX UNIQUE SCAN         | PK_P   |  1 |       |     1   (0)| 00:00:01|
| 4 |   TABLE ACCESS BY INDEX ROWID| ORDERS |  1 |    26 |     0   (0)| 00:00:01|
| 5 |    INDEX RANGE SCAN          | IDX_O  |  1 |       |     0   (0)| 00:00:01|
---------------------------------------------------------------------------------
Peter Lang
Which you could easily prove to yourself by looking at the explain plan, or turning on autotrace in SQLplus.
MJB
+1, Explain Plan is your friend.
DCookie
A: 

WOW!! This is interesting.. I have two different explain plan depends on different data volumn & query inside logical view(This is my assumption)

  1. The original question case : It is definitely doing filtering first. I have small number of data(<10 in total) in this test table.

  ` 
|   0 | SELECT STATEMENT             |           |     2 |   132 |     2   (0)|
00:00:01 |
|   1 |  NESTED LOOPS                |           |     2 |   132 |     2   (0)|
00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PERSON    |     1 |    40 |     1   (0)|
00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PERSON_PK |     1 |       |     0   (0)|
00:00:01 |
|*  4 |   INDEX RANGE SCAN           | ORDERS_PK |     2 |    52 |     1   (0)|
00:00:01 |
    Predicate Information (identified by operation id)
   3 - access("P"."P_ID"=1)
   4 - access("O"."P_ID"=1)
    Note
  - dynamic sampling used for this statement
`
  1. However, when the data become larger(just several hundreads though, 300 ~ 400) and the view query become complex(using "connect by" etc..), the plan changed, I think...
|   0 | SELECT STATEMENT             |                   |     1 |    29 |     2
   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |     1 |    29 |     2
   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| RP_TRANSACTION    |     1 |    12 |     1
   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | RP_TRANSACTION_PK |     1 |       |     0
   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| RP_REQUEST        |   279 |  4743 |     1
   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | RP_REQUEST_PK     |     1 |       |     0
   (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("TRANSACTION_ID"=18516648)
   5 - access("REQ"."REQUEST_ID"="TRANS"."REQUEST_ID") 

---- Below is my original post

In my knowledge, the oracle first execute the view(logical view) using temporary space and then do the filter.. So your query is basically same as

SELECT *
FROM ( SELECT *
FROM PERSON P, ORDERS O WHERE P.P_ID = O.P_ID ) where P_ID='1234'

I don't think you can create index on logical view(Materialized view uses index)

Also, you should be aware, you would execute the query for MY_VIEW, everytime you using select * from MY_VIEW where P_ID = '1234'.

I mean every single time. Naturally, it is not a good idea for the performance matter

exiter2000
@exiter2000: Have you actually tried this?
Peter Lang
This is in conflict with the first answer above :(
BestPractices
Kind of...Not exactly same view structure(P_ID and everything) but with my own view in oracle 10g.
exiter2000
@exiter2000: I have edited my question to provide the execution plan showing that index is used. Could you provide your execution plan too?
Peter Lang