Informix Indexes - Help

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? 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. 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>. Appreciate any help on this issue. Thanks Regards, Vamsi Mohan Harish -- Message posted via http://www.dbmonster.com

Not really sure I understand your question. or are there 2 or 3 questions ? 1/ 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? 2/ > 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. So after the migration system generated indexes exist in Informix. Did they exist before migration? 3/ And those Indexes are not migrated. Well, if informix generated them for whatever reason, I wouldn't gaurantee Oracle would. Maybe Oracle doesn't think you need them. You seem to suggest that you do need them. Maybe time to move to IDS 10 instead of Oracle!! Are there any other types of constraints on those columns? eg UNIQUE? I suspect you have some tables that have columns which are defined as having a UNIQUE contraint. Informix implemnts this via an system generated index. In Informix it is possible to have multiple each defined as UNIQUE. However it is only possible to define 1 primary key. Does Oracle have an equivalent to a UNIQUE contraint? If not then some of your contraint checking may have to be recoded or you may be moving back to Informix! 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? > > 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. > > 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>. > > Appreciate any help on this issue. > > Thanks > > Regards, > Vamsi Mohan Harish > > -- > Message posted via http://www.dbmonster.com

In Informix it is possible to have multiple each defined as UNIQUE. should have read.... In Informix it is possible to have multiple columns, each defined as UNIQUE.

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? > > 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. > > 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>. IDS also generates such indexes for UNIQUE constraints. If dbschema or myschema reports that the table has a unique constraint that is likely the unidentified index. FYI, you can find out what constraint the index belongs to thus: select t.tabname, c.* from sysconstraints c, sysindexes si, systables t where si.tabid = t.tabid and si.idxname = c.idxname and c.idxname = " 123_33"; The constrtype column will tell you what kind of constraint it is. Art S. Kagel > Appreciate any help on this issue. > > Thanks > > Regards, > Vamsi Mohan Harish >