I have an Oracle 18c table called LINES with 1000 rows. The DDL for the table can be found here: db<>fiddle.

The data looks like this:

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)));
    ...

I’ve created a function that’s deliberately slow — for testing purposes. The function takes the SDO_GEOMETRY lines and outputs a SDO_GEOEMTRY point.

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 real 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;

As an experiment, I want to create a function-based spatial index, as a way to pre-compute the result of the slow function.

---

Steps:

Create an entry in 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),
    sdo_dim_element('Y', 4757654.961, 4769799.360, 0.5)
  ),
 26917
);
commit;

Create a function-based spatial index:

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

---

Problem:

When I use the function in the SELECT clause of a query, the index isn’t being used. Instead, it’s doing a full table scan. And the query is still slow when I select all rows (CTRL+ENTER in SQL Developer).

Why would I want to select all rows? Because that’s how mapping
software works…you display all (or most) of the points in the map —
all at once.

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 |
---------------------------------------------------------------------------

Likewise, in my mapping software (ArcGIS Desktop 10.7.1), the map doesn’t utilize the index either. I can tell, because the points are slow to draw in the map.

I’m aware that it’s possible to create a view, and then register that view in USER_SDO_GEOM_METADATA (in addition to registering the index). And use that view in the map. I’ve tried that, but the mapping software still doesn’t use the index.

---

Question:

How can I utilize the function-based spatial index in the SELECT clause in a query?

---

Note:

I suspect it won’t be possible to test this in an online environment like db<>fiddle or Oracle Live SQL.

Reason: In the USER_SDO_GEOM_METADATA entry, we need to specify the function’s owner. But in online environments, the owner/schema name is temporary/ever-changing. So it wouldn’t make sense to enter the temporary schema name…making it impossible to test in an online environment. More info about the “include function’s owner” requirement here.