Roy Hann writes:
>On Tue, 19 Mar 1996, Ram Varra wrote:
>
>> This may be a repeated question, but I would like to know how to get
>> max overflow chain size in a hash table. Ingres catalogs only
>> provide the total number of overflow pages but there is no way to
>> find size of biggest chain or actual size distribution. Are there
>> any tools that could do this?
>
>The DBA Guides for 6.4 and 1.x both include a listing of a little program
>that you can use to measure the length of overflow chains in hash
>tables. Look in the index under "Overflow". I would cite page numbers
>but there are so many editions of the manuals...
>
>========================================================================
>Roy Hann rhann@tnc.com
>BKB Engineering Ltd., NAIUA Member-at-Large
>11211-76th Avenue,
>Edmonton, Alberta http://www.tnc.com/bkb
>T6G 0K2 Tel: (403)497-2409
>Canada FAX: (403)436-6055
>========================================================================
>
>
>
Unfortunately, the example program in the 6.4 (Dec '91) DBA Guide will not
work for a HASH table. All you get is a list of the primary pages that are
occupied by rows, followed by a list of the overflow pages. There is no
indication as to which overflow pages are chained from which primary pages.
If anyone knows how to do this, I'd be interested.
Cheers,
Liam
--
Liam McCauley Email: Liam_McCauley@QSP.co.uk
Database Administrator Tel : (0191)402 3283
Quality Software Products
Gateshead
UK
In article <314EE75B.257B@sedona.intel.com>,
Ram Varra writes:
> This may be a repeated question, but I would like to know how to get
> max overflow chain size in a hash table. Ingres catalogs only
> provide the total number of overflow pages but there is no way to
> find size of biggest chain or actual size distribution. Are there
> any tools that could do this?
Unfortunately, I don't have an SQL statement to retrieve the maximum
overflow chain size. But the way to find this out, is:
1. The number of primary pages within your table is (number_pages -
overflow_pages). I'll call it n.
2. (tid/512) gives the number of the page, where the record resides.
3. The primary pages have numbers from 0 until (n-1).
4. All overflow pages have number greater than or equal to n.
5. SELECT tid/512 AS page_no FROM returns a primary page,
the overflow chain connected to it, the next primary page and so on.
6. Count the different page numbers greater than or equal to n, until
you get a page number less than n. That's the length of the overflow
chain.
7. The maximum length of an overflow chain is what you look for.
Caution: You cannot use a SELECT DISTINCT nor any complicated query
since it would re-order the records and you would loose the necessary
information.
Perhaps you like to write a small C program to automate this process.
I don't know if there is already such a program somewhere.
Christopher Etz
--
________________________________________________________________________
Christopher Etz Kopernikusstr. 28 D-65929 Frankfurt/Main
cetz@cetz.rhein-main.de Tel.: +49 69 318091 Telefax: +49 69 318091
In article <314EE75B.257B@sedona.intel.com> Ram Varra
writes:
>This may be a repeated question, but I would like to know how to get
>max overflow chain size in a hash table. Ingres catalogs only
>provide the total number of overflow pages but there is no way to
>find size of biggest chain or actual size distribution. Are there
>any tools that could do this?
>
>Thanks in advance,
>Ram Varra
>rvarra@sedona.intel.com
I wrote a report writer script to do this. It works for HASH and ISAM. I give
no warranty or guarantee that there are no bugs. Use at your own risk or for
your own enjoyment.
/*
Written by Steve Caswell
Palmer & Associates, Inc.
Norcross Georgia
History:
30-jan-1992 originally published in INGRES 6 Database Administration
and Maintenance training class
02-mar-1992 added printing summary for last primary page found in select
*/
.NAME overflow
.DECLARE
totprim = INTEGER NOT NULL
,totpages = INTEGER NOT NULL
,totov = INTEGER NOT NULL
,chain = INTEGER NOT NULL
,chainlen = INTEGER NOT NULL
,rmain = INTEGER NOT NULL
,longchain = INTEGER NOT NULL
,longchainlen = INTEGER NOT NULL
,highdata = INTEGER NOT NULL
,highov = INTEGER NOT NULL
.SETUP
SET LOCKMODE SESSION WHERE READLOCK=NOLOCK;
.QUERY
SELECT ALL
date('now') AS nowdate
,dbmsinfo('username') AS username
,dbmsinfo('database') AS dbname
,relmain
,relspec
,0 AS datapage
,0 AS tidp
,1 AS type
FROM
iirelation
WHERE relid = '$table_name'
AND relowner = dbmsinfo('username')
UNION ALL
SELECT ALL
date('now') AS nowdate
,dbmsinfo('username') AS username
,dbmsinfo('database') AS dbname
,0 AS relmain
,0 AS relspec
,int4(tid/512)+1 as datapage
,tid AS tidp
,2 AS type
FROM
$table_name
.BREAK type, datapage
.HEADER report
.LET totprim = 0
.LET totpages = 0
.LET totov = 0
.LET chain = 1
.LET chainlen = 1
.LET longchain = 0
.LET longchainlen = 0
.LET highdata = 0
.LET highov = 0
.HEADER type
.IF type = 1 .THEN
.IF relspec != 5 AND relspec != 7 .THEN
.PRINT "Table must be hash or isam"
.NL
.PRINT "Any overflow information should be ignored for this table"
.NL
.LEFTMARGIN -1
.ENDIF
.LET rmain = relmain
.PRINT "Overflow distribution for table '", $table_name(c0),
"' owned by '", username(c0), "'"
.NL
.PRINT "Database: ", dbname(c0)
.NL
.PRINT "Generated ", nowdate
.NL 2
.ENDIF
.NL
.HEADER datapage
.IF type = 2 .THEN
.IF datapage <= rmain .THEN
.IF totpages > 0 .THEN
.PRINT "End of chain ", varchar(chain)(c0), ": ",
varchar(chainlen-(chainlen-1))(c0), " primary pages and ",
varchar(chainlen-1)(c0), " overflow pages"
.IF (chainlen-1) > longchainlen .THEN
.LET longchain = chain
.LET longchainlen = chainlen - 1
.ENDIF
.LET chain = chain + 1
.ENDIF
.NL 2
.PRINT "New chain: ", varchar(chain)(c0),
", primary page: ", varchar(datapage)(c0)
.NL
.LET chainlen=1
.LET totprim = totprim + 1
.LET highdata = datapage
.ELSE
.PRINT " overflow page: ",
varchar(datapage)(c0), " in chain ", varchar(chain)(c0)
.NL
.LET totov = totov + 1
.LET chainlen = chainlen + 1
.LET highov = datapage
.ENDIF
.LET totpages = totpages + 1
.ENDIF
.DETAIL
/*
for debugging purposes
*/
/*
.print "type: ", type, " datapage: ", datapage, " tid: ", tidp
.nl
*/
.FOOTER report
.IF totpages > 0 .THEN
.PRINT "End of chain ", varchar(chain)(c0), ": ",
varchar(chainlen-(chainlen-1))(c0), " primary pages and ",
varchar(chainlen-1)(c0), " overflow pages"
.ENDIF
.NL 2
.PRINT "Total pages: ", varchar(totpages)(c0)
.NL
.PRINT "Total main pages: ", varchar(totprim)(c0)
.NL
.PRINT "Total overflow: ", varchar(totov)(c0)
.NL
.PRINT "Longest overflow chain is ", varchar(longchainlen)(c0),
" pages on chain number ", varchar(longchain)(c0)
.NL
.PRINT "Highest main page number: ", varchar(rmain)(c0)
.NL
.PRINT "Highest main page with data: ", varchar(highdata)(c0)
.NL
.PRINT "Highest overflow page number: ", varchar(highov)(c0)
.NL
--
Steve Caswell | (770) 448-7727 | "The opinions expressed are my
VP Technology & Quality | sfc@tpghq.com | own. They may not be perfect,
The Palmer Group | http://www.tpghq.com | but they're all I've got."
-
Ingres Q & A
-
To William's Home Page
© William Yuan 2000
Email William