OraFAQ Forum: Performance Tuning » Limitation for view

Home » RDBMS Server » Performance Tuning » Limitation for view

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Limitation for view [message #242383]

Fri, 01 June 2007 14:52

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.

Report message to a moderator

Re: Limitation for view [message #242389 is a reply to message #242383]

Fri, 01 June 2007 15:15

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Limitation for view [message #242395 is a reply to message #242389]

Fri, 01 June 2007 15:33

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.

Report message to a moderator

Re: Limitation for view [message #242398 is a reply to message #242383]

Fri, 01 June 2007 15:46

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Limitation for view [message #242402 is a reply to message #242398]

Fri, 01 June 2007 16:14

vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY

Senior Member

Thank u sir i will try to change the full view structure.

Report message to a moderator

Re: Limitation for view [message #242403 is a reply to message #242383]

Fri, 01 June 2007 16:20

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Limitation for view [message #242466 is a reply to message #242403]

Sat, 02 June 2007 07:06

rleishman
Messages: 3728
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

Report message to a moderator

Re: Limitation for view [message #242727 is a reply to message #242383]

Mon, 04 June 2007 21:21

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.

Report message to a moderator

Previous Topic:	about patch
Next Topic:	dbms_stats.gather_schema_stats causes ORA-01555

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 18:50:15 CDT 2024