Removing Indexes

[rafidwh]

Hi Gurus,

I have a task where

1) In the first job I need to drop indexes from the table.
2) Load the table using SQL loader.
3)Add the indexes againg in the table.

My concern is how to drop and add indexes to the Table through Datastage

Best suggestions please

Thanks in advance

[kcbland]

Use sqlplus to run any SQL script. Choose a Batch or Sequence job to do the deed.

[kduke]

On some databases you can disable and then enable indexes after the load which is faster than dropping and creating.

[rafidwh]

Is there any option in datastage to disable and enable indexes .

How can I enable and disable the indexes,suggest me please

[rasi]

Hi

There is no direct way to disable and enable index from datastage. As Kenneth said you can have SQL script which does all these things and can be called from datastage.

Check the Oracle Reference Guide for syntax to disable and enable index

[rleishman]

Unless you are using Oracle v7.3, leave the indexes where they are and use SQL*Loader in Direct Path mode. Index maintenance is deferred until after the load. This is usually much better than dropping them or marking them as unusable. The exception is when the loaded data is large compared to the existing rows in the table - this is unusual in a Data Warehouse environment.

See this chapterin the manual.

If you still really want to do it, it would be simple enough to write a stored procedure that selected the names from USER_INDEXES and used dynamic SQL to mark them UNUSABLE.

[manojmathai]

Hi

Try a dummy job to select count(*) from some table .In the before / after SQL write the index dropping SQL.

Thanks
Manoj