[Qgis-developer] Oracle provider slow for large datasets - fetches all features every time
steve.pritchard at bto.org
Wed Oct 7 08:07:41 PDT 2015
Many thanks for getting back to me so quickly.
In "Log messages" I get:
2015-10-07T14:55:25 1 No spatial index on column
BTOWATCH.LOC_AUX_GEO_WEBS_VW.GEOM found - expect poor performance.
The view column does actually have a spatial index on it - on the
Looking more closely at the SQL, I see that QGIS does attempt a query with
SELECT "GEOM" FROM "BTOWATCH"."LOC_AUX_GEO_WEBS_VW" WHERE
However this fails with error 29902.
I've tried this same query with sqlplus and I get:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-13034: Invalid data in the SDO_ORDINATE_ARRAY in SDO_GEOMETRY object
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 416
29902. 00000 - "error in executing ODCIIndexStart() routine"
*Cause: The execution of ODCIIndexStart routine caused an error.
*Action: Examine the error messages produced by the indextype code and
take appropriate action.
I don't think this is anything to do with not having a spatial index (that
would be "ORA-13226: interface not supported without a spatial index").
I think the problem is with the coordinates given:
If I change these to (-1,-1,1,1) the query works fine:
SELECT "GEOM" FROM "BTOWATCH"."LOC_AUX_GEO"
*Is it ok to report this as a bug? If so, which Category should it go
When I access a table directly with QGIS, I can see that it *is* using
SDO_FILTER(...). However I also see that it is doing the following,
presumably to determine the extent:
SELECT SDO_TUNE.EXTENT_OF('BTOWATCH.HOUMA_TEST_GRIDS','BOUNDARY') FROM dual;
SELECT SDO_AGGR_MBR("BOUNDARY") FROM "BTOWATCH"."HOUMA_TEST_GRIDS";
The first query appears to return a perfectly sensible result
(coords: -11.4,48.9 1.9,60.9), and it takes 0.14 seconds.
However the second query takes 6 mins 9 secs !!
Why does QGIS need to do SDO_AGGR_MBR(...)?
On 6 October 2015 at 23:18, Jürgen E. <jef at norbit.de> wrote:
> Hi Steve,
> What messages do you get in the message log?
> I suppose it's a view and sdo_filter fails on it. Because sdo_filter
> a spatial index and qgis probably doesn't find it on that view and tries
> query with sdo_filter that fails and therefore doesn't use it later.
> problem might be the SRID.
> And without sdo_filter QGIS only resort is to retrieve all and filter them
> the client side.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Qgis-developer