Re: Large tables, updates, selects and varchars

On Apr 3, 10:22 am, mathewbutler <mathewbut..._at_yahoo.com> wrote:

> > We are partitioning with between 35 million and 60 million rows per
> > partition. This probably will not be the last change we are going to
> > make. There are likely to be future modifications.
>
> > It's going to store file paths and most queries will need to access
> > it. We chose VARCHAR2 because the size of the data will never exceed
> > 4000 chars, and we assumed that CLOBs were less efficient than
> > VARCHAR2s. Is this not the case?- Hide quoted text -
>
> > - Show quoted text -
>
> This doesn't answer your question, but if your O/S is POSIX compliant
> then file paths will be defined much shorter than what you have
> defined ( 255 characters from dim and distant memory ).
>
> I think you will need to consider;
>
> - your transaction mix ( the answer to your question will depend on
> the balance of inserts/updates/deletes and selects )

We will mostly do selects (90%) and inserts (10%). Updates will only happen when we add a new column. Deletes will almost never happen.

> - your database block size ( how many rows will fit onto a block, will
> you have significant row migration with the new column, and where this
> occurs will the additional work that Oracle needs to do be less that
> the alternative join )

This is the core issue I think. With the information I've given above, what will be the best solution? A separate table for each new column, or simply adding a column to the existing table and do an update.

> - space implications of any additional storage required, depending on
> the modelling approach

> - whether the approach adds or takes anything away from your systems
> simplicity and code maintainability, and if your proposed approach
> takes something away measure this against the gains.

Adding a column has the least impact on application design.

>
> It's not clear what you mean by update. ie: Whether you are concerned
> about the initial population of the new column for table entries, or
> the ongoing management of values in this column. In any case, remember
> that if you are modelling seperately you will also be storing
> 1+billion PK entries to enable you to find the new column.
>
> Hope this is some help.

The update is for the initial population. Received on Thu Apr 03 2008 - 12:32:03 CDT