Oracle Auto indexes missing after ora2pg migration? Look at CONSTRAINT_INDEX

It seems that Oracle customers started using the Auto Index feature. I can see that when they migrate to YugabyteDB. YugabyteDB Voyager is a tool to ease the migration of schema and data. As YugabyteDB is PostgreSQL compatible, ora2pg is used when the source is Oracle. It extracts the schema and data. Some customers moving from Oracle ATP managed service (Autonomous Transaction Processing) reported that a few indexes were not extracted: the ones that have been created automatically by the Automatic Indexing feature of Oracle 19c.

GENERATED

Being open source, it is easy to see that ora2pg gets the list of indexes from ALL_INDEXES B where " B.GENERATED = 'N'": https://github.com/darold/ora2pg/blob/2b9e77153cadf1d75e70ab6273445149d047d5c9/lib/Ora2Pg/Oracle.pm#L866,L868

The GENERATED column indicates that the index name is generated by the system (Y) or given by the user (N). It is used by ora2pg to filter out the generated indexes because they are supposed to be created automatically, usually by a PRIMARY KEY or UNIQUE constraint.

Let's check a few columns from DBA_INDEXES on an Oracle Database where some Auto Indexes have been created:

select
 regexp_replace(index_name,'^(SYS_[A-Z]*)?.*','\1') sys_name,
 index_type, uniqueness, generated, auto, constraint_index,
 count(*)
 from dba_indexes
 group by 
  regexp_replace(index_name,'^(SYS_[A-Z]*)?.*','\1'),
 index_type, uniqueness, generated, auto, constraint_index
  order by 6,5,4,3,2,1

There are two cases of GENERATED='Y' here:

• the ones with CONSTRAINT_INDEX='YES' and having their name starting with SYS_C
• the ones with AUTO='YES' and name starting with SYS_AI

This means that filtering on GENERATED='Y' is not the right way to filter out the indexes created by a constraint.

AUTO

A quick fix to include the Automatic Indexes would be replacing " B.GENERATED = 'N'" by "( B.GENERATED = 'N' or B.AUTO = 'YES' )". This would require checking the presence of the AUTO column which was added in Oracle 19c. However, I don't think that GENERATED is the right way to identify which indexes we want to export.

CONSTRAINT_INDEX

CONSTRAINT_INDEX='NO' looks like the right filter. However, it appears only in Oracle 21c and is not documented. Looking at the definition of DBA_INDEXES, it is based on IND$.PROPERTY with bitflag 4096, which is documented in dcore.bsq as:

/* The index was created by a constraint : 0x1000 */

The definition is clear: this is the right way to skip the indexes that were created by the constraint.

Indexes created implicitly or explicitely

To be sure, I check that the value is NO for a user-created index that enforces a constraint:

DEMO@o21c_tp> create table demo (a number);
Table DEMO created.

DEMO@o21c_tp> create index demo on demo(a);
Index DEMO created.

DEMO@o21c_tp> select regexp_replace(index_name,'^(SYS_[A-Z]*)?.*','\1') sys_name, index_type, uniqueness, generated, auto, constraint_index from user_indexes where table_name='DEMO';

SYS_NAME    INDEX_TYPE    UNIQUENESS    GENERATED    AUTO    CONSTRAINT_INDEX
___________ _____________ _____________ ____________ _______ ___________________
            NORMAL        NONUNIQUE     N            NO      NO

DEMO@o21c_tp> alter table demo add constraint demo unique(a);
Table DEMO altered.

DEMO@o21c_tp> select regexp_replace(index_name,'^(SYS_[A-Z]*)?.*','\1') sys_name, index_type, uniqueness, generated, auto, constraint_index from user_indexes where table_name='DEMO';

SYS_NAME    INDEX_TYPE    UNIQUENESS    GENERATED    AUTO    CONSTRAINT_INDEX
___________ _____________ _____________ ____________ _______ ___________________
            NORMAL        NONUNIQUE     N            NO      NO

With GENERATED='N' this index will be exported by ora2pg, which makes sense because it may include some additional columns.

However, you can also have an index created with the constraint, without a generated name, as it takes the name of the constraint:

DEMO@o21c_tp> alter table demo drop constraint demo;
Table DEMO altered.

DEMO@o21c_tp> drop index demo;
Index DEMO dropped.

DEMO@o21c_tp> alter table demo add constraint demo unique(a);
Table DEMO altered.

DEMO@o21c_tp> select regexp_replace(index_name,'^(SYS_[A-Z]*)?.*','\1') sys_name, index_type, uniqueness, generated, auto, constraint_index from user_indexes where table_name='DEMO';

SYS_NAME    INDEX_TYPE    UNIQUENESS    GENERATED    AUTO    CONSTRAINT_INDEX
___________ _____________ _____________ ____________ _______ ___________________
            NORMAL        UNIQUE        N            NO      YES

This undocumented CONSTRAINT_INDEX should be used by ora2pg but it isn't exposed in 19c.

'SYS_C'

Without CONSTRAINT_INDEX we have to stay with the filtering on the generated name. Then to include the Automatic Indexes one possibility is adding those with AUTO='YES'.

Another possibility is to rely on the fact that indexes generated by a constraint all start with SYS_C_ and there's a good chance that no user index start with this prefix. Then, this can be used to replace " B.GENERATED = 'N'" by: " B.INDEX_NAME = like 'SYS$_C$_%' escape '$'".

Get index definition without hacking anything

If you know that you missed the Auto Indexes, it is easy to get their definition with dbms_metadata:

set long 100000
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
select 
 regexp_replace(
 dbms_metadata.get_ddl('INDEX',index_name,owner) 
 ,'( AUTO *$)','--\1',1,1,'m')
 from dba_indexes 
 where auto='YES' and status='VALID';

You can create them in YugabyteDB. Please check the access patterns as some may benefit from range sharding (adding ASC or DESC) when they are queried with inequality predicates.

Finally

For migrating to YugabyteDB, I recommend reviewing quickly all indexes because you may want to think about some optimizations like range sharding, covering indexes, and primary key. So, finally, this doesn't matter too much. You should have a quick check comparing indexes in source and target. YugabyteDB has also a Performance Advisor that can recommend some indexes to add or remove.

If you want to read a nice story about this subtle difference of indexes created explicitly or with constraint, and why it matters, you will love this story where Tim Gorman accidentally dropped the 16TB main index. Because when an index is created with the constraint, it is dropped when you disable it.