How to drop a join index?

Database
Enthusiast

How to drop a join index?

I created a join index,and now I want to delete it.
I type :drop join index myindex ,and run it .
But the operation runs so long time(4 hours) ,I have to stop it.

Now I need you help,how can I drop the join index?

(BTW:Hash index has the same problem.)
3 REPLIES
Senior Apprentice

Re: How to drop a join index?

Hi Mr. Big :-)

the drop is probably blocked, try to drop with NOWAIT:
lock my_join_index exclusive nowait
drop join index my_join_index;

There might also be a left backup lock on the base table, which prevents any modification of the table structure...

Dieter
Enthusiast

Re: How to drop a join index?

To Dieter:
Thanks for your great help. Yes , my request blocked. I saw 'BLOCKED' in Teradata manager tool.
I try to drop it again with 'NOWAIT' option, but it doesn't working any more.
yes, I have modified base table structure , but now I can NOT use 'show table' to the base table yet.
How can I do next?
Enthusiast

Re: How to drop a join index?

OK,I resolve it.
Run arcmain,and 'release lock(mydb) all'.
that's ok.