would too many views cause issues on an oracle database

using an oracle database which have written a load of views to help with reporting.  been told by software company this could cause issues
i thought views only affected db when individually used. is there a limit to the number that should be written?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

sdstuberConnect With a Mentor Commented:
>>> i thought views only affected db when individually used

you are correct

There is a common, but mistaken, impression that views are somehow inherently slow.
So it's possible the vendor was trying to tell you to not "use" a lot of views.  Not necessarily that simply "having" a lot of views would be bad.  That's still not good advice without elaboration but that might be the intent behind the statement.

BUT....   It is definitely possible to overuse views and create a performance problem.  This is not an issue with views though; simply a matter of misuse in development.

For example.  If you have 2 views that produce similar output by querying the same tables but with slightly different where clauses that's fine.

If you then join those two views in order to compare/contrast values between related rows then there is the possibility that your query will be slower than necessary because you're accessing all of the same tables twice when it might be possible to read them only once.

In this example, neither view is wrong, neither view is slow.  Using them together is not wrong; but using them together might be slow.  This would be an "issue"; but not because you have too many views.  It's an issue because the views you had weren't used correctly.

This problem can creep in if you have views built on top of views with many layers.
Again - this is not, in itself, inherently bad or problematic.  In fact, it can be a great way to build up a suite of business logic in a consistent, reusable and meaningfully divided fashion.

But it's also possible to have lots of little views that do specialized lookups that when combined start double, triple, quadruple, etc hitting the same tables.  Or views doing summaries via aggregates - again, not inherently bad; but when you combine those views you might not be able to push predicates from outer queries through the aggregations to create efficient plans.
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I've heard of no such limit.

I would ask the folks in the software company the context behind that statement and for some reference material.
Mark GeerlingsDatabase AdministratorCommented:
I basically agree with slightwv's comment about "no such limit".  But, this is within reason.  If you have created thousands of views, that could create performance problems, since Oracle may have to check many of them when queries are submitted to see if one or more of them could be used to help with the current SQL statement.  Also, each view definition takes at least a bit of space in the data dictionary.

Not having enough views can also be a problem if you use only views for reporting, because then you may end up sometimes picking a view that gathers a lot more columns of information than you need for a particular report.

So basically, like with many questions involving Oracle, the actual answer is: it depends.  Usually though, using some views for reporting in Oracle is a good idea.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

DavidSenior Oracle Database AdministratorCommented:
Agreed.  Views, or even materialized views, are too simplistic an excuse from the vendor.  Revealing poorly analyzed business requirements, or poorly designed database architecture, would reflect "poorly" (sorry :) ) on the vendor's product.
If you have created thousands of views, that could create performance problems, since Oracle may have to check many of them when queries are submitted to see if one or more of them could be used to help with the current SQL statement.

A "view" won't be checked in this way.
A "materialized view" might be though, if it were created with REWRITE enabled for it.

They are completely different objects though.
Geert GOracle dbaCommented:
you can create millions of views without a problem
especially if they aren't being used
if it's necessary or pointless is another matter

a problem can only happen when views are used and
it the problem will only happen if the view was designed badly

the problem you'll have is if the view calls cause high resource consumption
and the resource consumption is higher than the server can handle

the worst thing with views is using function (or package calls) to read a single column from a single line of table and then nesting that view in other similar views

don't do this:
function getitem(column_id in varchar2, key in integer) returns varchar2 is
  data varchar2;
    case column_id
      when 1 then a
      when 2 then b
      when 3 then c
   etc ...
   end into data from table_x where prim_key = key;
  return data;

create view view_x as
select prim_key_y, prim_key_x, x, y, z,
  getitem(1, prim_key_x) a,
  getitem(2, prim_key_x) b,
  getitem(1, prim_key_x) c
  ... etc
from table_y

it works, no doubt about that
but if you suddenly have a dba who asks you why you are reading a table of 50 rows at a rate of 1000 times per second it's because everybody has adopted that idea or
you call a nested view every 10 seconds which causes that to happen

then the real nightmare starts of getting that out the system again
anybody have a decade of spare time on their hands to help ?
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.