Using Oracle Views in GeoServer

Question

I'm trying to pin down the requirements or the optimal setup for using an Oracle View in GeoServer 2.16.2. From what I have read in various forums is that for an Oracle View to work right in GeoServer it must have the following:

1. an entry for the Oracle View in user_sdo_geom_metadata
2. a spatial index that is owned by the user or profile that is making the call.
3. Oracle User needs privileges to MDSYS Views & Tables

My specific situation is that I cannot create a Materialized View or other Table that is rebuilt periodically.

The current setup is similar to the following example: The GeoServer has a store using USER1 that uses an Oracle View that is owned by USER1. However, the underlying tables and indexes for the Oracle View are owned by a different user, call them USER2. Currently, the performance of this setup is terrible.

I would like to push harder to having this setup conform to the 3 requirements that I listed above, but I cannot find the exact documentation that says that points 1, 2, 3 are required. It is just a theory.

With respect to item #3. I'm seeing processes in the log that seem to be stuck on queries such as the following:

SELECT SDO_INDEX_OWNER,SDO_INDEX_NAME,SDO_PARTITIONED,SDO_LAYER_GTYPE,SDO_INDEX_VERSION,SDO_INDEX_DIMS,SDO_RTREE_DIMENSIONALITY,SDO_INDEX_GEODETIC,SDO_INDEX_TYPE,SDO_LEVEL,SDO_NUMTILES,SDO_NUM_ROWS,SDO_NUM_BLKS,SDO_OPTIMIZED_NODES,SDO_MAXLEVEL,SDO_INDEX_TABLE,SDO_INDEX_PRIMARY,SDO_RTREE_HEIGHT,SDO_RTREE_NUM_NODES,SDO_RTREE_FANOUT,SDO_RTREE_ROOT,SDO_RTREE_SEQ_NAME,SDO_INDEX_PARTITION,SDO_RTREE_PCTFREE,SDO_RTREE_QUALITY,SDO_TABLESPACE,SDO_INDEX_STATUS,SDO_NL_INDEX_TABLE,SDO_DML_BATCH_SIZE,SDO_RTREE_ENT_XPND, SDO_ROOT_MBR FROM MDSYS.SDO_INDEX_METADATA_TABLE WHERE sdo_index_owner = :owner AND sdo_index_name = :iname ORDER BY SDO_INDEX_PRIMARY

When I try and query the table MDSYS.SDO_INDEX_METADATA_TABLE with the User that I have in my GeoServer Oracle Store, it says the ORA-00942: table or view does not exist. I can see the table in the MDSYS in Oracle Developer.

I would change the permissions myself for the User that I use if I could. Can someone tell me if the USER needs to be granted SELECT privileges for MDSYS for a GeoServer layer that connects to an Oracle database to be efficient?

I'm kind of surprised that the layer works at all since there seems to be no access to the MDSYS objects and no GEOMETRY_COLUMNS. If I select all 50,000 records in the layer it never completes. If I just change my GeoServer query to return just the first few hundred records it works which is surprising.

• user30184: I have no experience on your exact case but about something similar. Admin user "USER_A" who owns the table and index can create a view and add a line into sdo_metadata. Then USER_A grants SELECT rights for the GeoServer user "USER_B". GeoServer is reading the all_user_sdo_metadata and because of the grant USER_B can access the row even the view is owned by USER_A. It may be necessary to grant some more rights for USER_B before the access to spatial index succeeds. You can set the GeoServer logging level into GEOTOOLS_DEVEL and see what exact SQL queries are executed.
• user30184: Do you get good performance when you do not use a view but GeoServer reads directly the table as USER2 who is the owner of the table and index? Is the performance good if you create a view as USER2 and make GeoServer to read the view, still as USER2?
• Ian Turton: sounds like you need a GEOMETRY_COLUMNS table - docs.geoserver.org/latest/en/user/data/database/…
• zakariah1: I tried adding a GEOMETRY_COLUMNS table but had no luck with that. I created it with the subordinate user because the view is owned by the subordinate user. The layer is extremely fast when it is in a standalone table.
• zakariah1: Ian, If I went the GEOMETRY_COLUMNS way, Would I do the following? 1. Create the Table in the Schema of the Calling User. 2. Add a record that puts the name of the VIEW, not the table, into GEOMETRY_COLUMNS.

Answer 1

Oracle is notoriously slow, and due to it's proprietary nature gets little volunteer support from the GeoTools and GeoServer developers.

The manual page notes:

The Oracle data store by default looks at the MDSYS.USER_SDO* and MDSYS.ALL_SDO* views to determine the geometry type and native SRID of each geometry column. Those views are automatically populated with information about the geometry columns stored in tables that the current user owns (for the MDSYS.USER_SDO* views) or can otherwise access (for the MDSYS.ALL_SDO* views).

There are a few issues with this strategy:

• if the connection pool user cannot access the tables (because impersonation is used) the MDSYS views will be empty, making it impossible to determine both the geometry type and the native SRID

• the geometry type can be specified only while building the spatial indexes, as an index constraint. However such information is often not included when creating the indexes

• the views are populated dynamically based on the current user. If the database has thousands of tables and users the views can become very slow

You need to add a GEOMETRY_TABLE, as described here:

Starting with GeoServer 2.1.4 the administrator can address the above issues by manually creating a geometry metadata table describing each geometry column. Its presence is indicated via the Oracle datastore connection parameter named Geometry metadata table (which may be a simple table name or a schema-qualified one). The table has the following structure (the table name is flexible, just specify the one chosen in the data store connection parameter):

CREATE TABLE GEOMETRY_COLUMNS(
 F_TABLE_SCHEMA VARCHAR(30) NOT NULL,
 F_TABLE_NAME VARCHAR(30) NOT NULL,
 F_GEOMETRY_COLUMN VARCHAR(30) NOT NULL,
 COORD_DIMENSION INTEGER,
 SRID INTEGER NOT NULL,
 TYPE VARCHAR(30) NOT NULL,
 UNIQUE(F_TABLE_SCHEMA, F_TABLE_NAME, F_GEOMETRY_COLUMN),
 CHECK(TYPE IN ('POINT','LINE', 'POLYGON', 'COLLECTION', 'MULTIPOINT', 'MULTILINE', 'MULTIPOLYGON', 'GEOMETRY') ));

When the table is present the store first searches it for information about each geometry column to be classified, and falls back on the MDSYS views only if the table does not contain any information.

This table should be owned by the user you are using to connect to from GeoServer and will need to be populated with details of the tables and views that you are using.

If you continue to have problems then turning logging up to GEOTOOLS_DEVELOPER in the global settings will allow you to see the exact queries being sent to the database in your log file. This may help you see exactly the problem you are seeing.

If necessary you can submit patches to the GeoTools gt-jdbc-oracle extension or hire a commercial support team to fix issues for you. But it's probably cheaper and easier to switch to using PostGIS as your spatial database.

• zakariah1: It is a little disappointing that none of the Oracle experts have any experience to share on this.
• Ian Turton: may be consider spending some money - if you can afford to use Oracle you can hire experts