Oracle Auto indexes missing after ora2pg migration? Look at CONSTRAINT_INDEX
Tags: oracle, postgres, yugabytedb, migration
Published: 2023-02-07 22:36:46

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](https://github.com/darold/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](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_INDEXES.html) 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:

```sql
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
```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r1w553ub4swor284t9j0.png)

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:
```sql
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:
```sql
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`:

```sql
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';

```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/za67nrbcajuwm7n8whti.png)

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](https://nocoug.wordpress.com/2014/12/17/interview-with-tim-gorman-how-i-accidentally-dropped-the-16tb-main-index/). Because when an index is created with the constraint, it is dropped when you disable it.