RE: Database Design Best Practice help
Date: Thu, 31 Jan 2013 20:36:00 +0000
Message-ID: <0D8F4CAC0F9D3C4AACC63F50FD9957F72646F245_at_PRDTXWPEMLMB32.prod-am.ameritrade.com>
Not directly related, but if you stick with the small tables I generally like to create those small lookup tables as iots. Any block read you can get rid of is a good block read, esp. if the table is frequently accessed.
Jay Miller
Sr. Oracle Database Administrator
201.369.8355
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Tuesday, January 29, 2013 5:43 PM
To: oracle-l_at_freelists.org
Subject: Re: Database Design Best Practice help
One additional thought about consolidating logical entities into a single physical entity -- perhaps the problem becomes more clear if you take the idea all the way to absurdium?
Here is an account of a time when that happened (http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/) along with supplementary commentary on TheDailyWTF (http://forums.thedailywtf.com/forums/p/4166/96314.aspx#96314).
On 1/29/2013 2:38 AM, Sayan Sergeevich Malakshinov wrote:
> Toon Koppelaars wrote,on my timestamp of 28/01/2013 8:13 PM:
>> Just create a union-all view on top of those hundred tables, and write a
>> couple of instead-of triggers to deal with inserts/updates/deletes. Then
>> have your "controller" based on top of this view.
> Unfortunately the views like that increases the chance to get inefficient
> plan, especially when client dynamically build queries with many joins of
> views with big number of tables, even if we increase
> "_optimizer_max_permutations"
>
> Best regards,
> Sayan Malakshinov
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 31 2013 - 21:36:00 CET