How to create Oracle Spatial Index?

Question

I am trying to create an Oracle Spatial index but seeing strange behavior.

I have a table in my schema as follows:


CREATE TABLE "Event" (  
"EventID" NUMBER(32,0) GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 START WITH 1 NOT NULL, 
    "Name" NVARCHAR2(30), 
    "Location" "SDO_GEOMETRY" NOT NULL, 
     CONSTRAINT "PK_EVENT" PRIMARY KEY ("EventID")
   ) ;

This works fine and I know I have to create an entry in user_sdo_geom_metadata, that works as you would expect with the following:

insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid) values ('Event','Location', 
sdo_dim_array(sdo_dim_element('X',-180.0,180.0, 0.005),sdo_dim_element('Y',-90.0,90.0, 0.005)), 4326);

This reports success and when I do a select on user_sdo_geom_metadata I see the row. However, when I try to create the spatial index with:

CREATE INDEX "EVINDEX" ON "Event" ("Location") INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2

I get the following error:

SQL Error [29855] [99999]: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13203: failed to read USER_SDO_GEOM_METADATA view
ORA-13203: failed to read USER_SDO_GEOM_METADATA view
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10

The weird thing is the Index looks like it's been created.

select * from all_indexes where table_name='Event';

Shows the index??? The other odd thing is when I do a select * on ALL_SDO_GEOM_METADATA, no rows are returned??? I'm connecting as a user with almost every privilege and role but not as SYSDBA. I can't get my head around this one.

UPDATE

Incredibly, this seems to be a case sensitivity issue. If you change the table and column names to all UPPERCASE it works. It seems my neverending disappointment in Oracle has a whole new chapter. Going to try to struggle through this somehow, but like most things with Oracle, it's one unrelenting slog to get anything done :(

Alex Poole · Answer

The documentation says:

The table name cannot contain spaces or mixed-case letters in a quoted string when inserted into the USER_SDO_GEOM_METADATA view, and it cannot be in a quoted string when used in a query (unless it is in all uppercase characters).

and

The column name cannot contain spaces or mixed-case letters in a quoted string when inserted into the USER_SDO_GEOM_METADATA view, and it cannot be in a quoted string when used in a query (unless it is in all uppercase characters).

However, it also says:

All letters in the names are converted to uppercase before the names are stored in geometry metadata views or before the tables are accessed. This conversion also applies to any schema name specified with the table name.

which you can see if you query the user_sdo_geom_metadata view after your insert; the mixed-case names have become uppercase EVENT and LOCATION.

But then:

Note: Letter case conversion does not apply if you use mixed case (“CamelCase”) names enclosed in quotation marks. However, be aware that many experts recommend against using mixed-case names.

And indeed, rather unintuitively, it seems to work if you include the quotes in the user_sdo_geom_metadata insert:

insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid)
values (
  '"Event"',
  '"Location"', 
  sdo_dim_array(sdo_dim_element('X',-180.0,180.0, 0.005),
  sdo_dim_element('Y',-90.0,90.0, 0.005)), 4326
);

db<>fiddle

So it appears that the values from the view are at some point concatenated into a dynamic SQL statement, which would explain some of the behaviour.

How to create Oracle Spatial Index?

1 Answers