Re: partitionning join table and normalization

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 Received on Tue Apr 08 2008 - 11:08:28 CDT