Search

Top 60 Oracle Blogs

Recent comments

How to drop an index created by Oracle 19c Auto Indexing?

ORA-65532: cannot alter or drop automatically created indexes

Oracle 19c Automatic Indexing is not like the autonomous features that happen without your control. You can decide to enable it (if you are on a platform that allows it) or not, and in report-only or implementation mode.

But when you have enabled it to create new indexes, you are not supposed to revert its effect. What if you want to drop those indexes?

DROP INDEX

If I want to drop an index that has been created automatically (i.e with the AUTO=’YES’ in DBA_INDEXES) I get the following error:

SQL> select owner,index_name,auto,tablespace_name from dba_indexes natural where auto='YES';
OWNER              INDEX_NAME    AUTO    TABLESPACE_NAME
________ _______________________ _______ __________________
ADMIN SYS_AI_8u25mzzr6xw1v YES AITBS
ADMIN SYS_AI_gg1ctjpjv92d5 YES AITBS
ADMIN SYS_AI_26rdw45ph3hag YES AITBS
SQL> drop index ADMIN."SYS_AI_8u25mzzr6xw1v";
drop index ADMIN."SYS_AI_8u25mzzr6xw1v"
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

ALTER INDEX

I get the same error if I try to make it invisible (so that at least it is not used by the queries) or unusable (so that it is not maintained by the DML):

SQL> alter index ADMIN."SYS_AI_8u25mzzr6xw1v" invisible;
alter index ADMIN."SYS_AI_8u25mzzr6xw1v" invisible
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes
SQL> alter index ADMIN."SYS_AI_8u25mzzr6xw1v" unusable;
alter index ADMIN."SYS_AI_8u25mzzr6xw1v" unusable
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

IND$.PROPERTY unsupported hack

In ?/rdbms/admin/cdcore_ind.sql the definition for DBA_INDEXES defines AUTO as:

decode(bitand(i.property, 8), 8, 'YES', 'NO'),
...
from ... sys.ind$ i ...

In ?/rdbms/admin/dcore.bsq the comment for this IND$ flag is probably wrong (probably an old flag being re-used for the Auto-Index feature):

property number not null,/* immutable flags for life of the index */
/* unique : 0x01 */
/* partitioned : 0x02 */
/* reverse : 0x04 */
/* compressed : 0x08 */
/* functional : 0x10 */

The comment is wrong but the important thing is that the AUTO attribute is defined as an immutable property rather than a flag that can be mutable.

This gives me a possibility to drop an index that has been created by the Auto Index feature, but totally unsupported, undocumented and probably very dangerous. Here is the OBJECT_ID:

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where auto='YES';
OWNER           INDEX_NAME OBJECT_ID AUTO TABLESPACE_NAME
_____ ____________________ _________ ____ _______________
ADMIN SYS_AI_8u25mzzr6xw1v 73191 YES AITBS
ADMIN SYS_AI_gg1ctjpjv92d5 73192 YES AITBS
ADMIN SYS_AI_26rdw45ph3hag 73193 YES AITBS

The property 0x8 is set:

SQL> select property from sys.ind$ where obj#=73191;
PROPERTY
----------
8

I un-flag it:

SQL> show user
show user
USER is "SYS"
SQL> update sys.ind$ set property=property-8 
where bitand(property,8)=8 and obj#=73191;
1 row updated.

Not anymore flagged as AUTO:

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where index_name like 'SYS_AI%';
OWNER           INDEX_NAME OBJECT_ID AUTO TABLESPACE_NAME
_____ ____________________ _________ ____ _______________
ADMIN SYS_AI_8u25mzzr6xw1v 73191 NO AITBS
ADMIN SYS_AI_gg1ctjpjv92d5 73192 YES AITBS
ADMIN SYS_AI_26rdw45ph3hag 73193 YES AITBS

And I can now drop it:

SQL> drop index ADMIN."SYS_AI_8u25mzzr6xw1v";
Index dropped.

Again, this is totally unsupported: don’t do that!

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where index_name like 'SYS_AI%';
OWNER           INDEX_NAME OBJECT_ID AUTO TABLESPACE_NAME
_____ ____________________ _________ ____ _______________
ADMIN SYS_AI_gg1ctjpjv92d5 73192 YES AITBS
ADMIN SYS_AI_26rdw45ph3hag 73193 YES AITBS

DROP TABLESPACE

In a more supported way, I can drop all AUTO indexes by dropping the tablespace where they reside. If I plan to do that, I’ve probably defined a specific tablespace for them (rather than the default tablespace for the user):

SQL> select parameter_name,parameter_value from dba_auto_index_config order by 1;
                    PARAMETER_NAME    PARAMETER_VALUE
__________________________________ __________________
AUTO_INDEX_COMPRESSION OFF
AUTO_INDEX_DEFAULT_TABLESPACE AITBS
AUTO_INDEX_MODE IMPLEMENT
AUTO_INDEX_REPORT_RETENTION 31
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 50

This just works to remove all indexes created there:

SQL> drop tablespace AITBS including contents;
Tablespace dropped.

MOVE and DROP

I may not want to drop all of them. What if I move one index into a new tablespace? I don’t want to actually rebuild it, unusable is ok for me:

SQL> alter index ADMIN."SYS_AI_26rdw45ph3hag" rebuild tablespace EPHEMERAL unusable;
alter index ADMIN."SYS_AI_26rdw45ph3hag" rebuild tablespace EPHEMERAL unusable
*
ERROR at line 1:
ORA-14048: a partition maintenance operation may not be combined with other operations

Well, I don’t know how to do this without rebuilding it. So let’s do this:

SQL> create tablespace EPHEMERAL nologging;
Tablespace created.
SQL> alter user admin quota unlimited on EPHEMERAL;
User altered.
SQL> alter index ADMIN."SYS_AI_26rdw45ph3hag" rebuild tablespace EPHEMERAL online;
Index altered.

This works, so not all ALTER INEX commands fail with an ORA-65532.

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where index_name like 'SYS_AI%';
OWNER           INDEX_NAME OBJECT_ID AUTO TABLESPACE_NAME
_____ ____________________ _________ ____ _______________
ADMIN SYS_AI_gg1ctjpjv92d5 73192 YES AITBS
ADMIN SYS_AI_26rdw45ph3hag 73193 YES EPHEMERAL

And I can now drop this tablespace that contains only this index:

SQL> drop tablespace EPHEMERAL including contents;
Tablespace dropped.

Goal achieved, in a supported way:

SQL> select owner,index_name,object_id,auto,tablespace_name from dba_indexes natural left outer join (select owner index_owner,object_name index_name,object_id from dba_objects where object_type='INDEX') where index_name like 'SYS_AI%';
OWNER           INDEX_NAME OBJECT_ID AUTO TABLESPACE_NAME
_____ ____________________ _________ ____ _______________
ADMIN SYS_AI_gg1ctjpjv92d5 73192 YES AITBS

“_optimizer_use_auto_indexes”=OFF

Finally, if I don’t want to use the AUTO indexes, I don’t have to drop them. There’s a parameter to disable the use of them.

Here is a query using my AUTO index:

SQL> select count(*) from admin.words where sound='H400';
COUNT(*)
___________
152
SQL> select * from dbms_xplan.display_cursor(format=>'allstats last');
PLAN_TABLE_OUTPUT
____________________________________________________________________
SQL_ID bdbr7vnx88x7z, child number 0
-------------------------------------
select count(*) from admin.words where sound='H400'
Plan hash value: 335171867
-------------------------------------------------------------------                                                                                                                 | Id  | Operation         | Name                 |A-Rows| Buffers |                                                                                                                 -------------------------------------------------------------------                                                                                                                 |   0 | SELECT STATEMENT  |                      |     1|       3 |                                                                                                                 |   1 |  SORT AGGREGATE   |                      |     1|       3 |                                                                                                                 |*  2 |   INDEX RANGE SCAN| SYS_AI_gg1ctjpjv92d5 |   152|       3 |                                                                                                                 -------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SOUND"='H400')

Now, disabling all Auto Index at my session level:

SQL> alter session set "_optimizer_use_auto_indexes"=OFF;
Session altered.
SQL> select count(*) from admin.words where sound='H400';
   COUNT(*)
___________
152
SQL> select * from dbms_xplan.display_cursor(format=>'allstats last +outline');
PLAN_TABLE_OUTPUT
____________________________________________________________________
SQL_ID bdbr7vnx88x7z, child number 1
-------------------------------------
select count(*) from admin.words where sound='H400'
Plan hash value: 1662541906
----------------------------------------------------------------                                                                                                                    | Id  | Operation          | Name  | Starts | A-Rows | Buffers |                                                                                                                    ----------------------------------------------------------------                                                                                                                    |   0 | SELECT STATEMENT   |       |      1 |      1 |    1598 |                                                                                                                    |   1 |  SORT AGGREGATE    |       |      1 |      1 |    1598 |                                                                                                                    |*  2 |   TABLE ACCESS FULL| WORDS |      1 |    152 |    1598 |                                                                                                                    ----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SOUND"='H400')

Do you really want to drop them?

Note that if you drop them, then you probably also want to disable Auto Indexing at all or they will probably re-appear:

exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT');

And there it is your decision to create the indexes or not.

But remember that in theory, the presence of an index should not have bad effects as the optimizer. With correct statistics, the CBO can decide to use it or not. And the Auto Indexing feature has also a way to blacklist some auto-created indexes from some queries if a regression has been encountered.