在SELECT子句中使用基于函数的空间索引

我有一个名为LINES的Oracle18c表,其中有1000行。可以在此处找到该表的DDL: db<>fiddle 。

数据如下所示:

create table lines (shape sdo_geometry);
    insert into lines (shape) values (sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(574360, 4767080, 574200, 4766980)));
    insert into lines (shape) values (sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(573650, 4769050, 573580, 4768870)));
    insert into lines (shape) values (sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(574290, 4767090, 574200, 4767070)));
    insert into lines (shape) values (sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(571430, 4768160, 571260, 4768040)));
    ...

出于测试目的,我创建了一个故意放慢速度的函数。函数采用SDO_GEOMETRY行并输出SDO_GEOEMTRY 指向 。

create or replace function slow_function(shape in sdo_geometry) return sdo_geometry  
deterministic is
begin
    return 
    --Deliberately make the function slow for testing purposes...
    --    ...convert from SDO_GEOMETRY to JSON and back, several times, for no reason.
    sdo_util.from_json(sdo_util.to_json(sdo_util.from_json(sdo_util.to_json(sdo_util.from_json(sdo_util.to_json(sdo_util.from_json(sdo_util.to_json(sdo_util.from_json(sdo_util.to_json(
        sdo_lrs.geom_segment_start_pt(shape)
    ))))))))));
end;

作为一个实验,我想创建一个 function-based spatial index ,作为预计算慢函数的结果的一种方式。

步骤:

在USER_SDO_GEOM_METADATA中创建条目:

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'lines', 
  'infrastr.slow_function(shape)',
  --  🡅 Important: Include the function owner.
  sdo_dim_array (
    sdo_dim_element('X',  567471.222,  575329.362, 0.5),  --note to self: these coordinates are wrong.
    sdo_dim_element('Y', 4757654.961, 4769799.360, 0.5)
  ),
 26917
);
commit;

创建基于函数的空间索引:

create index lines_idx on lines (slow_function(shape)) indextype is mdsys.spatial_index_v2;

问题:

当我在查询的SELECT列表中使用函数时,索引没有被使用。相反,它正在进行全表扫描。。。因此,当我选择所有行时(SQL Developer中的CTRL+ENTER),查询仍然很慢。

您可能会问:“为什么选择 全部的 行?“答案:地图软件通常就是这样工作的……你可以同时显示地图中的所有(或大部分)点。

explain plan for

select
    slow_function(shape)
from
    lines

select * from table(dbms_xplan.display);

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    34 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| LINES |     1 |    34 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

同样,在我的地图软件(ArcGIS Desktop 10.7.1)中,地图也没有使用索引。我看得出来,因为在地图上画点很慢。

我知道可以创建一个视图,然后在USER_SDO_GEOM_METADATA中注册该视图(除了注册索引)。并在地图中使用该视图。我试过了,但地图软件仍然不使用索引。

我也尝试过SQL提示,但运气不好,我认为没有使用该提示:

create or replace view lines_vw as (
select
    /*+ INDEX (lines lines_idx) */
    cast(rownum as number(38,0)) as objectid, --the mapping software needs a unique ID column
    slow_function(shape) as shape
from
    lines  
where
    slow_function(shape) is not null --https://stackoverflow.com/a/59581129/5576771
)  

问题:

如何在查询中使用SELECT列表中基于函数的空间索引?