Oracle 18c空间查询优化：如何提升点面相交查询性能？

背景信息 #

我有两个Oracle 18c表：

• ply — 多边形表，共4970行
• pnt — 点表，共3500行
• 图形数据存储在SDO_GEOMETRY类型列中
• 补充：点表中存在大量空间重复的点，暂时未处理

（注：原始数据有在线示例，此处省略外链；原始有数据分布截图，此处省略外链）

当前查询逻辑 #

我编写了以下查询，用于筛选与至少一个点相交的多边形，确保每个多边形仅返回一行：

SELECT objectid 
FROM (SELECT ply.objectid,
             row_number() over(partition by ply.objectid order by null) rn
      FROM ply                                       -- ORDER BY NULL 为有意设置
CROSS JOIN pnt                                       -- 匹配任意点即可，不在乎具体点
     WHERE sdo_anyinteract(ply.shape, pnt.shape) = 'TRUE'
     )
WHERE rn = 1

查询返回1443个唯一的多边形OBJECTID。

执行计划 #

--------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |           |  1457 | 37882 |       | 12851   (1)| 00:00:01 |
|*  1 |  VIEW                              |           |  1457 | 37882 |       | 12851   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK          |           |  1457 |    10M|    11M| 12851   (1)| 00:00:01 |
|   3 |    NESTED LOOPS                    |           |  1457 |    10M|       | 10522   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL              | PNT       |  3500 |    12M|       |    11   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID    | PLY       |     1 |  3848 |       | 10522   (1)| 00:00:01 |
|*  6 |      DOMAIN INDEX (SEL: 0.010000 %)| PLY_SHAPE |       |       |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("RN"=1)
  2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PLY"."OBJECTID" ORDER BY  NULL )<=1)
  6 - access("MDSYS"."SDO_ANYINTERACT"("PLY"."SHAPE","PNT"."SHAPE")='TRUE')
Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

性能问题 #

• 本地Oracle 18c执行耗时约5秒，在线环境耗时超5秒
• 表为新建，统计信息已更新
• 执行计划显示已使用PLY_SHAPE空间域索引，但性能仍不理想
• 对比：桌面制图软件（如ArcGIS Pro）在内存中执行相同操作仅需0.5秒

提问 #

能否提升该查询的性能？作为新手，我惊讶于普通办公电脑上的制图软件性能竟优于企业级Oracle数据库。

优化建议 #

1. 去重点表重复点
   点表存在大量空间重复点，先去重可大幅减少JOIN次数，比如用CTE存储去重后的点：

WITH unique_pnts AS (
    SELECT DISTINCT shape FROM pnt
)
SELECT objectid
FROM ply
WHERE EXISTS (
    SELECT 1 FROM unique_pnts
    WHERE sdo_anyinteract(ply.shape, unique_pnts.shape) = 'TRUE'
)

2. 改用EXISTS半连接替代CROSS JOIN+ROW_NUMBER
   EXISTS是半连接逻辑，找到第一个匹配点后立即停止当前多边形的匹配，避免生成大量中间结果，执行效率远高于先全量匹配再去重：

SELECT objectid
FROM ply
WHERE EXISTS (
    SELECT 1
    FROM pnt
    WHERE sdo_anyinteract(ply.shape, pnt.shape) = 'TRUE'
)

3. 优化空间索引统计信息
   重新收集空间索引的统计信息，确保优化器能生成更优的执行计划：

EXEC DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => '你的用户名', INDNAME => 'PLY_SHAPE');

4. 为点表创建空间索引
   当前执行计划全扫点表，给点表创建空间索引后，优化器可能选择更高效的连接路径（如先过滤点再匹配多边形）：

CREATE INDEX pnt_shape ON pnt(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

5. 启用并行查询
   利用服务器多核CPU提升空间查询速度，并行度根据CPU核心数调整：

SELECT /*+ PARALLEL(ply 4) PARALLEL(pnt 4) */ objectid
FROM ply
WHERE EXISTS (
    SELECT 1
    FROM pnt
    WHERE sdo_anyinteract(ply.shape, pnt.shape) = 'TRUE'
)

内容的提问来源于stack exchange，提问作者User1974