Solved

would too many views cause issues on an oracle database

Posted on 2014-02-11
8
382 Views
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?
0
Comment
Question by:Wokka68
[X]
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
8 Comments
 
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.
0
 
LVL 74

Accepted Solution

by:
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.
0
 
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.
0
Independent Software Vendors: 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!

 
LVL 23

Expert Comment

by:David
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.
0
 
LVL 74

Expert Comment

by:sdstuber
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.
0
 
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;
begin
  select  
    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;
end;

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 ?
0
 
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.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 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