After you insert significant amounts of data into a target, you normally need to drop and re-create indexes on that table to optimize query speed. You can drop and re-create indexes by either of the following methods:
Using pre- and post-session SQL commands.
The preferred method for dropping and re-creating indexes is to define a pre-session SQL statement in the Pre SQL property that drops indexes before loading data to the target. Use the Post SQL property to re-create the indexes after loading data to the target. Define pre- and post-session SQL for relational targets in the mapping target properties or on the Mappings tab in the session properties.
Using the Designer.
The same dialog box you use to generate and execute DDL code for table creation can drop and re-create indexes. Every time you run a workflow that modifies the target table, launch the Designer and use this feature when you use this method.
You can also use stored procedures to drop and re-create indexes.