would too many views cause issues on an oracle database

Posted on 2014-02-11
Last Modified: 2014-06-06
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?
Question by:Wokka68
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 122 total points
ID: 39851183
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.
LVL 74

Accepted Solution

sdstuber earned 123 total points
ID: 39851217
>>> 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.
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39851238
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.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

LVL 23

Expert Comment

ID: 39851240
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.
LVL 74

Expert Comment

ID: 39851262
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.
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39852533
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 ?
LVL 22

Expert Comment

by:Steve Wales
ID: 40116741
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the dā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question