Varied block density for fixed length row tables

From: Rich Jesse <rjoralist2_at_society.servebeer.com>
Date: Wed, 14 Sep 2011 11:56:59 -0500 (CDT)
Message-ID: <cb9dd2e0d6b355d4e141f8692e9cb034.squirrel_at_society.servebeer.com>

Hey all,

I've got a schema in a 10.1 DB that gets a full copy of our 10.1 prod ERP schema on a monthly basis. To help reduce storage, I've dropped the PCTFREE on the largest tables to increase the density of rows per block. In reviewing how well this is working, I calculate the rows per block using:

SELECT
        rowcount,count(*)
FROM
(

	SELECT /*+ parallel (mytab 4) nocache (mytab) */
		count(*) ROWCOUNT
	FROM myschema.mytable MYTAB
	GROUP BY SUBSTR(ROWIDTOCHAR(ROWID),8,8)

)
GROUP BY rowcount
ORDER BY 1; On a few tables, the above query shows that lots (30%+) of blocks have 1 less row than the rest. This translates to Gigs of wasted space as far as I'm concerned.

These tables only have NCHAR and NUMBER datatypes, and do not have NULLs, so the rowsize should be static. The rows were all loaded with a single direct load INSERT pulling data across a DB link, and after a TRUNCATE DROP STORAGE. This particular table would have pulled the remote table using a parallel query, DOP of 2. The NLS NCHAR set is AL16UTF16. Blocksize 8K. Row length 843 bytes (from table stats).

I've rechecked my ROWIDTOCHAR calculation to group by block number, and it seems correct, but I could be misinterpreting the rowid format.

Thoughts?

Rich

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

Received on Wed Sep 14 2011 - 11:56:59 CDT