[gdal-dev] Slow OGROCITableLayer::GetExtent() query

[Vautour, André (INT)]

Hi all,

Currently OGROCITableLayer::GetExtent() calculates the cover by basically iterating over all of the features, either by using the min/max aggregate functions in the database, or by doing it with the features themselves.

The query being done currently looks like the following:

SELECT
    MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(t.SomeGeometryColumn ,m.DIMINFO,1)) AS MINX,
    MIN(SDO_GEOM.SDO_MIN_MBR_ORDINATE(t.SomeGeometryColumn, m.DIMINFO,2)) AS MINY,
    MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(t.SomeGeometryColumn, m.DIMINFO,1)) AS MAXX,
    MAX(SDO_GEOM.SDO_MAX_MBR_ORDINATE(t.SomeGeometryColumn, m.DIMINFO,2)) AS MAXY
FROM ALL_SDO_GEOM_METADATA m, SomeOwner.SomeTable t
WHERE m.TABLE_NAME = UPPER('SomeTable')
    AND m.COLUMN_NAME = UPPER('SomeGeometryColumn')
    AND OWNER = UPPER('SomeOwner');

I am trying to connect to a table that has around 50,000 rows, and that query with that particular table takes around 35 secs.

Given that the table has a spatial tree index, would it make more sense to simply get the root MBR of the spatial index:

SELECT
    SDO_GEOM.SDO_MIN_MBR_ORDINATE(m.SDO_ROOT_MBR, 1) AS MINX,
    SDO_GEOM.SDO_MIN_MBR_ORDINATE(m.SDO_ROOT_MBR, 2) AS MINY,
    SDO_GEOM.SDO_MAX_MBR_ORDINATE(m.SDO_ROOT_MBR, 1) AS MAXX,
    SDO_GEOM.SDO_MAX_MBR_ORDINATE(m.SDO_ROOT_MBR, 2) AS MAXY
FROM ALL_SDO_INDEX_METADATA m, ALL_SDO_INDEX_INFO i
WHERE i.INDEX_NAME = m.SDO_INDEX_NAME
    AND i.SDO_INDEX_OWNER = m.SDO_INDEX_OWNER
    AND i.TABLE_NAME = UPPER('SomeTable')
    AND i.COLUMN_NAME = UPPER('SomeGeometryColumn')
    AND i.TABLE_OWNER = UPPER('SomeOwner');

That query is pretty much instantaneous and gives me the same exact result.

The main issue that I see is that there isn't any guarantee that the spatial index is completely up-to-date, so it could not give the same result. One option would be to do the same as the PostgreSQL driver and only do the quick query if force is set to false. I'd also would be open to adding a configuration option to use the root MBR instead of the current behavior.

Thoughts?
Andre

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20170504/035202de/attachment.html>