Conference orarep::nomahs::rdb_60

5099.0. "record length in RDB$AIP or ANALYZE vs node size?" by 8292::PJACOB (Patrick [email protected]) Tue Mar 04 1997 06:47

In MF_PERSONNEL, DEPARTMENTS_INDEX is a unique index SORTED stored in MIXED 
storage area DEPARTMENTS. The RMU/DUMP of area DEPARTMENTS outputs a node size 
of 430. So far so good. But in RDB$AIP we see a record length of 215 bytes ; 
shouldn't it be 430 ? RMU/ANALYZE also shows a Record length of 215 ; shouldn't
it be 430 too ? 

If I define another index, let say DEPT_IND, with a node size of 430, I can 
see this 430 in place of the previous 215. 
	create unique index DEPT_IND
	on DEPARTMENTS ( DEPARTMENT_CODE asc)
	type is SORTED node size 430 store in DEPARTMENTS;

Any explanation will be appreciated. 

Can this explain an excessive page checking not documented in chapter 3.10 of
Guide to database Performance and Tuning ? ( We are talking about UNIQUE index
which have not been altered )


Patrick

$ @test.com
$ rmu/dump/lareas=rdb$aip/output=larea_rdbaip.txt mf_personnel
$ search/window=(4,8) larea_rdbaip.txt departments_inde
                                        entry #13
                        00000000  0323  first area bitmap page 0
                       0005 004E  0327  logical area 78, physical area 5
                              11  032B  area name length 17 bytes
45444E495F53544E454D545241504544  032C  area name 'DEPARTMENTS_INDE'
  000000000000000000000000000058  033C  area name 'X..............'
                        00000028  034B  snaps enabled TSN 40
                            00D7  034F  record length 215 bytes
                        00000000  0351  MBZ '....'
                              01  0355  entry is in use
                            0000  0356  MBZ '..'
                          000000  0358  thresholds are (0,0,0)
                          000000  035B  MBZ '...'
$ rmu/dump/areas=DEPARTMENTS/output=area_DEPARTMENTS.txt mf_personnel
$ search area_DEPARTMENTS.txt "node size"
                                  ....  total B-tree node size: 430
$ rmu/analyze/out=anal.txt mf_personnel
$ search/window=(0,4) anal.txt departments_inde
 Logical area: DEPARTMENTS_INDEX for storage area : DEPARTMENTS
 Larea id: 78,  Record type: 0, Record length: 215, Not Compressed

 Data records: 1, bytes used: 428 (1%)
   average length: 428
$

    
    Oops, sorry . I tested in 6.0-15 but it is documented in V7.0 release
    notes. In addition, I get the confirmation that this can cause
    excessive page checking.
    
    Since I do not see any reference to this in DECRDB060A_ECO6.RELEASE_NOTES, 
    I presume there will be no fix in 6.0A, rigth ? ( nor in 6.1 )
    
    Patrick. 

~In MF_PERSONNEL, DEPARTMENTS_INDEX is a unique index SORTED stored in MIXED 
~storage area DEPARTMENTS. The RMU/DUMP of area DEPARTMENTS outputs a node size 
~of 430. So far so good. But in RDB$AIP we see a record length of 215 bytes ; 
~shouldn't it be 430 ? RMU/ANALYZE also shows a Record length of 215 ; shouldn't
~it be 430 too ? 

When an index is defined which allows DUPLICATES (i.e. non-unique) we default
the AIP record-length to half the default node size.  This was done (way back,
maybe in V2.0) to allow a reasonable size for both the btree node and the
probable several (smaller) duplicate nodes.

~If I define another index, let say DEPT_IND, with a node size of 430, I can 
~see this 430 in place of the previous 215. 
~	create unique index DEPT_IND
~	on DEPARTMENTS ( DEPARTMENT_CODE asc)
~	type is SORTED node size 430 store in DEPARTMENTS;
~
~Any explanation will be appreciated. 

In Rdb 4.2 we used the node size to size the AIP for UNIQUE indices.  In Rdb7
we also use the default node size for UNIQUE indices with no NODE SIZE
specified.

~Can this explain an excessive page checking not documented in chapter 3.10 of
~Guide to database Performance and Tuning ? ( We are talking about UNIQUE index
~which have not been altered )

~    Since I do not see any reference to this in DECRDB060A_ECO6.RELEASE_NOTES, 
~    I presume there will be no fix in 6.0A, rigth ? ( nor in 6.1 )
    
We will not backport the change.  The correct action is to define appropriate
NODE SIZE or THRESHOLDS for the index.

Ian