Spatial Users Forum

Re: BTree Index Extent Sizes with Spatial Tables

Posted By: STUART MCCANN
Date: Thursday, 1 March 2007, at 8:32 p.m.

Unfortunately it seems that once you use a spatial type (and I suspect other UDTs, although I haven't tested) in a table the BTree index extent sizing algorithm gets it wrong even though the indexes are on built in data types.

For example, Given the following DDL and loading some data

create table testt (

id integer ,

shape st_multipolygon
) put shape in ( blobsbs ) extent size 81400 ;

create index testi on testt ( id ) ;

The below output from oncheck -pt clearly shows the first extent size for table testt as 40700 pages (81400KB). The index is on an int type and has been allocated a first extent sized of just 142 Pages (284Kb), however 2410 pages (4820Kb) have been used to store the index.

TBLspace Report for sdecad:informix.testt

Physical Address 8:2012

Creation date 02/22/2007 14:55:09

TBLspace Flags 901 Page Locking

TBLspace contains VARCHARS

TBLspace use 4 bit bit-maps

Maximum row size 2056

Number of special columns 1

Number of keys 0

Number of extents 1

Current serial value 1

Pagesize (k) 2

First extent size 40700

Next extent size 8

Number of pages allocated 40700

Number of pages used 11125

Number of data pages 11122

Number of rows 68438

Partition partnum 8388717

Partition lockid 8388717

Extents

Logical Page Physical Page Size Physical Pages

0 8:36823 40700 40700

Index testi fragment partition cadreposdbs in DBspace cadreposdbs

Physical Address 8:2013

Creation date 02/22/2007 14:55:09

TBLspace Flags 801 Page Locking

TBLspace use 4 bit bit-maps

Maximum row size 2056

Number of special columns 0

Number of keys 1

Number of extents 12

Current serial value 1

Pagesize (k) 2

First extent size 142

Next extent size 4

Number of pages allocated 2410

Number of pages used 2409

Number of data pages 0

Number of rows 0

Partition partnum 8388718

Partition lockid 8388717

I guess that most likely this is due to the maximum row size calculated, (2048 maxlen for the spatial type and 4 bytes for the int).

So, in our systems we need to drop and recreate B-Tree indexes on highly active (insert, update delete) spatial tables daily as they become interleaved within their DBSpaces. Not a very high availability option though, everybody/everything out. The drop/create index online statement causes too many concurrency problems to contenmplate using in mission critical production systems.

Alternatively, we can create the table with a first extent size 20 times greater than is required for the data so that we can get a properly sized first extent on the index. Just a bit wasteful on disk though. It would be really nice if you could specify an extent size when creating the index or change the index extent size algorithm to handle tables with spatial/UDTs better.

Spatial Users Forum is maintained by Administrator with WebBBS 5.12.