Re: Rtree index creation very slow

Jose Luis Matute wrote: > Hi, > > I'm dealing with a rtree index that takes 75 hrs to get created. The > table has 22M rows, nearly 2Gb data. Creating primary/foreign keys is > reasonabily fast (1 - 1,5 hrs), but creating the rtree index is a > nightmare and won't fit in the 12 hr. window to stop the service (as the > trials ina a development machine shows - reasonabily similar boxes). > I've observed that the disk isn't saturated, nor does memory (buffer > pool/DSS memory). It doesn't parallel (only one CPU used, although PDQ > parameters are set 'correctly'). > > Any hint will be appreciated! > > Thanks in advance. How are you building the index? Bottom_up or top_down? An R-tree index can be built much more quickly from the bottom up than from the top down, typically 10 to 20 times faster. This increased speed comes at a price: you must create a temporary dbspace of sufficient size to sort all the spatial data in the table. If a temporary dbspace does not exist, or it is too small, then the access method builds the R-tree index using the slower top_down algorithm. The create index options BOTTOM_UP_BUILD, BOUNDING_BOX_INDEX and NO_SORT have tremendous influence on the index build speed. See the chapter "Using R-Tree Indexes" in "IBM Informix Spatial DataBlade Module User's Guide" manual as well as the "IBM Informix R-Tree Index User's Guide" manual. Claus