ORA-30554 errors on Function-Based Spatial Indexes after Spatial Bundle Patch

INTRO : After Spatial Bundle Patch you may encounter ORA-30554 errors on functional indexes using spatial operators.

We have a Geographic Information System (GIS) application in which data is stored with latitude and longitude in separate columns. However, the geographic data is not stored directly in the SDO_GEOMETRY format. Another application accesses this data through views, where the latitude and longitude columns are dynamically cast to SDO_GEOMETRY in a computed column.

Since the base table does not store data directly in the SDO_GEOMETRY format, we created a functional index on the base table to leverage spatial indexing when using spatial operators. This solution has been working perfectly.

This method is effective only when all rows have valid values in the LONGITUDE and LATITUDE columns. If some rows have these columns set to NULL, you’ll need to implement an explicit function to manage those cases and return a NULL geometry. Providing NULL values for both X and Y coordinates to SDO_GEOMETRY leads to invalid geometries, which will cause the index creation to fail.

This solution is thoroughly explained step-by-step in the link provided: Create an SDO_GEOMETRY view from a non-spatial table. The solution covers how to convert dynamically latitude and longitude values into SDO_GEOMETRY, along with workarounds for cases where the coordinates may be missing (NULL).

Convert Latitude and Longitude to SDO_GEOMETRY on the fly

I’ve provided a demo showcasing this solution in action. The demo demonstrates how to convert latitude and longitude columns into SDO_GEOMETRY format, handle NULL values, and create functional indexes to optimize spatial queries.

-- Create sample table

SQL> CREATE TABLE cisg.t_cis_devices (
  ID number primary key,
  NAME varchar2(30),
  LONGITUDE number,
  LATITUDE number
);

-- Run with owner of the table (cisg user)
-- Insert metadata of the virtual column before creating a spatial index

SQL> INSERT INTO user_SDO_GEOM_METADATA (
  TABLE_NAME, COLUMN_NAME, DIMINFO, 
  SRID
) 
VALUES 
  (
    't_cis_devices', 
'MDSYS.SDO_GEOMETRY(2001,4326,MDSYS.SDO_POINT_TYPE(NVL(LONGITUDE,0),NVL(LATITUDE,0),NULL),NULL,NULL)', 
    mdsys.sdo_dim_array (
      mdsys.sdo_dim_element ('X', -180, 180, 0.05), 
      mdsys.sdo_dim_element ('Y', -90, 90, 0.05)
    ), 
    4326
  );
COMMIT;

-- Functional index -- actually a virtual column created
SQL> CREATE INDEX cisg.IDX_SPTL_CIS_DEV ON cisg.t_cis_devices (
  "MDSYS"."SDO_GEOMETRY" (
    2001, 
    4326, 
    "MDSYS"."SDO_POINT_TYPE" ((NVL("LONGITUDE",0),NVL("LATITUDE",0), NULL), 
    NULL, 
    NULL
  )
) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('tablespace=USERS');

-- Grant select on base table to another schema busint
SQL> GRANT SELECT ON cisg.t_cis_devices to busint;

-- View is created on another schema
SQL> CREATE VIEW busint.v_cis_devices 
select id, name,
MDSYS.SDO_GEOMETRY(2001,4326,MDSYS.SDO_POINT_TYPE(NVL("LONGITUDE",0),NVL("LATITUDE",0),NULL),NULL,NULL) as GEO
from cisg.t_cis_devices;

-- Test query to check whether spatial index utilized or not
SQL> SELECT 
  * 
FROM 
  busint.v_cis_devices 
WHERE 
  SDO_EQUAL (
    GEO, 
    MDSYS.SDO_GEOMETRY (
      2001, 
      4326, 
      MDSYS.SDO_POINT_TYPE (
        35.1157,
        37.4061, 
        NULL
      ), 
      NULL, 
      NULL
    )
  ) = 'TRUE';

On my example, I have created spatial indexes using the MDSYS.SPATIAL_INDEX_V2 type. For more information on why I chose this spatial index type, you can refer to my blog post titled ‘SPATIAL_VECTOR_ACCELERATION Parameter Speeds Up Spatial Operations.’

As I mentioned in my previous post, ‘Oracle Database Patching Tips,’ we apply the Spatial Bundle Patch without hesitation, as we are using it effectively. We don’t want to miss any updates related to it. The document titled What Is the Latest Spatial Patch Bundle for 12c and Higher Databases? (Doc ID 2514624.1) is used to identify the latest spatial bundle patch.

ORA-30554 function-based index string.string is disabled

After applying the 19.24 Database Release Update along with the Spatial Patch Bundle 37185000 and running datapatch, we encountered issues on the related application utilizing spatial indexes started failing immediately with ORA-30554 errors.

[oracle@blt01 ~]$ oerr ora 30554
30554, 00000, "function-based index %s.%s is disabled"
// *Cause:  An attempt was made to access a function-based index that has
//          been marked disabled because the function on which the index
//          depends has been changed.
// *Action: Perform one of the following actions:
//          -- drop the specified index using the DROP INDEX command
//          -- rebuild the specified index using the ALTER INDEX REBUILD command
//          -- enable the specified index using the ALTER INDEX ENABLE command
//          -- make the specified index usable using the ALTER INDEX UNUSABLE
//             command

At the datapatch stage, spatial types, packages, and functions are recompiled. Since the functional indexes, like the one I demonstrated, depend on these objects, the related functional indexes are automatically disabled.

This behavior is document in “Concepts and Usage of Function Based Indexes (Doc ID 66277.1)“,

V. USING SQL, PLSQL FUNCTIONS IN FUNCTION BASED INDEX:

PL/SQL functions, and package functions can be used in creating function based index.

The PL/SQL functions used in defining function-based index must be declared as DETERMINISTIC. The owner of the index should have EXECUTE privilege on the function being used to create the index.

The index is dependent on the state of PL/SQL function. The index can be invalidated or made unusable by changes to the function. The index is marked DISABLED, if there are changes to the function or the function is recreated. The time-stamp of the function is used to validate the index.

To enable the index after the function is created, if the signature of the function is same as before:

ALTER INDEX ENABLE;

If the signature of the functions is changed, to make the changes effective in the index, the index need to be revalidated to make it valid and enable.

ALTER INDEX REBUILD;

Also According to the Oracle Database Database Development Guide, 19c, “If you change the semantics of a DETERMINISTIC function and recompile it, then you must manually rebuild any dependent function-based indexes and materialized views.”

We used the query provided below to detect which indexes needs enabling;

SQL> SELECT    'ALTER INDEX '
       || '"'
       || OWNER
       || '"'
       || '.'
       || '"'
       || INDEX_NAME
       || '"'
       || ' ENABLE;'
  FROM DBA_INDEXES
 WHERE FUNCIDX_STATUS = 'DISABLED';

FootNote: We also observed ‘TNS-12637: Packet receive failed’ and ‘TNS-12699: Native service internal’ errors on an application using third-party JDBC drivers. I recommend checking out the blog post ‘Beware If You’re Planning to Apply DBRU 19.24 on Products Using Third-Party JDBC Drivers!!!‘ by Adityanath Dewoolkar for more insights.

Hope it helps.