join index

[saxgeek]

In trying to clean up a test database, I've discovered a join index with no name on one of the tables. Does anyone know how to drop this index? I've tried drop join index but since it doesn't have a name, I'm stumped.

Thanks for any help.

 

[BillDHS]

Can it be "viewed" using WinDDI (Teradata Administrator), if so, use that utility to highlight and drop it.

 

[saxgeek]

I can only see it when I right click on the table and show indexes. When I show the table ddl, it doesn't show up. I have no idea how it got there either.

 

[dnoeth]

If it's a Join Index, you'll never see it in Show Table and it must have a name.

Please submit a

select
IndexNumber, IndexType, UniqueFlag,
ColumnName, ColumnPosition
from dbc.indices
where databasename = ...
and tablename = ...

and post the result.

Dieter

 

[dnoeth]

If it's a Join Index, it must have a name and you'll never see it using a Show Table...

Please submit a

select IndexNumber, IndexType, UniqueFlag, ColumnName, ColumnPosition
from dbc.indices
where databasename = ...
and tablename = ...

and post the results.

Dieter

 

[saxgeek]

Here are the results - we have tried doing a drop index on index 8 but it won't let us:

IndexNumber IndexType UniqueFlag ColumnName ColumnPosition
8 J N Appropriation 2
8 J N FundCode 1
1 P Y OID 1

 

[saxgeek]

Here's what I get when I right click on the table and select indexes:

IndexName ColumnName Type Unique IndexNumber ColumnPosition
OID Primary Y 1 1
FundCode Join Index N 8 1
Appropriation Join Index N 8 2