DB View vs Select query?

Question

After reading other similar questions on SO/Google, I get DB views(Virtual/Simple table views not materialized views) are primarily used for convenience and security, not for speed improvements. Some people says views are reusable and at central place but that can be kept at central place also in code

Per mine understanding Views should have been marginally better than query when coming from web server. Reason is when query is executed through code in Web server, the query text needs to travel on network which is not the case in views. I believe both query(prepared statement) and Views are pre-compiled ? . So same in that sense. Is my understanding correct here ?

This resource says opposite

Performance – What may seem like a simple query against a view could turn out to be a hugely complex job for the database engine. That is because each time a view is referenced, the query used to define it, is rerun.

But this is true for query also. Is not it ?

This question is for simple views not materialized/indexed view

Reference resources are Is a view faster than a simple query?

Query vs. View

Answer 1

(Speaking about Oracle here, because that's my area of knowledge)

Views are not "pre-compiled" as such. They are simply stored text, and thus conceptually, when you run

select * from my_view

it is logically equivalent to

select * from ( [query that defines view] )

With regard to

"Reason is when query is executed through code in Web server, the query text needs to travel on network which is not the case in views"

this is true, but it is pretty rare for your web server and your database to be separated by such a bad network that a 100 byte query against a view versus a 500 byte against the base tables would be noticeable.

And finally, with respect to performance, it depends on the view. When a view is referenced in a query, there are two mechanisms that might come into play.

One is "view merging" where we can integrate the text of the view into the query as if the view never existed, eg

view:  select * from t
query: select * from my_view where x=1

could be merged into:

select * from t where x=1

and thus the text of the view is never actually executed.

But another option is "view resolution" where the view is sufficiently complex or contains definitions that would prohibit it being merged. For example, a view containing a window function:

view:  select t.*, row_number() over ( order by blah ) from t
query: select * from my_view where x=1

cannot be merged into:

select t.*, row_number() over ( order by blah ) from t
where x=1

because the window function would no longer return the same result. (One returns the ranking across the entire table, the other returns the ranking only for rows where x=1).

So in the case of view resolution, you could see performance implications, but simply because we need to guarantee correctness of results, not through a limitation of views in general.