[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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 23

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

649 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