Informatica – PowerCenter – Session -Pre SQL – Post SQL – Drop / Create Indexes

Following SQL query can drop the specified indexes as part of pre SQL in Session: (This query checks existence of index before dropping)

BEGIN DECLARE COUNT_INDEXES_1 INTEGER \;
BEGIN DECLARE COUNT_INDEXES_2 INTEGER \;
BEGIN SELECT COUNT(*) INTO COUNT_INDEXES_1 FROM USER_INDEXES WHERE INDEX_NAME = ‘(mention the index 1 name)’ \;
BEGIN SELECT COUNT(*) INTO COUNT_INDEXES_2 FROM USER_INDEXES WHERE INDEX_NAME = ‘(mention the index 2 name)’ \;
IF COUNT_INDEXES_1 > 0 THEN EXECUTE IMMEDIATE ‘DROP INDEX (mention the index 1 name)’ \; END IF \;
IF COUNT_INDEXES_2 > 0 THEN EXECUTE IMMEDIATE ‘DROP INDEX (mention the index 2 name)’ \; END IF \;
END \;
END \;
END \;
END \;

 

Following SQL query can create the specified indexes as part of post SQL in Session: (This query checks existence of index before creating)

BEGIN DECLARE COUNT_INDEXES INTEGER \;BEGIN SELECT COUNT(*) INTO COUNT_INDEXES  FROM  USER_INDEXES WHERE INDEX_NAME = ‘(mention the index  name)’ \; IF COUNT_INDEXES = 0 THEN   EXECUTE IMMEDIATE ‘CREATE INDEX (mention the index  name) ON  (mention table name) (column name)’ \; END IF \;  END \;END \;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s