SQL Query Performance Issues

[JoshBillings]

We are setting up a new Oracle enterprise geodatabase. This set up involves an external procedure that runs on a weekly basis to update data on a table.

Part of this external procedure has a SQL query (below) to select a subset of data based on a spatial relationship using the ST_Intersects function. In our current environment, this statement takes less than a minute to run. In our new environment, we are encountering performance issues on the "AND t1.field2 != t2.field3" portion of this statement - to the tune of it never completing. If we take this statement out of the query, the query runs fine, so we have been able to narrow this performance issue to this particular statement.

Both tables (Table1 and Table2 in the query below) have spatial indexes and attribute indexes associated with them.

Any ideas on what we could try to get this to successfully run?

SELECT t1.field1, t1.field2, t2.field3 FROM Table1 t1, Table2 t2  
WHERE sde.st_intersects(t2.shape, sde.st_pointonsurface((t1.shape))) = 1
AND t1.field2 != t2.field3
AND t2.field3 != 'Attribute';

Thanks - Josh

[MarceloMarques]

@JoshBillings - compare the Query Execution Plan in the old server with the new server to determine what might be causing the problem.

Make sure the new server has the esri st_geometry library properly installed and check if it is working.

Configure extproc to access ST_Geometry in Oracle—ArcGIS Pro | Documentation

You need to use the version of the esri st_geometry library that matches the version of the geodatabase arcsde repository.

[VinceAngelo]

Placing join tables in the FROM list hasn't been best practice since Oracle 8.
I strongly urge you to use JOIN syntax.  And "!=" best written "<>". 

The optimizer might prefer placing the point in the first argument, but if Table2
is indexed on t2.field3, and that has higher selectivity, you might want to specify
the non-spatial constraints first (though NOT is usually an awful JOIN constraint).

- V

[MarceloMarques]

@JoshBillings 

Oracle INNER JOIN Demonstrated with Practical Examples (oracletutorial.com)

Oracle LEFT JOIN By Practical Examples (oracletutorial.com)

RIGHT OUTER JOIN in Oracle By Practical Examples (oracletutorial.com)

[Bud]

Edit: Never mind. The syntax is the same as your query.

As a last resort, you could try using the Select Layer by Location — Intersect (DBMS) geoprocessing tool which creates a spatial SQL query in a database view. Maybe the syntax or performance would be different. You could steal the SQL from the view to use in your query.

Related:

1. Select Layer by Location — Intersect (DBMS) — Existing Selections
2. Intended use case for Select Layer by Location — Intersect (DBMS)?
3. Select Layer By Location — Indicate that Intersect (DBMS) creates a database view
4. Select Layer by Location — Intersect (DBMS) — User-defined output database view name
5. Select Layer by Location — Intersect (DBMS) — Create a query layer instead of a database view
6. Select Layer by Location — Intersect (DBMS) — Show SQL query definition in GP tool details