Re: Design Question
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Mon, 19 Oct 2009 09:33:38 -0700 (PDT)
As a Vulcan once said, "There are always alternatives."
My kids are right.... I AM a geek.... (Holds head in his hands).
RF
Robert G. Freeman
Oracle ACE
Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide
(Sybex)
Oracle Database 11g New Features (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Other various titles
Blog: http://robertgfreeman.blogspot.com
________________________________
From: Jared Still <jkstill@xxxxxxxxx>
To: Robert Freeman <robertgfreeman@xxxxxxxxx>
Cc: Muru.Balakrishnan@xxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Mon, October 19, 2009 10:27:45 AM
Subject: Re: Design Question
To be quite honest, I have never liked the idea for all
the reasons you mention, and I have never used it.
It is just an alternative. Testing may show that it is not
worth the hassle. Too, the advice was given when RBO
was the only viable choice for an optimizer.
The CBO is considerably smarter about how to join
tables, so stuffing everything into a single code table
may now be of little or no value.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
On Mon, Oct 19, 2009 at 9:13 AM, Robert Freeman <robertgfreeman@xxxxxxxxx>
wrote:
>
>Personally, I never liked the single table approach for codesets/lookup data.
>I've started down that path a few times and always decided that it was more
>hassle and risk than individual codeset tables. I think it's a bit of a
>holdover from non-relational days and involves several negatives.
>
>Negatives include lack of the ability to define FK's (sure, you can design
>triggers, but what a hassle), lack of clarity with respect to the nature of
>the data in the table (I know what person_type_code stores, I have no idea
>what a table called codesets really stores), hierarchial issues (the design
>itself requires a multi-tier hierarchy of data. IE: code_set_type,
>code_set_identifier, code_set_value) which complicates the SQL generated.
>
>It might work for a small design with only a few lookups, but for a
> large design I think it's just asking for trouble.
>
>My opinion, YMMV.... :-)
>
>
>RF
>
>
> Robert G. Freeman
>Oracle ACE
>Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
>>
>Author:
>Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
>OCP: Oracle Database 11g Administrator Certified Professional Study Guide
>(Sybex)
>Oracle Database 11g New Features (Oracle Press)
>>
>Oracle Database 10g New Features (Oracle Press)
>Other various titles
>Blog: http://robertgfreeman.blogspot.com
>
>
>>
>
>
>
________________________________
From: Jared Still <jkstill@xxxxxxxxx>
>To: robertgfreeman@xxxxxxxxx
>Cc: Muru.Balakrishnan@xxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
>Sent: Fri, October 16, 2009 3:41:12 PM
>Subject: Re: Design Question
>
>
>
>On Fri, Oct 16, 2009 at 2:25 PM, Robert Freeman <robertgfreeman@xxxxxxxxx>
>wrote:
>
>>>
>>
>>
>>...
>>One thing I've found is that the *more* codesets you have, the more complex
>>the queries can get. The more complex a query gets, the more difficult it
>>becomes to tune. I had a model a couple of years ago with some very complex
>>security rules. Each rule really translated into it's own codeset table.
>>Also, because of the complexity of the security rules, SQL had to be
>>dynamically generated based on various contexts (what a mess!!). It involved
>>lots of sub-queries, etc. Performance was a bear for a number of reasons.
>>
>>
>
>
>>
>Another approach to that was one advocated by Gurry and Corrigan in
>Oracle Performance Tuning (O'Reilly 1996) is to combine multiple lookup
>tables into a single table.
>
>
>IIRC another column was added to indicate what the codes were used for.
>
>
>That approach may be useful, and as usual, YMMV and it requires testing.
>
>
>Jared Still
>Certifiable Oracle DBA and Part Time Perl Evangelist
>Oracle Blog: http://jkstill.blogspot.com
>>
>Home Page: http://jaredstill.com
>
>
>
>
>
Other related posts:
