Missing of Materialized view Indexes - leads performance issues

 When any Materialized view dropped and re-created, Indexes created on the materialized view should be taken care and re-created again. Otherwise, following query can be used to collect DDL scripts from source environment and run again if any index is missed during drop and re-create of materialized view.

-- All mview Index DDL - to generate GET_DDL script

select 'select dbms_metadata.get_ddl(''INDEX'',''' || INDEX_NAME || ''',''' ||
       OWNER || ''') from dual;' from all_indexes a where a.table_name in (
select v.MVIEW_NAME from all_mviews v )
and index_name not like '%PK%'

e.g.,
select dbms_metadata.get_ddl('INDEX','IDX_ID','HR') from dual;

--keep the above output and spool the output again for Create Script, 
eg.,

set heading off;
 set echo off;
 Set pages 999;
 set long 90000;
spool c:\temp\mvu_idx_ddl.log
select 'select dbms_metadata.get_ddl(''INDEX'',''' || INDEX_NAME || ''',''' ||
       OWNER || ''') from dual;' from all_indexes a where a.table_name in (
select v.MVIEW_NAME from all_mviews v )
and index_name not like '%PK%'
spool off;

Thanks
Hope sure you can collect DDL scripts from source database environment.

DBAsh.........njy..............