Ingres Q & A
/*
* table_size_info.rw
*
* DESCRIPTION:
* Report to view table size information. It selects all table/index info
* from the system catelogs. Then, based on the last time the structure was
* modified, it will calculate the amount of pages the table will take if
* remodified with the parameters used the last time (like MINPAGES &
* FILLFACTOR).
*
* CREATION:
* Mark Kale
*
* MODIFICATIONS:
* Init Date Comments
* ---- ---- --------
* mdk 092496 Added total size of db
* mdk 092496 Got rid of length and key_length columns, added Kb
Waste
*
*/
.name table_size_info
.shortremark DBA: Table Size & Re-Modification Information
.lm 0
.rm 80
.pl 66
.noformfeeds
.setup
set autocommit on; /* for interactive use */
set lockmode session where readlock = nolock;
.query
select
db = dbmsinfo ('database'),
t.table_name,
t.storage_structure,
t.is_compressed,
t.table_type,
t.num_rows,
t.row_width,
t.number_pages,
t.overflow_pages,
data_fill = float8 (float8(t.table_dfillpct) / 100),
leaf_fill = float8 (float8(t.table_lfillpct) / 100),
index_fill = float8 (float8(t.table_ifillpct) / 100),
minpages = t.table_minpages,
maxpages = t.table_maxpages,
c.column_name,
c.column_nulls,
c.column_datatype,
col_len = c.column_length
from
iitables t,
iicolumns c
where
t.table_owner = dbmsinfo ('dba') /* DBA tables */
and t.storage_structure != 'HEAP' /* have no keys */
and t.table_type in ('T', 'I') /* also get indexes */
and t.table_name = c.table_name
and t.table_owner = c.table_owner
and c.key_sequence > 0 /* only count keys */
union
select
db = dbmsinfo ('database'),
t.table_name,
t.storage_structure,
t.is_compressed,
t.table_type,
t.num_rows,
t.row_width,
t.number_pages,
t.overflow_pages,
data_fill = (t.table_dfillpct / 100),
leaf_fill = (t.table_lfillpct / 100),
index_fill = (t.table_ifillpct / 100),
minpages = t.table_minpages,
maxpages = t.table_maxpages,
column_name = '',
column_nulls = 'N',
column_datatype = '',
col_len = 0
from
iitables t
where
t.table_owner = dbmsinfo ('dba') /* DBA tables */
and t.storage_structure = 'HEAP'
and t.table_type = 'T'
.sort number_pages :desc, table_name, column_name
.position
table_name (0),
storage_structure (24),
number_pages (30),
overflow_pages (46),
num_rows (54)
.declare
key_len = integer, /* key length */
rows_per_page = integer, /* number of tuples per data page */
page_break = integer,
x = integer,
free = integer,
keys_per_lpage = integer, /* number of keys per leaf page */
keys_per_ipage = integer, /* number of keys per index page */
max_keys = integer,
leaf_pages = integer,
data_pages = integer,
remainder = integer,
sprig_pages = integer,
index_pages = integer,
required_pages = integer,
csum = integer with value "0",
nsum = integer with value "0",
rsum = integer with value "0",
waste = float
/* Need a separate header for REPORT since we want the report description only
* on the first page.
*/
.header report
.left .pr "DB: ", uppercase (db) (c0)
.center .pr "Table Size Information"
.right .pr "As of: ", date('today') (d"03-feb-01")
.nl
.right .pr "Page : ", page_number
.center .pr "TABLE_SIZE_INFO.RW"
.nl 2
.tab 5 .pln
"This report shows all DBA-owned tables, there current size as
reported by"
.tab 5 .pln
"the system catelogs, and the amount of space the table would require if"
.tab 5 .pln
"re-modified with the same parameters as it was before. A %Waste is also"
.tab 5 .pln
"given which shows the relative waste. If OVFLW is indicated, it means the"
.tab 5 .pln
"table/index is actually 'fuller' than it would be if re-modified. For"
.tab 5 .pln
"compressed tables, the compression ratio is shown."
.nl 2
.ul
.tab table_name .pr "Table"
.tab storage_structure .pr "Type"
.right number_pages .pr "# Pages"
.right 45 .pr "Req Pgs"
.right overflow_pages .pr "# Ovflw"
.right num_rows .pr "# Rows"
.right 73 .pr "Waste(Kb)"
.tab 74 .pr "%Waste"
.nl
.noul
.let page_break = 1
.header page
.left .pr "DB: ", uppercase (db) (c0)
.center .pr "Table Size Information"
.right .pr "As of: ", date('today') (d"03-feb-01")
.nl
.right .pr "Page : ", page_number
.nl 2
.ul
.tab table_name .pr "Table"
.tab storage_structure .pr "Type"
.right number_pages .pr "# Pages"
.right 45 .pr "Req Pgs"
.right overflow_pages .pr "# Ovflw"
.right num_rows .pr "# Rows"
.right 72 .pr "Waste(MB)"
.tab 74 .pr "%Waste"
.nl
.noul
.let page_break = 1
.header table_name
.let key_len = 0
.detail
/* figure out the real key_length */
.let key_len = key_len + col_len
.if column_datatype = 'VARCHAR' .then
.let key_len = key_len + 2
.elseif column_datatype = 'DATE' .then
.let key_len = 12
.elseif column_datatype = 'MONEY' .then
.let key_len = 10
.endif
.if column_nulls = 'Y' .then
.let key_len = key_len + 1
.endif
.footer table_name
.tab table_name .pr table_name (c24)
.if is_compressed = 'Y' .then
.tab (storage_structure)
.tab -1
.pr "C" /* for compressed */
.endif
.tab storage_structure .pr storage_structure (c5)
.if number_pages < 1000000 .then
.right number_pages
.pr number_pages ("zzz,zzn")
.else
.right number_pages
.pr number_pages ("zzzzzzn")
.endif
.if storage_structure = 'HASH' or storage_structure = 'ISAM' .then
.if overflow_pages < 1000000 .then
.right overflow_pages
.pr overflow_pages ("zzz,zzn")
.else
.right overflow_pages
.pr overflow_pages ("zzzzzzn")
.endif
.endif
.if num_rows < 10000000 .then
.right num_rows
.pr num_rows ("z,zzz,zzn")
.else
.right num_rows
.pr num_rows ("zzzzzzzzn")
.endif
.if storage_structure = 'HASH' .then
.let rows_per_page = (data_fill * 2008) / (row_width + 2)
.if rows_per_page < 1 .then .let rows_per_page = 1 .endif
.let required_pages = (num_rows / rows_per_page)
.if required_pages < minpages .then
.let required_pages = minpages
.elseif required_pages > maxpages and maxpages > 0 .then
.let required_pages = maxpages
.endif
.elseif storage_structure = 'HEAP' .then
.let rows_per_page = 2008 / (row_width + 2)
.if rows_per_page < 1 .then .let rows_per_page = 1 .endif
.let required_pages = (num_rows / rows_per_page)
.elseif storage_structure = 'BTREE' .then
.let rows_per_page = (data_fill * 2010) / (row_width + 2)
.if rows_per_page < 1 .then .let rows_per_page = 1 .endif
.let free = 2008 - (rows_per_page * (row_width + 2))
.if (free > (2048 - (data_fill * 2048))) and (row_width <= free) .then
.let rows_per_page = rows_per_page + 1
.endif
.if rows_per_page > 512 .then .let rows_per_page = 512 .endif
/* For BTREE secondary indexes, all data is at leaf level */
.if table_type = 'I' .then
.let key_len = row_width
.endif
.let max_keys = (1964 / (key_len + 6)) - 2
.let keys_per_lpage = max_keys * leaf_fill
.let keys_per_ipage = max_keys * index_fill
.if keys_per_lpage < 2 .then .let keys_per_lpage = 2 .endif
.if keys_per_ipage < 2 .then .let keys_per_ipage = 2 .endif
.let leaf_pages = (num_rows / keys_per_lpage)
.let data_pages = leaf_pages *
int4(float8(keys_per_lpage) / rows_per_page + .9999)
.let remainder = num_rows - (leaf_pages * keys_per_lpage)
.if remainder > 0 .then
.let leaf_pages = leaf_pages + 1
.let data_pages = data_pages +
int4(float8(remainder) / rows_per_page + .9999)
.endif
.if table_type = 'I' .then
.let data_pages = 0
.endif
.if leaf_pages <= keys_per_ipage .then
.let sprig_pages = 0
.else
.let sprig_pages = (float8(leaf_pages) / keys_per_ipage +
.9999)
.endif
/* figure number of non-index pages, an iterative calulation which
* is fudged here. Will have a separate calculation for each level
* of non-sprig index pages.
*/
.let index_pages = 0
.if sprig_pages > keys_per_ipage .then
.let x = sprig_pages / keys_per_ipage
.let index_pages = x
.if x > keys_per_ipage .then
.let x = x / keys_per_ipage
.let index_pages = index_pages + x
.if x > keys_per_ipage .then
.let x = x / keys_per_ipage
.let index_pages = index_pages + x
.if x > keys_per_ipage .then
.let x = x / keys_per_ipage
.let index_pages = index_pages + x
.if x > keys_per_ipage .then
.let x = x / keys_per_ipage
.let index_pages = index_pages + x
.endif
.endif
.endif
.endif
.endif /* have non-sprig pages */
.let required_pages = data_pages + leaf_pages + sprig_pages +
index_pages + 2
.elseif storage_structure = 'ISAM' .then
.let rows_per_page = (data_fill * 2008) / (row_width + 2)
.if rows_per_page < 1 .then .let rows_per_page = 1 .endif
.let free = 2008 - (rows_per_page * (row_width + 2))
.if (free > (2048 - (data_fill * 2048))) and (row_width <= free) .then
.let rows_per_page = rows_per_page + 1
.endif
.if rows_per_page > 512 .then .let rows_per_page = 512 .endif
.let data_pages = num_rows / rows_per_page
.let keys_per_ipage = 2008 / (key_len + 2)
.let index_pages = data_pages / keys_per_ipage
.let required_pages = index_pages + data_pages
.if required_pages >= 2 .then
.let required_pages = required_pages + 2
.else
.let required_pages = 2
.endif
.endif
.if required_pages <= 0 .then .let required_pages = 1 .endif
.if required_pages < 1000000 .then
.right 45
.pr required_pages ("zzz,zzn")
.else
.right 45
.pr required_pages ("zzzzzzn")
.endif
/* haven't figured out compression */
.if is_compressed = 'N' .then
.if (number_pages-required_pages) > 0 .then
.right 72 .pr ((number_pages-required_pages))*2 ("zzz,zzz")
.endif
.tab 74
.let waste = (float8(number_pages - required_pages)
* 100 / required_pages)
.if waste < 0 .then
.pr "OvrFul"
.else
.pr waste (f6.1)
.endif
.let nsum = nsum + number_pages
.let rsum = rsum + required_pages
.else
/* print compression ratio */
.tab 74
.let waste = required_pages / number_pages
.pr waste (i4), ':1'
.let csum = csum + number_pages
.endif
.nl
/*
* DEBUG code
*
.pln "tot = ", required_pages (i6), " data= ", data_pages (i6),
" leaf= ", leaf_pages (i6), " sprig= ", sprig_pages (i6),
" idx = ", index_pages (i6)
.pln "lpage = ", keys_per_lpage (i3), " ipage= ", keys_per_ipage (i3),
"rows/p= ", rows_per_page (i3)
*/
.footer report
.need 5
.nl 2
.pln "Database is: ", ((nsum+csum) * 2 / 1024) ("zzz,zzz,zzn"), "Mb"
.pln "It requires: ", ((rsum+csum) * 2 / 1024) ("zzz,zzz,zzn"), "Mb"
.pln "We can save: ", (((nsum+csum) - (rsum+csum)) * 2 / 1024)
("zzz,zzz,zzn"), "Mb"
Mark Kale mdkale@aol.com
Caribou Lake Software, Inc.
www.CaribouLake.com
Ingres Q & A
To William's Home Page
© William Yuan 2000
Email William