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.