would too many views cause issues on an oracle database

Posted on 2014-02-11
Medium Priority
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 488 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 492 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.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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 38

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

741 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