Is it possible to ignore indexes when defining temp table like db table?

[Cringer]

Is it possible to ignore indexes when defining temp table like db table? I have a db table that has a unique index on it, but the temp table can quite validly have duplicates in it (because there's an additional field that's on the temp table that defines uniqueness). Is there a way I can define my temp table like the db table, but ignore any indexes?

 

[Cringer]

Oh I'm on 10.2A03 on a Win XP box.

 

[Cringer]

Found the way already. Can just define another index on the tt with the same name and it overrides that of the db definition it seems.

 

[RealHeavyDude]

That is expected behavior. Whenever you define something LIKE then everything will be inherited except what you explicitly define. That of course will take precedence.

Heavy Regards, RealHeavyDude.

 

[Cringer]

Thanks RHD - I expected to get an error if I tried to define the index - attempt to define duplicate index, or something of that ilk.

 

[Mike Derrick]

If I recall correctly, when you define your TT LIKE it will inherit the indexes from the database. BUT - if you specify your own index, any of the database indexes you want must be now be specified to be included as part of the temp-table definition. So you may want to check your TT and see if it has all the indexes you think it has.

After posting this, I reread the Help on defining Temp-Tables. Here are a couple of snippits:

If you use the USE-INDEX option, only the definitions you specify with that option are copied to the temp-table.

If you do not specify the USE-INDEX option and do not use the INDEX option of the DEFINE TEMP-TABLE statement,
then all index definitions are copied from the specified table to the temp-table.

 

[Cringer]

Thanks Mike - not relying on the indexes so all is good!