No spatial index on Oracle views

[qgib]

Author Name: Médéric RIBREUX (Médéric RIBREUX)
Original Redmine Issue: 12619
Affected QGIS version: 2.8.1
Redmine category:data_provider/oracle
Assignee: Jürgen Fischer

---

Hello,

I've got an Oracle Spatial view on a spatially enabled table (which is well declared in Oracle metadata table).

When I try to open the view, QGIS seems to be unable to retrieve the spatial index of the primary table. It only tries to find the spatial index of the view which doesn't exists as under Oracle, a view cannot have a spatial index. I've got the message: "No spatial index on column SCHEMA.VIEW.GEOM found - expect poor performance." and indeed, there is poor performance.

We need to find a way to see if the view has got a spatial index on it. I think that we can try to use a SELECT query with a spatial filter function (sdo_filter for example) on the view and see if Oracle returns an error (ORA-13226 or equivalent) in case there is no spatial index for the view. If there is an error, the view cannot be used with spatial indexes, otherwise, it can and QGIS should use the same queries for spatially enabled indexed tables. It is the only simple way I can find for the moment...

This problem only affects views as materialized views can have a directly attached index.

Cheers for the fix !

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Did you verify that this is still the case in master and/or the release-2_8 branch? Both have nightly builds in OSGeo4W (qgis-dev and qgis-rel-dev respectively).

[qgib]

Author Name: Jukka Rahkonen (Jukka Rahkonen)

---

I would like to see first some SQL that QGIS is sending to Oracle when a) target is a table with spatial index b) target is such a view that triggers QGIS to send the warning. I am not so sure that there is any difference because Oracle is utilizing spatial index transparently if it exists and client does not need to care about it.

[qgib]

Author Name: Médéric RIBREUX (Médéric RIBREUX)

---

Hello,

Jürgen, I will close this bug as it doesn't occurs on nightly build (but present in 2.8.1). I can see that QGIS is throwing a query that tries to use sdo_filter function just after a query which tries to list the spatial indexes of the layer. Something like:

SELECT "GEOM" FROM "SCHEMA"."TABLE" WHERE sdo_filter("GEOM",mdsys.sdo_geometry(2003,27562,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(1,1,-1,-1)))='TRUE'

At first I thought it was not working because of a difference between the declared (in USER_GEOM_METADATA) SRID of my view and the SRID of the differents tables upon which my view was built. After modifying the SRID of the view, there is nothing in QGIS log and I can see that all the queries on the view are made with sdo_filter.

Well, sorry for the noise...

Jukka, when QGIS is not able to find an index on an Oracle table, it grabs the whole layer and this can takes lots of time.

Here is the query made by QGIS when there is no index:

SELECT "GEOM","GID",... FROM "SCHEMA"."TABLE" "featureRequest"

Here is the query made by QGIS when there is a spatial index:

SELECT "GEOM","GID",... FROM "SCHEMA"."TABLE" "featureRequest" WHERE sdo_filter("GEOM",
mdsys.sdo_geometry(2003,
                   NULL,
                   NULL,
                   mdsys.sdo_elem_info_array(1,1003,3),
                   mdsys.sdo_ordinate_array(300433.61760321096517146,252442.49572887236718088,302415.39868119265884161,253959.14463810005690902))) = 'TRUE'
AND mod("featureRequest"."GEOM".sdo_gtype,100) IN (1,5)

When there is a spatial index on an Oracle table, you can use the sdo_filter function. If there is no spatial index, "you can't use this function":https://docs.oracle.com/html/B10826_01/sdo_operat.htm#sthref878 which is very convenient to narrow the query to an extent...

---

• status_id was changed from Open to Closed