Utilize function-based spatial index in SELECT list

Question

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)',
  --  🡅 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 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 list of a query, the index isn't being used. Instead, it's doing a full table scan...so the query is still slow when I select all rows (CTRL+ENTER in SQL Developer).

You might ask, "Why select all rows?" Answer: That's how mapping software often 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.

I've also tried an SQL hint, but no luck — I don't think the hint is being used:

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
)

Question:

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

I suppose I could have used DBMS_LOCK.sleep(1); to make my function slow, instead of converting to and from WKB. See the unrelated example in the “DETERMINISTIC Hint” section in this page: oracle-base.com/articles/12c/with-clause-enhancements-12cr1 — User1974
– User1974, Commented Jul 5, 2022 at 11:54

David Lapp · Accepted Answer · 2022-06-07 17:48:23Z

1

A spatial index is invoked only by the WHERE clause, not the SELECT list. A function in the SELECT list is invoked for every row returned by the WHERE clause, which in your case is SDO_ANYINTERACT( ) returning all rows.

answered Jun 7, 2022 at 17:48

David Lapp

812 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon Greener · Accepted Answer · 2022-06-03 22:36:30Z

1

You don't appear to be firing the index; just adding the function call as an attribute is insufficient

select
    slow_function(shape)
from
    lines

Should be....

select slow_function(shape)
  from lines
Where sdo_anyinteract(slow_function(shape),sdo_geometry(2003, 26917,null,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(1,2,3,4)) = 'TRUE'

Where 1,2,3,4 are the values of an optimized rectangle.

answered Jun 3, 2022 at 22:36

Simon Greener

4252 silver badges5 bronze badges

Comments

User1974 · Accepted Answer · 2022-06-04 03:06:24Z

I tried using sdo_anyinteract() in the WHERE clause, as @SimonGreener suggested.

Unfortunately, the query still seems to be doing a full table scan (in addition to using the index). I was hoping to only use the index.

select
    slow_function(shape) as shape
from
    lines  
where
    sdo_anyinteract(slow_function(shape),
        mdsys.sdo_geometry(2003, 26917, null, mdsys.sdo_elem_info_array(1, 1003, 1), mdsys.sdo_ordinate_array(573085.8702, 4771088.3813, 566461.6349, 4768833.3225, 570335.0629, 4757455.1278, 576959.2982, 4759710.1866, 573085.8702, 4771088.3813))
            ) = 'TRUE'

---------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |    TABLE ACCESS BY INDEX ROWID  | LINES     |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX (SEL: 0.000000 %)| LINES_IDX |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------
   2 - access("MDSYS"."SDO_ANYINTERACT"("INFRASTR"."SLOW_FUNCTION"("SHAPE"),"MDSYS"."
              SDO_GEOMETRY"(2003,26917,NULL,"MDSYS"."SDO_ELEM_INFO_ARRAY"(1,1003,1),"MDSYS"."SDO_OR
              DINATE_ARRAY"(573085.8702,4771088.3813,566461.6349,4768833.3225,570335.0629,4757455.1
              278,576959.2982,4759710.1866,573085.8702,4771088.3813)))='TRUE')

I played around with a SQL hint: /*+ INDEX (lines lines_idx) */. But that didn't seem to make a difference.

User1974 · Accepted Answer · 2022-06-09 16:04:37Z

The function takes the SDO_GEOMETRY lines and outputs a SDO_GEOEMTRY point.

A possible alternative might be:

Instead of returning/indexing a geometry column, maybe I could return/index X&Y numeric columns (using a regular non-spatial index). And then either:

A) Convert the XY columns to SDO_GEOMETRY after-the-fact in a query on-the-fly, or...

B) Use GIS software to display the XY data as points in a map. For example, in ArcGIS Pro, create an "XY Event Layer".

That technique seemed to work ok here: Improve performance of startpoint query (ST_GEOMETRY). I was able to utilize the function-based index (non-spatial) in a SELECT clause — making my query significantly faster.

Of course, that technique would work best for points — since converting XYs after-the-fact to point geometries is easy/performant/practical. Whereas converting lines or polygons (maybe from WKT?) to geometries after-the-fact likely wouldn't make much sense. Even if that were possible, it would likely be too slow, and defeat the purpose of precomputing the data in the function-based index in the first place.

Collectives™ on Stack Overflow

Utilize function-based spatial index in SELECT list

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related