RE: Advice needed on moving data with table changes

This is a huge topic.

I'll pitch in just one bit about one "e.g.":

Adding columns:

If the average resultant column length after population is a significant portion of the average free space per block in the existing table, you should consider the ramifications of row migration on your application performance.

If your current rows routinely fit in one block and the resultant row lengths with population of the new columns will routinely require multiple blocks, you should consider the ramification of row chaining on your application performance.

If your resultant row lengths will routinely require multiple blocks, then you may need to consider re-ordering all the columns in your table to maximize the fitting of the most popular columns into the first block of the row. If you need a tie breaker in that consideration, then columns that are referenced in queries that typically are fetched via an index that does not contain the column in question should be favored to fit in the first block. All this probably is only effective if the values exist at insert time or are at least populated in order by column. Out of line object references only count to the extent of the referencing value, etc., and your mileage may vary.

If you know the average column length of a column to be added that will be populated later with "real" values, and if that value's length has a reasonably low variability, and if there is a reasonable value of that average length that can be easily understood in all required contexts to be an artificial value, then you can minimize row migration when the row is populated if you default to an artificial value. Dates and fixed length codes are often candidates for this treatment, likewise foreign id reference integers to a listed value meaning that it is the artificial value. (By the way, when you have a reference table that contains the value meaning that the value has not yet been assigned or is unknown, and if you're trying to pre-claim row space to avoid row migration, then zero and one are bad choices for the id. Instead use an id such that the number takes up at least as much space as the high numbered ids. (Okay, if there are 100 or fewer choices in the reference list, then 1 and 0 are okay choices.) If your performance and size characteristics are such that you need to think about this at all, then it is likely the extra size now is worth the avoided row migration later. On the other hand, if you're only going to populate a small fraction of the values you might be better off saving the space.

If the new column's lack of a "real" value will be used to trigger post processing as time is available, then it is worth consideration that an artificial value (or values, such as negative integers possibly in the order you want post processing to occur for values that will be positive integers later) that can be indexed may be a better choice by which to drive the selection of records for post processing than null.

Whether or not each of these considerations is worth your time to consider is highly dependent on the precise actual situation, and I probably left some out. (see various references to over-engineering and compulsive tuning disorder [CTD].)

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of John Dunn
Sent: Monday, December 04, 2006 5:17 AM
To: 'oracle-l digest users'
Subject: Advise needed on moving data with table changes

We have a new version of an application which involves lots of changes to tables

e.g. columns added, columns deleted, columns renamed, columns moved to new tables

We needed to upgrade the live system and move the data to the new table formats.

What is the best approach here? What oracle utilities will handle this best?

John Dunn

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Dec 04 2006 - 08:45:51 CST