Find missing index

We might miss some indexes when we do export and import. How could we find the missing several ones from hundreds of indexes.

We can not use index name to identify the missing ones since system generated index have different name in original and imported schemas.

We can not use table_name, index_column and column_position to identify them since a table column can be used by multiple indexes.

1. we need compare number of (table_name, index_column and column_position)

Query:
select table_name, column_name, column_position, count(*) from dba_ind_columns
where index_owner='ORIGINAL_SCHEMA' group by table_name, column_name, column_position
minus
select table_name, column_name, column_position, count(*) from dba_ind_columns
where index_owner='IMPORTED_SCHEMA' group by table_name, column_name, column_position;

EXAMPLE:

SQL> select table_name, column_name, column_position, count(*) from dba_ind_columns
2 where index_owner='ORIGINAL_SCHEMA' group by table_name, column_name, column_position
3 minus
4 select table_name, column_name, column_position, count(*) from dba_ind_columns
5 where index_owner='IMPORTED_SCHEMA' group by table_name, column_name, column_position;

TABLE_NAME COLUMN_NAME COLUMN_POSITION COUNT(*)
------------------------------ -------------------- --------------- ----------
TABLE1 ID 1 4

SQL> select table_name, column_name, column_position, count(*) from dba_ind_columns
2 where index_owner='IMPORTED_SCHEMA' group by table_name, column_name, column_position
3 minus
4 select table_name, column_name, column_position, count(*) from dba_ind_columns
5 where index_owner='ORIGINAL_SCHEMA' group by table_name, column_name, column_position;

TABLE_NAME COLUMN_NAME COLUMN_POSITION COUNT(*)
------------------------------ -------------------- --------------- ----------
TABLE1 ID 1 3


2. find exact index name
SQL> select index_name, table_name, column_name, column_position from dba_ind_columns
2 where index_owner='ORIGINAL_SCHEMA' and table_name = 'TABLE1';

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------- ---------------
SYS_C002474232 TABLE1 ID 1
IND_TABLE1_1 TABLE1 OID 1
IND_TABLE1_2 TABLE1 EDATE 1
IND_TABLE1_3 TABLE1 ID 1
IND_TABLE1_3 TABLE1 EDATE 2
IND_TABLE1_4 TABLE1 ID 1
IND_TABLE1_4 TABLE1 OID 2
IND_TABLE1_5 TABLE1 OID 1
IND_TABLE1_5 TABLE1 EFDATE 2
IND_TABLE1_6 TABLE1 ID 1
IND_TABLE1_6 TABLE1 OID 2

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------- ---------------
IND_TABLE1_6 TABLE1 EDATE 3

12 rows selected.

SQL> select index_name, table_name, column_name, column_position from dba_ind_columns
2 where index_owner='IMPORTED_SCHEMA' and table_name = 'TABLE1';

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ -------------------- ---------------
IND_TABLE1_2 TABLE1 EDATE 1
IND_TABLE1_3 TABLE1 ID 1
IND_TABLE1_3 TABLE1 EDATE 2
IND_TABLE1_4 TABLE1 ID 1
IND_TABLE1_4 TABLE1 OID 2
IND_TABLE1_5 TABLE1 OID 1
IND_TABLE1_5 TABLE1 EDATE 2
IND_TABLE1_6 TABLE1 ID 1
IND_TABLE1_6 TABLE1 OID 2
IND_TABLE1_6 TABLE1 EDATE 3
IND_TABLE1_1 TABLE1 OID 1

11 rows selected.

The missing index is SYS_C002474232

3. get index info for creation.

SQL> select index_name, index_type, uniqueness from dba_indexes where index_name ='SYS_C002474232';

INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
SYS_C002474232 NORMAL UNIQUE


4. create missing index.

create index id_table1 on imported_schema.table1 (ID);