Bug report #11358
Spatial index is not used in Oracle views
|Affected QGIS version:||2.4.0||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||19649|
When adding a layer that corresponds to a view in Oracle then QGIS will not use a spatial filter when fetching data. The result is that all data is fetched into QGIS regardless of zoom level and performance is very poor for large datasets.
This behaviour is not seen when using Oracle tables directly. It is also not seen when using PostgreSQL views.
I have used an SQL tracker to see the sql generated by qgis.
It seems that the problem arises from this query performed when adding an Oracle layer.
SELECT i.index_name,i.domidx_opstatus FROM all_indexes i JOIN all_ind_columns c ON i.owner=c.index_owner AND i.index_name=c.index_name AND c.column_name='GEOMETRI' WHERE i.table_owner='DAGI_10E_NOHIST_L1' AND i.table_name='KOMMUNEINDDELING' AND i.ityp_owner='MDSYS' AND i.ityp_name='SPATIAL_INDEX'
As the spatial index is on the table and not on the view itself, QGIS will not find the spatial index in the data dictionary.
And I suppose that when not finding a spatial index it will wrongly not use sdo_filter clauses in the data queries.