/* ************************************************ */
/*                                                  */
/* fnctn:   Average records per block               */
/*                                                  */
/* name:    @c:\oracle\sql\recsblks.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* Query can be used to calculate how much space a  */
/*    table will ultimately require.                */
/* ************************************************ */

/* *********************** */
/* How many rows per block */
/* *********************** */

SELECT 	SUBSTR(T.rowid,1,8)||'-'||substr(T.rowid,15,4) BLOCK,
	count(*) ROW_CNT
from &tab_name T
where rownum < 2000
group by SUBSTR(T.rowid,1,8)||'-'||SUBSTR(T.rowid,15,4);

/* *********************** */
/* What is the average size */
/* *********************** */

SELECT 	avg(count(*)) "Average rows/block"
from &tab_name T
where rownum < 2000
group by SUBSTR(T.rowid,1,8)||'-'||SUBSTR(T.rowid,15,4);

/* ********************************** */
/* How much space for the whole table */
/* ********************************** */

SELECT (&NUMBER_OF_ROWS/avg(count(*))) * &BLOCK_SIZE "Total Space Required"
from &tab_name T
where rownum < 2000
group by SUBSTR(T.rowid,1,8)||'-'||SUBSTR(T.rowid,15,4);

commit;

