Re: Design Question
- From: Jared Still <jkstill@xxxxxxxxx>
- To: "Blanchard, William" <wblanchard@xxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 19 Oct 2009 10:09:01 -0700
That would seem to be the only logical conclusion.
:)
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:39 AM, Blanchard, William <
wblanchard@xxxxxxxxxxxxxxxxxxxx> wrote:
> Does the fact that I don't think this makes you a geek make me a geek?
> ;-)
>
>
> WGB
>
> ------------------------------
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Robert Freeman
> *Sent:* Monday, October 19, 2009 11:34 AM
> *To:* Jared Still
> *Cc:* Muru.Balakrishnan@xxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: Design Question
>
> 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:
