INFORMIX SERVER / 5725A3900 / B70 - IDS 11.70

So after fixing APAR IT27689, which dealt with distributions not
getting built correctly when the xyunits of a spatial reference
system was increase past a certain point, it seems the
selectivity of spatial filters may be too small now.  This then
causes the optimizer to favor paths using the rtree index on the
spatial column which may result in more work in the server and
slower query times.

If the distributions are dropped, a new, faster/less work plan
could be picked.  It would not be expected that a better query
plan be generated with less information available (like having
the distributions on the spatial column not exist).

For example in the query statistics part of set explain output
lets look at the following 2 outputs, 1 with distributions on
the spatial column and the next without.

With distributions:

Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                ec
  t2                se

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t1     26241      1         26241      00:00.19   1

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t2     3          567       3          00:00.08   0

  type     rows_prod  est_rows  time       est_cost
  -------------------------------------------------
  nljoin   3          1         00:00.28   4

t1 is the table with spatial and the scan showed it was using
the rtree...this plan causes 26241 rows to be produced to be
nested loop joined to t2.  The overall time of the query is ~
0.3 seconds

Without distributions on spatial column:

Query statistics:
-----------------

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                se
  t2                ec

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t1     567        567       567        00:00.00   33

  type     table  rows_prod  est_rows  rows_scan  time
est_cost

----------------------------------------------------------------
---
  scan     t2     3          565006    567        00:00.00   2

  type     rows_prod  est_rows  time       est_cost
  -------------------------------------------------
  nljoin   3          57        00:00.00   1083

This query scans the smaller table 1st and joins to the table
with the spatial column using a different index and applies the
spatial filter as a table level (so after the join has
happened).  This query scans fewer rows and is taking so little
time that the output shows the time to run is 0.0 seconds.

This small difference can be huge on busy systems that run the
affected queries heavily.

****************************************************************
* USERS AFFECTED:                                              *
* Users of IDS versions prior to 12.10.xC14.                   *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* If xyunits in a spatial reference system is increased, when  *
* data distributions are added, the optimizer may favor the    *
* spatial index too much.                                      *
****************************************************************
* RECOMMENDATION:                                              *
****************************************************************

keiner bekannt / siehe Local-Fix

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

10.05.2019
23.09.2019
19.02.2020

12.10.xC14