Re: partitionning join table and normalization
From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 08 Apr 2008 09:37:45 -0700
Message-ID: <1207672664.88357@bubbleator.drizzle.com>
>> chm..._at_gmail.com wrote:
>>> Hello,
>>> I'm working on a database which contains a big table TD (10 millions
>>> lines) with many columns (180)
>>> I wonder if the performance won t be better if i normalize some
>>> columns (about 20).
>>> So this is the new table NORM_FIELD :
>>> ID_DATA (ID reference from original table)
>>> FIELD_ID
>>> FIELD_DATA
>>> + 3 - 4 fields about updating (date, user, ...)
>>> TD is partitionning case 2 columns.
>>> Is it possible to partitionning NORM_FIELD which the same critery of
>>> TD ?
>>> Even ifnot, what the better solution between :
>>> - copy data of partitioning field in NORM_FIELD to store in the same
>>> partition (and have this critery for the join condition)
>>> - create a new partitioning critery for NORM_FIELD (like the last
>>> digit for having the best distribution)
>>> I don t want to change the application code, so i will rename my table
>>> and create a view with this name which will be a join with a another
>>> view build from this method :
>>> http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/d...
>>> Will performance be ok if i do this change ?
>>> What s about your experience ?
>>> Thanks for answers.
>>> Chmanu
>>> PS : it is possible i've not be clear cause my poor english, in this
>>> case ask me to explain.
>> Performance may or may not improve but maintainability and the
>> ability to use it for something other than a data-dumpster will.
>>
>> 10M rows is not a particularly large table. But 180 columns is,
>> in the vast majority of cases, a really bad idea.
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
>> 10M rows is not a particularly large table. But 180 columns is,
>> in the vast majority of cases, a really bad idea.
Date: Tue, 08 Apr 2008 09:37:45 -0700
Message-ID: <1207672664.88357@bubbleator.drizzle.com>
Barry Bulsara wrote:
> On Apr 8, 4:41 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> chm..._at_gmail.com wrote:
>>> Hello,
>>> I'm working on a database which contains a big table TD (10 millions
>>> lines) with many columns (180)
>>> I wonder if the performance won t be better if i normalize some
>>> columns (about 20).
>>> So this is the new table NORM_FIELD :
>>> ID_DATA (ID reference from original table)
>>> FIELD_ID
>>> FIELD_DATA
>>> + 3 - 4 fields about updating (date, user, ...)
>>> TD is partitionning case 2 columns.
>>> Is it possible to partitionning NORM_FIELD which the same critery of
>>> TD ?
>>> Even ifnot, what the better solution between :
>>> - copy data of partitioning field in NORM_FIELD to store in the same
>>> partition (and have this critery for the join condition)
>>> - create a new partitioning critery for NORM_FIELD (like the last
>>> digit for having the best distribution)
>>> I don t want to change the application code, so i will rename my table
>>> and create a view with this name which will be a join with a another
>>> view build from this method :
>>> http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/d...
>>> Will performance be ok if i do this change ?
>>> What s about your experience ?
>>> Thanks for answers.
>>> Chmanu
>>> PS : it is possible i've not be clear cause my poor english, in this
>>> case ask me to explain.
>> Performance may or may not improve but maintainability and the
>> ability to use it for something other than a data-dumpster will.
>>
>> 10M rows is not a particularly large table. But 180 columns is,
>> in the vast majority of cases, a really bad idea.
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -
> > > Daniel
>> 10M rows is not a particularly large table. But 180 columns is,
>> in the vast majority of cases, a really bad idea.
> > 180 columns smacks of bad design. > > In a really horrible design, 180 indexes on these 180 columns would > exist as well. > > To explore this argument further, if you did have this sort of bad > structure, would the Oracle CBO still choose the index that would give > the best execution plan to satisfy the query? Would it still choose > the index that would give the best execution plan for a table with > 1000 columns (the limit in 10.2)? > > I don't know the answer. Does anyone? (at some point, surely the cost > of determining the cost could be larger that the cost for executing > the query) > > Barry
While I agree with your conclusion ... "smacks of bad design" ... I am not at all sure I agree with how you got there. Why does 180 columns add up to 180 indexes? More than a handful on a table too "smacks of bad design."
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Tue Apr 08 2008 - 11:37:45 CDT