Bug report #11358

Spatial index is not used in Oracle views

Added by Viktor Rasmussen over 9 years ago. Updated over 9 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/Oracle
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

Description

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.


Related issues

Duplicated by QGIS Application - Bug report #11722: Hang on Identify Feature on Oracle layers Closed 2014-11-25

Associated revisions

Revision 991f94d3
Added by Jürgen Fischer over 9 years ago

oracle provider:
  • if a no spatial index is found try sdo_filter (fixes #11358)
  • allow specifing connection parameters (eg. OCI_ATTR_PREFETCH_ROWS=10000)

History

#1 Updated by Giovanni Manghi over 9 years ago

  • Category set to Data Provider/Oracle

#2 Updated by Jürgen Fischer over 9 years ago

  • Status changed from Open to Closed

Also available in: Atom PDF