An Oracle Auto Index function to drop secondary indexes - what is a “secondary” index?


In 19.4 the Auto Index package has 4 procedure/functions:

  • CONFIGURE to set the Auto Index documented parameters
  • REPORT_ACTIVITY to get a CLOB about an Auto Index executions (statements analyzed, indexes created,…)
  • REPORT_LAST_ACTIVITY which calls the previous one for the last run only
  • DROP_SECONDARY_INDEX which is documented as “Deletes all the indexes, except the ones used for constraints, from a schema or a table.

What is a secondary index?

I have not used this term for a long time. First, I’ve learned it at school during my first databases classes. The course supposed that the table rows are stored ordered, on the primary key. Then, the index on the primary key is a bit special: it does not need to have an entry for each row. It can be “sparse”. A value between the two index entries will be physically between the two locations. This index on the primary key is the “primary” index. And of course, it is unique. The other indexes are called “secondary” and must be dense, with one entry for each row to address (scattered physically in the table) and can be unique or not.

Then I started to work on Oracle and forgot about all that: there’s no distinction between a “primary” and a “secondary” index. And there’s no need to create a “primary” one first. We can create many indexes, and later decide what will be the primary key. One of the indexes may be used to enforce the constraint if it starts with those columns, not even needed to be unique.

All indexes are “dense” with Oracle because there’s no physical order in a Heap Table. We can get an idea about “sparse” index when we think about the branches: they store only a min/max value for each leaf block. But they address a leaf block and not table blocks with full rows. Except when the leaves contain the full rows and there’s no additional Heap Table. This is the case with an Index Organized Table (IOT). The “sparse” branches and “dense” leaves are not the only analogy between Oracle IOT and the “primary” indexes. An IOT can be created only the primary key. It is a primary index. And the other indexes are secondary indexes: “dense”, addressing the IOT leaf blocks through its primary index.

There are other databases where the tables are clustered, must have a primary key defined at table creation time, enforced by the primary index. But Oracle, and all databases with heap tables, are different. Oracle does not constrain indexes to be primary or secondary, index definition is not dependent on physical storage. This is agility invented 40 years ago.


In the context of Auto Indexing, “secondary” indexes have a slightly different meaning. Of course, the index enforcing the primary key is not considered as “secondary”. But the idea of “secondary” goes further: all indexes that are not required by integrity constraints. This is the idea which started on Exadata where analytic workloads may not need indexes thanks to SmartScan. It continued with the Autonomous DataWarehouse cloud service, where the CREATE INDEX was not available — allowing only implicit indexes created by primary key or unique constraint. Now it goes to OLTP where indexes do not need to be created but the optimizer will create them automatically.

Here we can imagine that this Auto Index function is there to drop all the indexes created for performance reasons, so that the Auto Index feature can create the required ones only. But what about the indexes created on foreign key columns to avoid a table lock when the parent key is deleted? We don’t want to drop them, right? It is a secondary index, but can it be considered as “used for constraints” even if it is not referenced in the dictionary by DBA_CONSTRAINTS.INDEX_NAME?

I’ve created a few additional indexes on the SCOTT schema:

connect scott/tiger@//localhost/PDB1
create index EMP_FK on EMP(DEPTNO);
create bitmap index EMP_BITMAP on EMP(JOB);
create index EMP_FKPLUS on EMP(DEPTNO,JOB);
create unique index EMP_UNIQUE on EMP(HIREDATE,ENAME);
set ddl segment_attributes off
set ddl storage off
select dbms_metadata.get_ddl('INDEX',index_name,user) from user_indexes;

Here I have unique and non-unique, regular and bitmap, covering foreign key only, or foreign key plus other columns:

I run the “drop secondary index” procedure:

exec sys.dbms_auto_index.drop_secondary_indexes('SCOTT','EMP');

Here is what remains:

  • All unique indexes are still there, not only the ones on the primary key
  • the index that covers exactly the foreign key remains
  • all others have been dropped

But the index that covers more than the foreign key (EMP_FKPLUS) has been dropped. And don’t think that there’s some intelligence that detected the other index on the foreign key (EMP_FK). If you run the same without EMP_FK, the EMP_FKPLUS is still dropped. So be careful if you use this: an index which was created to avoid lock will be considered “secondary” except if it was created with exactly the same definition as the foreign key columns. I have sql_trace’d the query used to find the indexes to drop:

Look at the LISTAGG: the comparison between the foreign key columns and the index column is too simple in my opinion: exactly the same columns and in the same position. The index to solve a “foreign key lock issue” can be more complex: it only needs to start with the foreign key columns, in whatever order.

In summary, what is considered as dropping “secondary” indexes here is basically dropping all indexes that are not enforcing primary key or unique constraints and not matching exactly the foreign key column definition. This Drop Secondary Indexes procedure is probably there only for testing: removing all indexes that may be created automatically and see what happens.

Developer Advocate at Yugabyte, Open Source distributed SQL database. Incidentally Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member