Home > database >  Utilize function-based spatial index in SELECT clause
Utilize function-based spatial index in SELECT clause

Time:06-04

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 intentionally 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 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)',
  --             
  • Related