In Hive 3 the rarely used "INDEXES" was removed from the DDL
There are a few issues here:
- The Standalone-Metastore schema for Hive 3+ all include the "IDXS" table, which has no function.
- The upgrade schemas from 2.x -> 3.x do not do any cleanup of the IDXS table
- If a user used the "INDEXES" feature in 2.x and then upgrades their metastore to 3.x+ they cannot drop any table that has an index on it due to "IDXS_FK1" constraint since the TBLS entry is referenced in the IDXS table
- Since INDEX is no longer in the DDL they cannot run any command from Hive to drop the index.
- Users can manually connect to the metastore and either drop the IDXS table or the foreign key constraint
Since indexes provide no benefits in Hive 3+ it should be fine to drop them completely in the schema upgrade scripts. At the very least the 2.x -> 3.x+ scripts should drop the fk constraint.