RE: Varied block density for fixed length row tables
- From: "Rich Jesse" <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 14 Sep 2011 14:56:46 -0500 (CDT)
Hey Mark!
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
