Function-based index on function returning number from abstract datatype

Question

Using the following data:

create table my_table (shape sdo_geometry);

begin
insert into my_table (shape) values (sdo_geometry('linestring(100 200, 300 400, 500 600)'));
insert into my_table (shape) values (sdo_geometry('linestring(700 800, 900 1000)'));
end;
/

I can select the geometry's startpoint X coordinate using this expression:

select
    (sdo_util.get_coordinate(shape,1)).sdo_point.x as startpoint_x
from
    my_table

 STARTPOINT_X 
 ------------
          100 
          700 

---

I want to create a function-based index using that same expression — without creating a custom function:

create index idx1 on my_table ((mdsys.sdo_util.get_coordinate(shape,1)).sdo_point.x);

But I get an error:

Error starting at line : 6 in command -
create index idx1 on my_table ((mdsys.sdo_util.get_coordinate(shape,1)).sdo_point.x)
Error report -
ORA-02327: cannot create index on expression with datatype ADT
02327. 00000 -  "cannot create index on expression with datatype %s"
*Cause:    An attempt was made to create an index on a non-indexable
           expression.
*Action:   Change the column datatype or do not create the index on an
           expression whose datatype is one of  VARRAY, nested table, object,
           LOB, or  REF.

As that error states, we can't create indexes where the expression is an abstract datatype (ADT) such as SDO_GEOEMTRY.

But the expression's output datatype isn't abstract. It's a number. So I don't understand why I'm getting that error.

---

I am able to workaround the issue by creating a custom function:

create or replace function startpoint_x(shape sdo_geometry) return number 
deterministic is
begin
    return sdo_util.get_coordinate(shape,1).sdo_point.x;
end; 
/

And create an index using the function:

create index idx1 on my_table 
   (sys_context('USERENV', 'SESSION_USER')||'.'||startpoint_x(shape));

That works as expected. But I would prefer to avoid creating a custom function, since it's just one more thing to manage and seems unecessary. Also, I'd like to understand the root cause behind why the custom function works, but using the expression in the index doesn't work.

db<>fiddle

---

Is there a way to create a function-based index on MDSYS.SDO_UTIL.GET_COORDINATE(SHAPE,1)).SDO_POINT.X without creating a custom function?

Answer 1

You need to restrict an object type returned by a function to a specific (sub)type by using treat function:

create index idx1
on my_table (treat(
  mdsys.sdo_util.get_coordinate(shape,1)
  as sdo_geometry
).sdo_point.x
);

✓

create index idx12
on my_table(shape.sdo_point.x);

✓

db<>fiddle here