Re: Informix Indexes - Help

Hi, Harish Vamsi Mohan via DBMonster.com wrote: > Hello, > > We are migrating Informix 9.4 to Oracle 9i database. We are facing an issue > with the Indexes on the Informix. When a Primary or a Foreign Key (for > which an Index is created implicitly on Informix) is dropped, will the > Indexes will also get deleted automatically? Why are you facing problems, if your source db is IDS? Your Oracle instance is the one with the problems, right? And the answer to your question is: it depends. If you create the index itself and later you add the constraint there are two separate 'objects' in the database and you must drop them both. If you create the constraint without worring about the index then dropping the constraint will drop the index too. > > Because, when we verified and validated the Indexes after migration, we > have found some instances where the system generated index is present on a > Column in Informix which is neither a Primary key nor a Foreign Key. And > those Indexes are not migrated. > Several e-mails have been written about unique constraints generating indexes, but I believe that your question is not an Informix problem... how does Oracle keeps unique constraints? It sounds like your application using those unique_constraint_generated_indexes without your knowledge. If you need them just create them in Oracle. > Also, we need to know why these system indexes are created(its neither > related to primary key constraint nor foreign key constraint). > > To our understanding, System generated Indexes are of the syntax > <TabID>_<ColID>. ' <TabID>_<ConstraintId>' Notice the first character is a blank space. > > Appreciate any help on this issue. > > Thanks > > Regards, > Vamsi Mohan Harish > sending to informix-list