Oracle Auto Indexes are ignored

[FranckPachot]

The indexes created by Oracle Auto Indexing are marked as GENERATED = 'Y' and then are ignored by ora2pg. A user that runs with Auto Indexing in Oracle probably wants the same indexes on PostgreSQL. Ideas:

• To filter out the indexes created by Primary Key and Unique constraints there's a CONSTRAINT_INDEX column as of 21c but replacing GENERATED by this would also need to filter out LOB, IOT,...
• There's an AUTO column, and the condition could be GENERATED = 'N' or AUTO='YES' but this column is not there in pre 19c versions
• A dirty trick that can be used in all versions is relying on the fact that generated names start with SYS_C_ and then the filter can be:

  (B.INDEX_NAME like 'SYS$_C$_%' escape '$')

  instead of

  (B.GENERATED = 'N')

  More about this: https://dev.to/yugabyte/oracle-auto-indexes-missing-after-ora2pg-migration-look-at-constraintindex-31p8

[darold]

Hi Franck,

Commit e5fd2a0 fixes this issue using the dirty trick with the filter on column name. Thanks for detailed description of this issue, I was not aware of this new feature in Oracle 19c.