view execution

[mesuj]

All
When i say select * from view , will the select query used to frame view be executed.Then why view is faster than the actual qry?.
Can a view be created on a transaction table?.
Is there any concept called rebuilding the view?.
Thanks
mesuj

 

[jee]

here is a clip from the Oracle 8i concepts manual on views:

"Oracle stores a view's definition in the data dictionary as the text of the query that defines the view. When you reference a view in a SQL statement, Oracle:

Merges the statement that references the view with the query that defines the view

Parses the merged statement in a shared SQL area

Executes the statement

Oracle parses a statement that references a view in a new shared SQL area only if no existing shared SQL area contains a similar statement. Therefore, you obtain the benefit of reduced memory usage associated with shared SQL when you use views."


1)I am not expert, but I would guess that the speed of views over their equivalent SQL has to do with parsing/caching. Since every time you execute a view the SQL is exactly the same, if it is in cache it doesn't have to be reparsed and if the data is there, it doesn't have to be looked up again. If you submit the equivalent query, but its text is different enough from that stored with the view, it has to be parsed.

2) Again, I am guessing, but I don't think a transaction table is a real database table, but just a data structure maintained somewhere in memory by Oracle. If this is true, then of course you cannot build a view on a transaction table.

3)Any time you execute the CREATE OR REPLACE VIEW.... statement, the named view is "rebuilt".

 

[mesuj]

thanks jee.that was a very useful info.