Missing of Materialized view Indexes - leads performance issues
--------------------------------------------
Applies to Oracle Server - Enterprise Edition / Information in this document applies to any platform.
-- 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','INDX_SERVICEID','BILLING') 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\ehishyd.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;
