We have temporary table where we store details of the index (to be created or dropped). How can we find an index name based on the column names, position and table name. Below are the details I'm trying:
MI_INDEXES_MASTER
| INDX_KEY | TABLE_NAME | UNIQUENESS | COLUMN_NAME | ALTER_CONDITION | COLUMN_POSITION |
| 261 | MI_READING0 | UNIQUE | MI_READING0_RDG_TAKEN_DT_D | DROP | 1 |
| 261 | MI_READING0 | UNIQUE | MI_READING0_RDG_VAL_CHAR_C | DROP | 2 |
| 261 | MI_READING0 | UNIQUE | MI_READING0_RDG_VAL_NUM_N | DROP | 3 |
| 261 | MI_READING0 | UNIQUE | MI_READING0_RELAT_ML_ENTIT_KEY | DROP | 4 |
| 261 | MI_READING0 | UNIQUE | FMLY_KEY | DROP | 5 |
I'm trying with the below query but unable to get the index name, if there are other indexes with same fields but different positions or same number of fields but few different fields.
SELECT DISTINCT uic.index_name
FROM mi_indexes_master mi
JOIN user_ind_columns uic
ON uic.table_name = mi.table_name
AND uic.column_name = mi.column_name
AND uic.column_position = mi.column_position
JOIN user_indexes ui
ON ui.uniqueness = mi.uniqueness
AND ui.index_name = uic.index_name
WHERE mi.indx_key = 261;
Using Oracle 11g on Win7