Page 1 of 1
How to delete the indexes of a table using routine
Posted: Sat Feb 24, 2007 12:17 pm
by Madhu1981
Hi
I have to delete the indexes of a table using routines. Can anyone help me... please
Posted: Sat Feb 24, 2007 12:51 pm
by narasimha
Write a script which will drop indices.
Execute this script in your routine!
Posted: Sat Feb 24, 2007 1:51 pm
by DSguru2B
Or you can do it in the OPEN command of a database stage. And how exactly are you going to do it in a routine? Are you planning do use a Basic Transformer?
Re: How to delete the indexes of a table using routine
Posted: Sat Feb 24, 2007 2:37 pm
by chulett
Madhu1981 wrote:I have to delete the indexes of a table using routines.
No, you don't. You may need to drop them but why do you believe that you 'need' to do this in a routine?
As noted, the OPEN command is a more appropriate place for commands of that nature. Another solution, seeing as how most users would not have the privledges needed for this, would be the creation of procedure under a user that does and then grant your user the ability to execute it.
You'll need to rebuild them once you are done.
Posted: Sat Feb 24, 2007 3:16 pm
by ray.wurlod
Craig beat me to it.
Always challenge unreasonable and preposterous requirements.
Surely if you can drop indexes without a routine, that is more efficient? How good are your C++ skills? What resources do "they" have for maintaining routines?
Posted: Sun Mar 04, 2007 9:20 am
by Madhu1981
DSguru2B wrote:Or you can do it in the OPEN command of a database stage. And how exactly are you going to do it in a routine? Are you planning do use a Basic Transformer?
Hi Guru...
could you explain me in detail. I haven't used this OPEN command in database stage. Can you tell me the syntax of this open command also.
Thanks in advance
Posted: Sun Mar 04, 2007 10:31 am
by DSguru2B
Its in the database stage. Syntax will be the same as you would use in your favourite sql tool. OPEN command will run only once when the connection to the database opens, before the main sql. You just need to drop index <<index_name>> on <<table_name>> . Search in google for the exact syntax for your database.
Posted: Sun Mar 04, 2007 10:34 am
by ray.wurlod
The syntax for OPEN and CLOSE command is "any valid SQL".
"Valid" includes your having appropriate database/table privileges to execute the said command.
Posted: Mon Mar 05, 2007 12:06 am
by kumar_s
What is the stage you are using to load the data.
Oracle Enterprise stage has "Index mode" as a option, where you can select "Rebuild". Where these drop and recreate will be taken care by Datastage.