Oracle 19c Automatic Indexing: Dropping Automatic Indexes (Fall Dog Bombs The Moon)

 

Julian Dontcheff recently wrote a nice article on the new Automatic Index Optimization feature available in the upcoming Oracle Database 20c release (I’ll of course blog about this new 20c feature in the near future).

Within the article, Julian mentioned a clever method of how to effectively drop Automatic Indexes that I thought would be worth checking out.

For a number of reasons (which I’ll cover in some detail in upcoming articles, but for now using Automatic Indexing in REPORT ONLY mode is but one reason), you can easily be left with an Automatic Index that might get in the way of things and you may want to drop it.

However, as we’ll see, you can’t easily drop an Automatic Index. The only “supported” manner to drop an Automatic Index is to wait for the Automatic Index Retention period to be exceeded (which is by default some 373 days and assumes the index is not used during this period).

Julian has come up with an alternate strategy.

By way of a demo, I currently have the following Automatic Index (SYS_AI_5zjkc60knz9zp):

SQL> select index_name, auto, status, visibility from user_indexes
where table_name='CRACKED_ACTOR';

INDEX_NAME                     AUT STATUS   VISIBILIT
------------------------------ --- -------- ---------
CRACKED_ACTOR_CODE1_CODE2_I    NO  VALID    VISIBLE
SYS_AI_5zjkc60knz9zp           YES VALID    INVISIBLE

SQL> select index_name, column_name, column_position from user_ind_columns
where index_name='SYS_AI_5zjkc60knz9zp';

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
SYS_AI_5zjkc60knz9zp           ID                                           1

 

So I have an Automatic Index on the ID column of the CRACKED_ACTOR table, BUT it’s currently INVISIBLE and so can’t be used be default by the CBO.

If I run the following query:

SQL> select * from cracked_actor where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 786009234

-----------------------------------------------------------------------------------
| Id | Operation        | Name          | Rows | Bytes | Cost (%CPU)| Time        |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |               |    1 |    24 |       3 (0)| 00:00:01    |
|* 1 | TABLE ACCESS FULL| CRACKED_ACTOR |    1 |    24 |       3 (0)| 00:00:01    |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

     1 - filter("ID"=42)
Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
      7042 consistent gets
         0 physical reads
         0 redo size
       789 bytes sent via SQL*Net to client
       401 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

 

The CBO uses a Full Table Scan because the available Automatic Index on the ID column is current invisible.

If I try to convert it to being VISIBLE:

SQL> alter index "SYS_AI_5zjkc60knz9zp" visible;
alter index "SYS_AI_5zjkc60knz9zp" visible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

I can’t, because you can’t alter an Automatic Index to be Visible/Invisible.

If I try to just drop the Automatic Index:

SQL> drop index "SYS_AI_5zjkc60knz9zp";
drop index "SYS_AI_5zjkc60knz9zp"
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

Again, I can’t just simply drop an Automatic Index.

However, I am allowed to Rebuild (or Coalesce or Shrink) an Automatic Index. Therefore, I can create a new dummy tablespace and rebuild the Automatic Index to reside in this particular tablespace:

SQL> alter index "SYS_AI_5zjkc60knz9zp" rebuild tablespace bowie_stuff;

Index altered.

 

I can then drop this tablespace including all its contents (and hence drop the Automatic Index contained within):

SQL> drop tablespace bowie_stuff including contents and datafiles;

Tablespace dropped.

 

The problematic Automatic Index is now gone:

SQL> select index_name, auto, status, visibility from user_indexes
where table_name='CRACKED_ACTOR';

INDEX_NAME                     AUT STATUS   VISIBILIT
------------------------------ --- -------- ---------
CRACKED_ACTOR_CODE1_CODE2_I    NO  VALID     VISIBLE

I can now either manually create the necessary index or wait for the Automatic Index to now hopefully create a new, visible Automatic Index to address my query:

SQL> create index cracked_actor_id_i on cracked_actor(id);

Index created.

SQL> select index_name, auto, status, visibility from user_indexes where table_name='CRACKED_ACTOR';

INDEX_NAME                     AUT STATUS   VISIBILIT
------------------------------ --- -------- ---------
CRACKED_ACTOR_CODE1_CODE2_I    NO  VALID    VISIBLE
CRACKED_ACTOR_ID_I             NO  VALID    VISIBLE

 

I’ve now addressed the problematic query:

SQL> select * from cracked_actor where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 4160941723

----------------------------------------------------------------------------------------------------------
| Id | Operation                          | Name               | Rows | Bytes | Cost (%CPU)| Time        |
----------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |                    |    1 |    24 |       2 (0)| 00:00:01    |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED| CRACKED_ACTOR      |    1 |    24 |       2 (0)| 00:00:01    |
|* 2 | INDEX RANGE SCAN                   | CRACKED_ACTOR_ID_I |    1 |       |       1 (0)| 00:00:01    |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

      2 - access("ID"=42)
Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         3 consistent gets
         0 physical reads
         0 redo size
       793 bytes sent via SQL*Net to client
       401 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

 

Thanks Julian for the cool tip 🙂