Improve performance of ST_PointN() function (SDE.ST_GEOMETRY in Oracle)

[Bud]

When troubleshooting performance issues with SDE.ST_GEOMETRY queries in Oracle 12c/18c, I’ve found that the ST_PointN function is very slow when used in bulk (10x slower than many other ST_GEOMETRY functions).

The ST_PointN function doesn’t seem do anything special. It’s not doing complex spatial analysis or anything like that; it just gets a specific point from the SHAPE blob. So I wonder if there might be an issue with how it’s designed.

For example, changing the the function so that it’s DETERMINISTIC might make it faster.

Could Esri investigate/improve the performance of that function?

[Bud]

For example, a query that generates rows for each vertex (3000 features):

1    SELECT   a.ROAD_ID
2             ,b.NUMBERS VERTEX_INDEX
3             ,a.SDE.ST_X(SDE.ST_PointN(a.SHAPE, b.NUMBERS)) AS X
4             ,a.SDE.ST_Y(SDE.ST_PointN(a.SHAPE, b.NUMBERS)) AS Y
5    FROM     ROAD a
6             CROSS JOIN NUMBERS b
7    WHERE    b.NUMBERS <= SDE.ST_NumPoints(a.SHAPE)

----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                      |  5996 |  1545K|       |   262   (1)| 00:00:01 |
|   1 |  MERGE JOIN         |                      |  5996 |  1545K|       |   262   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN   | R23715_SDE_ROWID_UK  |    30 |    90 |       |     1   (0)| 00:00:01 |
|*  3 |   SORT JOIN         |                      |  3997 |  1018K|  2392K|   261   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| ROAD                 |  3997 |  1018K|       |    34   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
"   3 - access(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))"
"       filter(""B"".""NUMBERS""<=""SDE"".""ST_NumPoints""(""A"".""SHAPE""))"

That query takes 30 seconds to run, which is far too long. In comparison, pretty much any other query on the FC runs instantly.

Testing/benchmarking:

I created two tables (both contain 10,000 rows): ROADS_BM and ROADS_STARTPOINT_BM. I ran simple queries on the tables using each of the functions that are involved. Here are the results:

               +-----------+------------------+---------------------------------------------------------------------------+
               | TIME(sec) | RETURN TYPE      | QUERY                                                                     |
+--------------+-----------+------------------+---------------------------------------------------------------------------+
| ST_X         | < 0.5     | Double precision | SELECT ROAD_ID FROM (                                                     |
|              |           | (Number)         | SELECT ROAD_ID, SDE.ST_X(SHAPE) AS X FROM ENG.ROADS_STARTPOINT_BM         |
|              |           |                  | ) WHERE X IS NOT NULL ORDER BY ROAD_ID                                    |
+--------------+-----------+------------------+---------------------------------------------------------------------------+
| ST_Y         | < 0.5     | Double precision | SELECT ROAD_ID FROM (                                                     |
|              |           | (Number)         | SELECT ROAD_ID, SDE.ST_Y(SHAPE) AS Y FROM ENG.ROADS_STARTPOINT_BM         |
|              |           |                  | ) WHERE Y IS NOT NULL ORDER BY ROAD_ID                                    |
+--------------+-----------+------------------+---------------------------------------------------------------------------+
| ST_NumPoints | < 0.5     | Integer          | SELECT ROAD_ID FROM (                                                     |
|              |           |                  | SELECT ROAD_ID, SDE.ST_NumPoints(SHAPE) AS NUM_POINTS FROM ENG.ROADS_BM   |
|              |           |                  | ) WHERE NUM_POINTS IS NOT NULL ORDER BY ROAD_ID                           |
+--------------+-----------+------------------+---------------------------------------------------------------------------+
| ST_PointN*   | **9.5**   | ST_POINT         | SELECT ROAD_ID FROM (                                                     |
|              |           | (ST_GEOMETRY     | SELECT ROAD_ID, SDE.ST_PointN(SHAPE,1) AS ST_POINT FROM ENG.ROADS_BM      |
|              |           | subclass)        | ) WHERE ST_POINT IS NOT NULL ORDER BY ROAD_ID                             |
+--------------+-----------+------------------+---------------------------------------------------------------------------+

Function documentation: ST_X, ST_Y, ST_NumPoints, ST_PointN

The result? ST_PointN is the problem. Its 9.5 second response time is slow compared to the other functions. 

So I think that proves that the performance of ST_PointN needs to be improved.