INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

Consider the following example:

create table t1 (col1 int, col2 st_geometry);

create index idx1 on t1(col2 st_geometry_ops) using rtree;

If the xyunits column of the included spatial reference system
with SRID == 4 is modified to increase it's value with the
following SQL statement:

update spatial_references
set xyunits = 596523200
where srid = 4;

If you then load data to the table and run update statistics
high and/or medium and try and run the following query with set
explain on, you would see the server pick a sequential scan,
rather then using the index on the spatial column, col2.

If the distributions on the spatial column are dropped, the
query will go back to using the spatial index.

example query:

select count(*) from t1
where
informix.SE_EnvelopesIntersect(col2,ST_PolyFromText('POLYGON
((-43.4274583345408 -22.4553006230988, -43.4274583345408
-22.4482426104252, -43.4105975264892 -22.4482426104252,
-43.4105975264892 -22.4553006230988, -43.4274583345408
-22.4553006230988))' , 4 ) );


Bad set explain output showing sequential scan:

Estimated Cost: 34001
Estimated # of Rows Returned: 1

  1) informix.t1: SEQUENTIAL SCAN

        Filters: informix.se_envelopesintersect(informix.t1.col2
,UDT )

If the xyunits value is not modified and left with it's default
the query would use the index and the set explain output would
look like this:

Estimated Cost: 1
Estimated # of Rows Returned: 1

  1) informix.t1: INDEX PATH

    (1) Index Name: informix.t1_idx1
        VII Index Keys: col2   (Serial, fragments: ALL)
        VII Index Filter:
informix.se_envelopesintersect(informix.t1.col2,UDT )

****************************************************************
* USERS AFFECTED:                                              *
* Users of IDS prior to 12.10.xC13.                            *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* When xyunits value in spatial_references table gets too      *
* large, indexes on spatial columns may not get used when      *
* distributions are built on that column.                      *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

07.01.2019
24.09.2019
24.09.2019

12.10.xC13