<div dir="ltr"><div class="gmail_extra"><div><span style="font-size:12.8px"><font color="#000000">Jürgen,</font></span><br><div><span style="font-size:12.8px"><font color="#000000"><br></font></span></div><div><span style="font-size:12.8px"><font color="#000000">Many thanks for getting back to me so quickly.</font></span></div><div><br></div><div><span style="font-size:12.8px"><font color="#000000">In "Log messages" I get:</font></span></div><div><span style="font-size:12.8px"><font color="#000000"><br></font></span></div><div><font face="monospace, monospace">2015-10-07T14:55:25 1 No spatial index on column BTOWATCH.LOC_AUX_GEO_WEBS_VW.GEOM found - expect poor performance.<span style="font-size:12.8px"><font color="#000000"><br></font></span></font></div><div><br></div><div>The view column does actually have a spatial index on it - on the underlying table.</div><div><br></div><div>Looking more closely at the SQL, I see that QGIS does attempt a query with SDO_FILTER:</div><div><br></div><div><div><font face="monospace, monospace">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'</font></div></div><div><br></div><div>However this fails with error 29902.</div><div><br></div><div>I've tried this same query with sqlplus and I get:</div><div><br></div><div><div><font face="monospace, monospace">ORA-29902: error in executing ODCIIndexStart() routine</font></div><div><font face="monospace, monospace">ORA-13034: Invalid data in the SDO_ORDINATE_ARRAY in SDO_GEOMETRY object</font></div><div><font face="monospace, monospace">ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 416</font></div><div><font face="monospace, monospace">29902. 00000 - "error in executing ODCIIndexStart() routine"</font></div><div><font face="monospace, monospace">*Cause: The execution of ODCIIndexStart routine caused an error.</font></div><div><font face="monospace, monospace">*Action: Examine the error messages produced by the indextype code and</font></div><div><font face="monospace, monospace"> take appropriate action.</font></div></div><div><br></div><div>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").</div><div><br></div><div>I think the problem is with the coordinates given: <span style="font-family:monospace,monospace">mdsys.sdo_ordinate_array(1,1,-1,-1)</span></div><br>If I change these to (-1,-1,1,1) the query works fine:</div><div><br></div><div><div><font face="monospace, monospace">SELECT "GEOM" FROM "BTOWATCH"."LOC_AUX_GEO" </font></div><div><font face="monospace, monospace">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'</font></div><div><br></div><div><b>Is it ok to report this as a bug? If so, which Category should it go under?</b></div><div> </div><div><br></div><div>When I access a table directly with QGIS, I can see that it <b><u>is</u></b> using SDO_FILTER(...). However I also see that it is doing the following, presumably to determine the extent:</div><div><br></div><div><div>SELECT SDO_TUNE.EXTENT_OF('BTOWATCH.HOUMA_TEST_GRIDS','BOUNDARY') FROM dual;</div><div>SELECT SDO_AGGR_MBR("BOUNDARY") FROM "BTOWATCH"."HOUMA_TEST_GRIDS";</div></div><div><br></div><div>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.</div><div>However the second query takes 6 mins 9 secs !!</div><div><br></div><div>Why does QGIS need to do SDO_AGGR_MBR(...)?</div><div><br></div><div>Regards,</div><div><br></div><div>Steve</div><div><br></div><div><br></div><div class="gmail_extra"><div class="gmail_quote">On 6 October 2015 at 23:18, Jürgen E. <span dir="ltr"><<a href="mailto:jef@norbit.de" target="_blank">jef@norbit.de</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">Hi Steve,<br><br>What messages do you get in the message log?<br><br>I suppose it's a view and sdo_filter fails on it. Because sdo_filter requires<br>a spatial index and qgis probably doesn't find it on that view and tries one<br>query with sdo_filter that fails and therefore doesn't use it later. Another<br>problem might be the SRID.<br><br>And without sdo_filter QGIS only resort is to retrieve all and filter them on<br>the client side.<br><font color="#888888"><br>Jürgen<br></font></blockquote><div><font color="#888888"><br></font></div></div></div></div>
</div></div>