Function-based index on object type

115 Views Asked by At

I have a column in an Oracle 18c table that is an object type (user-defined type): Esri's ST_GEOMETRY spatial type.

I want to create a function-based index on that column:

create or replace function my_owner.test_func(shape in sde.st_geometry) return sde.st_geometry
deterministic is
begin
    return sde.st_geometry_operators.st_startpoint_f(sde.st_geometry_operators.st_geometryn_f(shape,1)); 
end;

create index my_owner.test_idx on my_owner.active_transportation (my_owner.test_func(shape));

I can create the function without issue. But when I try to create the index, it fails:

Error starting at line : 10 in command -
create index my_owner.test_idx on my_owner.active_transportation (my_owner.test_func(shape))
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.

That error seems pretty definitive: Oracle can't create a function-based index on objects.

With that said, are there any workarounds for this scenario?

For example, could I cast the object as something else in the function, create the index on the casting, and then un-cast it in a query?

  • For what it's worth, there is a function that can convert the object to a blob. Unfortunately though, blobs aren't supported in function-based indexes either. I get the same error.
0

There are 0 best solutions below