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
Removing Indexes
Moderators: chulett, rschirm, roy
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
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
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
-
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
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.
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.
Ross Leishman
-
- Participant
- Posts: 23
- Joined: Mon Jul 04, 2005 6:25 am