CODEWITHSUNDEEP
oracle-databaseview
Punter Vicky
Punter Vicky

Reputation: 16992

Oracle View vs joining tables in Oracle

I understand that a view is a window to an underlying table or set of tables in Oracle. For instance if I have a view that is created by joining multiple tables , will the view perform the actual join operations when I select data from the view? Does a view perform better than joining multiple tables to fetch data or is it the same with respect to performance?

Upvotes: 1

Views: 1872

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

There is usually no performance difference between a single query and a logically equivalent query that uses views.

Oracle has optimizer transformations that can combine views with the outer query; predicate pushing, simple and complex view merging, etc. Think of views more like a text macro that builds a large query, instead of a function that returns rows.

For example, in the below query Oracle would probably be smart enough to push the predicate on the primary key column into the view. Although the view by itself might return millions of rows, when the entire query is run Oracle will apply the predicate on the primary key column first.

select *
from view_returns_millions_of_rows
where primary_key_value = 1;

Upvotes: 1

Related Questions