2

Often times at work we opt to create views in the database to expose the data that we want to work with instead of building some monster query in our code. Being somewhat new to this field my assumptions are that the view is just another query that is added onto whatever query I pass at run-time.

Sometimes we are returning hundreds of thousands of results, even millions sometimes and this can take some times. So, my question is does a view affect the query time and/or performance of the query?

1

According to this discussion from 2007 in an Oracle forum, views were not cached at that time. From this description, it is clear that Oracle provides query result caching, but that will work independently from queries through a view, or selects without a view. If you use the same view in different queries, I would expect that the result caching might help a little bit, since the cache results from one query might be reused by a different query based on the same view, but this will probably depend on the query and the Oracle version.

However, Oracle supports the concept of materialized views, which can speed up queries, and also queries base on those kind of views. So the correct answer is:

  • it depends on the Oracle version, the database configuration and the particular query.

The best thing you can do is actually measure it for your specific situation.

3
  • Views are not cached in any version of Oracle. Query result caching is a feature that is available but that doesn't depend on whether code is in a view-- a SQL statement coming from an application could just as easily take advantage of query result caching. But that's a niche feature that the vast majority of applications won't be able to meet. Jun 1 '15 at 22:05
  • @JustinCave: see my edit.
    – Doc Brown
    Jun 2 '15 at 5:42
  • This helps me understand how to speed up my query with materialized views, got my query from 1m35s to 55s.
    – DylanStout
    Jun 3 '15 at 16:00
1

In general, a view's performance is equal to that of the defining query's performance. Usually the query planner can do all its tricks across views' boundaries if views are joined with other datasets.

It's always a good idea to run EXPLAIN PLAN against your most used statements, and see what might be a bottleneck.

Views that perform costly processing can be made materialized so that the computed values are stored and auto-updated. This makes sense when the underlying tables change not as often as the view is queried.

1

At a conceptual level, yes, a view is just a stored query that the optimizer will combine with whatever additional joins and predicates you add in your code to produce an optimal query plan. In theory, using a view should be no different than replicating the "monster query" over and over in your code. And to the extent that your DBA team may be able to do something to improve the performance of the query, having it in a view may make it easier for them to transparently improve the performance of all your queries.

Now, when you get down into the weeds, it is entirely possible that using a view would improve the performance of some queries and degrade the performance of other queries when compared to replicating the monster query because it causes the optimizer to pick a different query plan. Since the optimizer is considering 10's of thousands of query plans and making lots of decisions about which branches to follow and which to abandon, it's hard to know in advance which queries would benefit from a view and which might be harmed. Heck, it's often hard to know why a particular query was improved or harmed by putting some logic in a view unless you enjoy going deeply into 10053 trace files. For most developers writing most queries, it's reasonably safe to assume that the presence of a view isn't going to have a meaningful impact on performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.