Support function-based indexes on [Abstract datatypes] (edited)

User_1871

It would be helpful if FBIs were supported on object datatypes [Abstract datatypes] (edited).

Currently, that doesn’t seem to be possible:

create index lines_fbi_sdo_idx on lines (id, startpoint_sdo_geom(shape));

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.

Solomon Yakobson

Post what are you trying to create.

SY.

User_1871

Thanks @Solomon Yakobson.

I want to pre-compute a calculated SDO_GEOMETRY object column using a non-spatial FBI.

1. I want to invoke the FBI in the SELECT clause/attribute list of a query (not invoke it in the WHERE clause via a spatial operation).
   • That's not possible with a function-based spatial index: https://community.oracle.com/tech/apps-infra/discussion/comment/16838231/#Comment_16838231. Whereas it is possible in a non-spatial FBI.
   • And in the explain plan, I only want to utilize the index; I don't want to do a full table scan.
2. I want to include an ID column in the FBI.
   • That's not possible with a function-based spatial index either (composite indexes aren't allowed). Whereas it is possible with non-spatial FBIs: https://stackoverflow.com/a/72269515/5576771

-------------------------------------------------------

First, I'll demonstrate something that's close to what I want (works without errors), but isn't exactly what I'm looking for:

Create a non-spatial FBI that precomputes an ID column and numeric X & Y columns (instead of a SDO_GEOMETRY column).

Steps:

Create test data:

create table lines (id number(38,0), shape sdo_geometry);
begin
  insert into lines (id, shape) values (1, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(574360, 4767080, 574200, 4766980)));
  insert into lines (id, shape) values (2, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(573650, 4769050, 573580, 4768870)));
  insert into lines (id, shape) values (3, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(574290, 4767090, 574200, 4767070)));
  insert into lines (id, shape) values (4, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(571430, 4768160, 571260, 4768040)));
  ...
end;
/

Full test data here: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=0c43a5cc1cf37a1a8ac74c676f013c1d

Create functions to get the X & Y coordinates as numbers:

create function startpoint_x(shape in sdo_geometry) return number 
deterministic is
begin
    return shape.sdo_ordinates(1);
end; 
   
create function startpoint_y(shape in sdo_geometry) return number 
deterministic is
begin
    return shape.sdo_ordinates(2);
end;  

select
    id,
    startpoint_x(shape) as startpoint_x,
    startpoint_y(shape) as startpoint_y
from
    lines;

        ID STARTPOINT_X STARTPOINT_Y
---------- ------------ ------------
         1       574360      4767080
         2       573650      4769050
         3       574290      4767090
...

[full table scan]

Create a non-spatial FBI that stores the ID, X (number), and Y (number) in a composite index:

create index lines_fbi_idx on lines (id, startpoint_x(shape), startpoint_y(shape));

Selecting the data invokes the index and avoids a full-table scan (fast/good):

select
  id,
  startpoint_x(shape) as startpoint_x,
  startpoint_y(shape) as startpoint_y
from
  lines
where --https://stackoverflow.com/a/59581129/5576771
  id is not null
  and startpoint_x(shape) is not null
  and startpoint_y(shape) is not null;

--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     3 |   117 |     4   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| LINES_FBI_IDX |     3 |   117 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------
   1 - filter("ID" IS NOT NULL AND "INFRASTR"."STARTPOINT_X"("SHAPE") IS NOT 
              NULL AND "INFRASTR"."STARTPOINT_Y"("SHAPE") IS NOT NULL)
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

That works as expected.

Note: That's is just a bare-bones example for the purposes of this post. In reality, the custom functions would have more complicated logic and be a lot slower — hence the need for precomputing in an index.

-------------------------------------------------------

However, as mentioned, that's not actually what I want. I want to index an ID column and a SDO_GEOMETRY column (object type). It would look like this:

create function startpoint_sdo_geom(shape in sdo_geometry) return sdo_geometry 
deterministic is
begin
  return sdo_lrs.geom_segment_start_pt(shape);
end; 

select
    id,
    startpoint_sdo_geom(shape) as startpoint_sdo_geom
from
    lines

ID  STARTPOINT_SDO_GEOM 
--  -------------------
 1  [MDSYS.SDO_GEOMETRY]
 2  [MDSYS.SDO_GEOMETRY]
 3  [MDSYS.SDO_GEOMETRY]
...

[full table scan]

I'll try to create a function-based index that stores the ID and the calculated SDO_GEOMETRY in a composite index:

create index lines_fbi_sdo_idx on lines (id, startpoint_sdo_geom(shape));

Error:

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.

-------------------------------------------------------

So, as mentioned, it's not currently possible to do what I want: Create a function-based index on an object datatype.

See points #1 & #2 at the top of this post regarding why that won't work when using a true function-based spatial index either.

Does that make sense? Thanks for your interest.

Solomon Yakobson

Well, what is index - we have index key value and index gives as a pointer to row(s) with that key values. So key values we supply need to be compared with key values in the index. Therefore we need to know how to compare values of key data type. So if object type defines MAP method as deterministic function (and it is indeed deterministic) we can use that MAP method to index UDT. Or, if Oracle will ever introduce indexes with what in SQL Server is called included columns then adding UDT column as index included column wouldn't require UDT compare method and would be possible to support (assuming index key doesn't exceed key max length).

SY.

User_1871

@Solomon Yakobson Thanks. Although I have to admit, I don't think I totally follow everything you said. :)

Will give it some thought and see if I can figure it out.

Solomon Yakobson

Well, beat up classic example - all you know is book title. Luckily there is library catalog where books are sorted alphabetically by title. That is index. Person who created that catalog knows that book title starting with letter B goes in catalog after book title starting with letter A and if two titles start with same letter then we sort by second letter, and so on. Anyway we need to sort (ergo compare) book titles to create that catalog (index). Same is Oracle index - we must compare key expressions to build it. With built-in Oracle datatypes comparison rules are also built-in but with user defined types Oracle has no clue how to compare two objects. So object type can have MAP method that maps object to some value (think of it as object weight) and if such value is of built-in datatype and method is deterministic and is declared as such then we can build index on object mapped value. For example:

DROP TABLE LINE_SEGMENTS PURGE
/
DROP TYPE LINE_SEGMENT_OBJ_TYPE
/
CREATE OR REPLACE
  TYPE LINE_SEGMENT_OBJ_TYPE
    AS OBJECT(
              X1 NUMBER,
              Y1 NUMBER,
              X2 NUMBER,
              Y2 NUMBER,
              MAP MEMBER FUNCTION LINE_SEGMENT_LENGTH
                RETURN NUMBER
                DETERMINISTIC
             )
/
CREATE OR REPLACE
  TYPE BODY LINE_SEGMENT_OBJ_TYPE
    IS
      MAP MEMBER FUNCTION LINE_SEGMENT_LENGTH
        RETURN NUMBER
        DETERMINISTIC
        IS
        BEGIN
            RETURN SQRT(POWER(X1 - X2,2) + POWER(Y1 - Y2,2));
        END;
END;
/
CREATE TABLE LINE_SEGMENTS(
	                   ID NUMBER,
	                   LINE_SEGMENT LINE_SEGMENT_OBJ_TYPE
                         )
/
INSERT
  INTO LINE_SEGMENTS
  VALUES(
         1,
         LINE_SEGMENT_OBJ_TYPE(3,7,6,11)
        )
/
INSERT
  INTO LINE_SEGMENTS
  VALUES(
         2,
         LINE_SEGMENT_OBJ_TYPE(5,15,13,21)
        )
/
CREATE INDEX LINE_SEGMENTS_IDX1
  ON LINE_SEGMENTS(
                   LINE_SEGMENT_OBJ_TYPE.LINE_SEGMENT_LENGTH(LINE_SEGMENT)
                  )
/
SET LINESIZE 132
EXPLAIN PLAN FOR
SELECT  *
  FROM  LINE_SEGMENTS
  WHERE LINE_SEGMENT_OBJ_TYPE.LINE_SEGMENT_LENGTH(LINE_SEGMENT) = 5 -- line segments legth is 5
/
SELECT  *
  FROM  TABLE(DBMS_XPLAN.DISPLAY)
/

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 3985448576

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |     1 |    80 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LINE_SEGMENTS      |     1 |    80 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | LINE_SEGMENTS_IDX1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LINE_SEGMENT_OBJ_TYPE"."LINE_SEGMENT_LENGTH"("LINE_SEGMENT")=5)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

18 rows selected.

SQL>

As you can see Oracle used FBI on map method.

SY.

User_1871

@Solomon Yakobson Thanks!

I tried taking your type:

CREATE OR REPLACE
  TYPE LINE_SEGMENT_OBJ_TYPE
    AS OBJECT(
              X1 NUMBER,
              Y1 NUMBER,
              X2 NUMBER,
              Y2 NUMBER,
              MAP MEMBER FUNCTION LINE_SEGMENT_LENGTH
                RETURN NUMBER
                DETERMINISTIC
             )

…and changed RETURN NUMBER to RETURN SDO_GEOMETRY:

CREATE OR REPLACE
  TYPE LINE_SEGMENT_OBJ_TYPE
    AS OBJECT(
              X1 NUMBER,
              Y1 NUMBER,
              X2 NUMBER,
              Y2 NUMBER,
              MAP MEMBER FUNCTION LINE_SEGMENT_LENGTH
                RETURN SDO_GEOMETRY
                DETERMINISTIC
             )

ButI got this error:

Errors: TYPE LINE_SEGMENT_OBJ_TYPE

Line/Col: 7/35 PLS-00522: MAP methods must return a scalar type.

Environment: Oracle Live SQL (19c)

Thoughts?

Solomon Yakobson

As I mentioned:

So object type can have MAP method that maps object to some value (think of it as object weight) and if such value is of built-in datatype and method is deterministic and is declared as such then we can build index on object mapped value.

Oracle has no clue how to compare two SDO_GEOMETRY type objects. How do you compare say point do decagon or even two same dimensional objects, e.g. point to point? Yoy need to lokk into Oracle provided s[atial indexes or build your own indextype.

SY.

Solomon Yakobson

Built-in types are listed in Oracle Built-in Data Types. Spatial types are Oracle-Supplied Types.

SY.

User_1871

For our notes, function-based indexes aren't supported for VARRAYs either:

Convert SDO_ORDINATE_ARRAY to built-in VARRAY (to create function-based index)

Solomon Yakobson

Could you start with seriously reading documentation which it looks like you didn't do even when you were given links to documentation. Oracle supports scalar data types and composite data types. Scalar types are built-in SQL or PL/SQL types. Composite types are user defined object or collection types (user can be Oracle supplied user like MDSYS with oracle supplied spatial types). Object type has attributes that again can be of scalar or composite type. Collections have elements that again can be of scalar or composite type. VARRAY is type of collection, not a built-in type. Built-in types are scalar data types that don't need declaration/definition. We don't declare/define what VARCHAR2 or FLOAT is. We don't define their comparison rules... VARRAY has no predefined comparison rules. How do you compare two varrays? Which of two varrays is greater? And same applies to nested tables an object types. So unless we can compare two composites we simply can't index them. And it is not just Oracle - it is common sense. And if with object type we can provide MAP method that returns a value to be used when comparing two objects of this type and thus create FBI on object MAP, collections have no methods so we can't have even that.

SY.