Drop and recreate index/constraints in informatica presql/postsql

example:

I have a Table called Employee

Employee Table  is having constraint name on EmpNo  as EMPNO_PK and  index is on deptno as  Dept_No

so i want to drop and recreate the these indexes to improve the performance of my job.

Add the bleow script in  target/source preSQL based on requirement.

script to Drop the Index's :

DECLARE
   v_index        NUMBER := 0\;
   v_constraint   NUMBER := 0\;
BEGIN
   SELECT COUNT (*)
     INTO v_constraint
     FROM user_constraints
    WHERE table_name = 'Employee'
          AND constraint_name = 'EMPNO_PK'\;

   SELECT COUNT (*)
     INTO v_index
     FROM user_indexes
    WHERE table_name = 'Employee'
          AND index_name ='Dept_No'\;

   IF v_constraint <> 0
   THEN
      EXECUTE IMMEDIATE 'ALTER TABLE Employee DROP CONSTRAINT EMPNO_PK'\;
      END IF\;

   IF v_index <> 0
   THEN
      EXECUTE IMMEDIATE 'DROP INDEX Dept_No'\;
      END IF\;

   COMMIT\;
END\;


Note : if same table is having more than one index/constraints and if u want to drop those then simply u can change queries by putting IN( index1,index2 or constraint1 or constraint2) in above queries.
and u can include another EXECUTE IMMEDIATE obe by one.


Add the bleow script in  target/source postSQL based on requirement.

Script to Recreate Index:


DECLARE
   v_index        NUMBER := 0\;
   v_constraint   NUMBER := 0\;
BEGIN
   SELECT COUNT (*)
     INTO v_constraint
     FROM user_constraints
    WHERE table_name = 'Employee'
          AND constraint_name = 'EMPNO_PK'\;

   SELECT COUNT (*)
     INTO v_index
     FROM user_indexes
    WHERE table_name = 'Employee'
          AND index_name ='Dept_No'\;

   IF v_index = 0
   THEN
      EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX Dept_NO ON Emplyoee (deptno)'\;
      END IF\;

   IF v_constraint = 0
   THEN
      EXECUTE IMMEDIATE 'ALTER TABLE Employee ADD (CONSTRAINT EMPNO_PK  PRIMARY KEY (EmpNo) USING INDEX EMPNO_PK)'\;
      END IF\;

   COMMIT\;
END\;;


Note : what ever the index/constraints you dropped above same no of indexes/constraints you have to create. same like above you need to add IN function i nsql query to add more than one and add EXECUTE IMMEDIATE one by one.