This content originally appeared on DEV Community 👩‍💻👨‍💻 and was authored by Franck Pachot
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 withSYS_C - the ones with
AUTO='YES'and name starting withSYS_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 '$'".
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.
This content originally appeared on DEV Community 👩‍💻👨‍💻 and was authored by Franck Pachot
Franck Pachot | Sciencx (2023-02-07T22:36:46+00:00) Oracle Auto indexes missing after ora2pg migration? Look at CONSTRAINT_INDEX. Retrieved from https://www.scien.cx/2023/02/07/oracle-auto-indexes-missing-after-ora2pg-migration-look-at-constraint_index/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.