RE: Varied block density for fixed length row tables

> The vsize of a number is related how many significant digits it contains. So
> if some rows have number columns with the value 0 and some have a lot of
> digits they will vary in physical length. Numbers that are even 10, 1000,
> 100000, etc. are 1 byte smaller, for example than other integers with the
> same number of digits (except for even 100, 10000, 1000000, etc. which are
> two bytes smaller).

Well I'll be. I just tested that by creating a table with a single NUMBER(6) and comparing table stats with values of "10" and "999999" -- 3 bytes vs. 5, respectively. So my rows are indeed variable length after all!

Of course, *now* I managed to find the storage formula for NUMBER ("Internal Numeric Format" heading):

http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/datatype.htm#i16209

> Although that all begs the question of how you're executing this copy. I
> would suspect in the race between dropping prices in cpus and storage you'd
> still win staying a little bigger and using some physical copy strategy
> (redo application family including roll it yourself and dataguard, or
> storage vendor based duplication) rather than reloading. My expectation of

However, those answers seem to carry capital expenditures with them. Mine is a one-shot of DBA time, requires minimal extra/intermediate storage, and is customized to work around Oracle bugs (like Data Pump always generating huge amounts of redo when using a DB link in v10.1). Also, I may have overlooked options on the DIY physical copy, but the nature of our DB setup requires some physical differences between the two schemas, as per our use of the ERP software (JDEdwards).

> Likewise, I'm wondering for what you use the copy. Changing density and
> ordering might well have performance implications.

Granted. This copy of the Production schemas is a CYA move to placate a small but very important group of users. There are other factors between the two DBs that cause a much larger gap in performance than block density (e.g. no buffer cache contention when compared to Production), so I'm not terribly worried about it.

It looks like the values in the NUMBER datatype are my culprit. Now I know, and knowing is half the battle...

Thanks Mark!!!

Rich

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