Dropping/Recreating indexes in Informatica MDM

There won't be many times, but sometimes you may need to drop/recreate indexes on the CMX_ORS tables to typically re-import data. The following code PL/SQL code snippets shows how to do it.

Dropping Indexes
CREATE OR REPLACE
PROCEDURE drop_ors_indexes( in_table_name IN VARCHAR2)
AS
    ddlText VARCHAR2(1000);
    CURSOR constraint_ IS
        SELECT t.table_name,
            replace(trim(k.rowid_key_constraint,'.','_')) rowid_key_constraint
        FROM C_REPOS_TABLE t, C_REPOS_KEY_CONSTRAINT k
        ON ( t.rowid_table = k.rowid_table
        AND t.table_name = in_table_name)
        WHERE EXISTS ( SELECT 1 FROM USER_INDEXES u WHERE u.INDEX_NAME = replace(trim(k.rowid_key_constraint,'.','_'));
BEGIN
    FOR rec IN constraint_
    LOOP
        ddlText := 'DROP INDEX '||rec.rowid_key_constraint;
        execute immediate ddlText;
    END LOOP;
END drop_ors_indexes;
/

Re-creating indexes
CREATE OR REPLACE
PROCEDURE create_ors_indexes(in_table_name IN VARCHAR2)
AS
    Left as an exercise for the reader to create based on drop indexes.
    You would need to use C_REPOS_KEY_CONSTRAINT_COL to use the columns for the index. Tablespace name is also available from the C_REPOS_TABLE.
BEGIN
END create_ors_indexes;
/