[Qgis-developer] Oracle provider slow for large datasets - fetches all features every time

[Steve Pritchard]

Jürgen,

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
underlying table.

Looking more closely at the SQL, I see that QGIS does attempt a query with
SDO_FILTER:

SELECT "GEOM" FROM "BTOWATCH"."LOC_AUX_GEO_WEBS_VW" WHERE
sdo_filter("GEOM",mdsys.sdo_geometry(2003,8307,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(1,1,-1,-1)))='TRUE'

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:
mdsys.sdo_ordinate_array(1,1,-1,-1)

If I change these to (-1,-1,1,1) the query works fine:

SELECT "GEOM" FROM "BTOWATCH"."LOC_AUX_GEO"
WHERE
sdo_filter("GEOM",mdsys.sdo_geometry(2003,8307,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(-1,-1,1,1)))='TRUE'

*Is it ok to report this as a bug? If so, which Category should it go
under?*


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(...)?

Regards,

Steve


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
> requires
> a spatial index and qgis probably doesn't find it on that view and tries
> one
> query with sdo_filter that fails and therefore doesn't use it later.
> Another
> problem might be the SRID.
>
> And without sdo_filter QGIS only resort is to retrieve all and filter them
> on
> the client side.
>
> Jürgen
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20151007/2a3418ff/attachment-0001.html>