Forum Stats

  • 3,874,885 Users
  • 2,266,782 Discussions
  • 7,911,993 Comments

Discussions

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

User_1871
User_1871 Member Posts: 247 Red Ribbon

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.


User_1871
1 votes

Active · Last Updated

«1

Comments

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,949 Red Diamond

    Post what are you trying to create.

    SY.

  • User_1871
    User_1871 Member Posts: 247 Red Ribbon
    edited Jun 17, 2022 4:31PM

    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).
    2. I want to include an ID column in the FBI.

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

    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
    Solomon Yakobson Member Posts: 19,949 Red Diamond

    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
    User_1871 Member Posts: 247 Red Ribbon

    @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
    Solomon Yakobson Member Posts: 19,949 Red Diamond
    edited Jun 17, 2022 12:46PM

    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
  • User_1871
    User_1871 Member Posts: 247 Red Ribbon

    @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
    Solomon Yakobson Member Posts: 19,949 Red Diamond
    edited Jun 17, 2022 9:07PM

    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.

    User_1871
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,949 Red Diamond

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

    SY.

    User_1871
  • User_1871
    User_1871 Member Posts: 247 Red Ribbon

    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
    Solomon Yakobson Member Posts: 19,949 Red Diamond

    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.