Home » RDBMS Server » Performance Tuning » Limitation for view
Limitation for view [message #242383] Fri, 01 June 2007 14:52 Go to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
Hello sir


I have one question there is any limitation for views ,because
we have created a view from different 20 tables and also view have more than 120 columns from those 20 tables


for eg: if we have tried to run the following query it would be freezed

>select count(*) from <view_name(from 20 tables)> ;


plz give me good suggestion to overcome this type of problems.

-advanced Thanx.
Re: Limitation for view [message #242389 is a reply to message #242383] Fri, 01 June 2007 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>we have created a view from different 20 tables
I strongly suspect this application was NOT designed to Third Normal Form.
Having to join 20 tables to answer 1 single business question seems outlandish on the surface.
Re: Limitation for view [message #242395 is a reply to message #242389] Fri, 01 June 2007 15:33 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
Thanx for ur reply sir



Actually this view normally used for report generation purpose
we need this 120 colums information thats why we have desidned like this ,there is any other way to fast up the view output.
Re: Limitation for view [message #242398 is a reply to message #242383] Fri, 01 June 2007 15:46 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>we need this 120 colums information thats why we have desidned like this ,there
I have no problem with 120 column, but that averages out to about 6 columns per table.
>is any other way to fast up the view output.
Reduce the number of tables being joined by the view.

I still have a hard time accepting the implicit premise that TWENTY different data relationships actually exist that require 20 different tables to be used & joined.
Re: Limitation for view [message #242402 is a reply to message #242398] Fri, 01 June 2007 16:14 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
Thank u sir i will try to change the full view structure.
Re: Limitation for view [message #242403 is a reply to message #242383] Fri, 01 June 2007 16:20 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Hopefully you or your "data architect" can (re)design collection of tables so they all are in Third Normal Form.
I suspect that this will reduce the number tables involved.
Re: Limitation for view [message #242466 is a reply to message #242403] Sat, 02 June 2007 07:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is nothing wrong with using views per-se, joins of views can present problems though - especially when they are complex views (see the doco for a definition of complex views).

Oracle has trouble with joins of 5 or more tables. The more tables, the more likely you are to have trouble. See here for more.

Ross Leishman
Re: Limitation for view [message #242727 is a reply to message #242383] Mon, 04 June 2007 21:21 Go to previous message
artmt
Messages: 32
Registered: October 2006
Location: Boston
Member
If some of the queries against your view need to query base tables A,B,C,D while other queries are only intersted in tables AEDF, then consider table functions as alternative to views.
Previous Topic: about patch
Next Topic: dbms_stats.gather_schema_stats causes ORA-01555
Goto Forum:
  


Current Time: Tue Dec 06 12:31:23 CST 2016

Total time taken to generate the page: 0.08779 seconds