Oracle 19c Automatic Indexing: Dropping Automatic Indexes (Fall Dog Bombs The Moon) May 12, 2020
Posted by Richard Foote in 19c, 19c New Features, Automatic Indexing, Drop Index, Index Rebuild, Oracle Indexes.3 comments
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 🙂
Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part II (Stay) May 6, 2020
Posted by Richard Foote in 19c, 19c New Features, Add Column To Existing Index, Automatic Indexing, Oracle Indexes.3 comments
In my previous post, I discussed how Automatic Indexing did not recognise there was already an existing logically equivalent manually created index and so created effectively a redundant Automatic Index.
I also discussed previously how Automatic Indexing was clever enough to logically add new columns to existing Automatic Indexes if it determined such a new index can be used effectively for both previous and new workloads.
In this post, how will Automatic Indexing handle the scenario if a previously manually created index could also potentially be improved by adding a new column.
I’ll start by creating a table similar to my previous post but with more distinct values for the CODE3 column such that the test query will be more selective and so make the CBO favour the use of an index (Note: all examples are run on the OLTP Autonomous Database Cloud Service and hence the odd parallel execution plans):
SQL> create table major_tom6 (id number, code1 number, code2 number, code3 number, name varchar2(42)); Table created. SQL> insert into major_tom6 select rownum, mod(rownum, 1000)+1, ceil(dbms_random.value(0, 100)), ceil(dbms_random.value(0, 100)), 'David Bowie' from dual connect by level = 10000000; 10000000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'MAJOR_TOM6'); PL/SQL procedure successfully completed.
I’ll now manually create an index for BOTH combinations of the CODE2, CODE3 columns:
SQL> create index major_tom6_code2_code3_i on major_tom6(code2, code3); Index created. SQL> create index major_tom6_code3_code2_i on major_tom6(code3, code2); Index created. SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='MAJOR_TOM6'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- MAJOR_TOM6_CODE2_CODE3_I NO NO VISIBLE DISABLED VALID 10000000 23697 9890973 MAJOR_TOM6_CODE3_CODE2_I NO NO VISIBLE DISABLED VALID 10000000 23697 9890973
If I now run the following query:
SQL> select * from major_tom6 where code2=42 and code3=42;
983 rows selected.
Execution Plan
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 28000 | 997 (1)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1000 | 28000 | 997 (1)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| MAJOR_TOM6 | 1000 | 28000 | 997 (1)| 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 1000 | | 5 (0)| 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1000 | | 5 (0)| 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | MAJOR_TOM6_CODE2_CODE3_I | 1000 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("CODE2"=42 AND "CODE3"=42)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
971 consistent gets
0 physical reads
0 redo size
27836 bytes sent via SQL*Net to client
1303 bytes received via SQL*Net from client
67 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
983 rows processed
The CBO favours the use of an index as with just 983 rows returned from a 10M row table, the index is the cheaper access method.
If I now run the following SQL which also includes the more selectively CODE1 column predicate as well (which returns just 1 row):
SQL> select * from major_tom6 where code1=42 and code2=42 and code3=42;
Execution Plan
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 997 (1)| 00:00:01 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 28 | 997 (1)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| MAJOR_TOM6 | 1 | 28 | 997 (1)| 00:00:01 |
| 4 | BUFFER SORT | | | | | |
| 5 | PX RECEIVE | | 1000 | | 5 (0)| 00:00:01 |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1000 | | 5 (0)| 00:00:01 |
| 7 | PX SELECTOR | | | | | |
|* 8 | INDEX RANGE SCAN | MAJOR_TOM6_CODE2_CODE3_I | 1000 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CODE1"=42)
8 - access("CODE2"=42 AND "CODE3"=42)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
971 consistent gets
0 physical reads
0 redo size
867 bytes sent via SQL*Net to client
588 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
The CBO again uses the same index based on columns CODE2, CODE3 as this has already been proven to be more efficient than a FTS. However, an index that also included the CODE1 column would be even more efficient as the CBO could simply use this index to fetch just the row(s) of interest, without having to perform the unnecessary filtering on the CODE1 column.
So what does Automatic Indexing do in this scenario? If we look at the corresponding Auto Indexing Report:
INDEX DETAILS ------------------------------------------------------------------------------- The following indexes were created: *: invisible --------------------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | --------------------------------------------------------------------------------------- | BOWIE | MAJOR_TOM6 | SYS_AI_4nc6g08bw8db7 | CODE2,CODE3,CODE1 | B-TREE | NONE | ---------------------------------------------------------------------------------------
We notice Auto Indexing has created a new index based on columns CODE2, CODE3, CODE1, however it has NOT dropped any indexes.
If we look at the Verification section of the Auto Indexing Report:
VERIFICATION DETAILS
-------------------------------------------------------------------------------
The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : BOWIE
SQL ID : 93zw1kj4n43n9
SQL Text : select * from major_tom6 where code1=42 and code2=42 and code3=42
Improvement Factor : 972.2x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 159122 1240
CPU Time (s): 70379 1320
Buffer Gets: 10698 4
Optimizer Cost: 997 4
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 11 1
Executions: 11 1
We can see the index was created because of a 972.2x improvement in the performance of the SQL query I ran.
If we look at the details of the indexes that now exist on the table:
SQL> select index_name, auto, constraint_index, visibility, compression, status, num_rows, leaf_blocks, clustering_factor from user_indexes where table_name='MAJOR_TOM6'; INDEX_NAME AUT CON VISIBILIT COMPRESSION STATUS NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR ------------------------- --- --- --------- ------------- -------- ---------- ----------- ----------------- MAJOR_TOM6_CODE2_CODE3_I NO NO VISIBLE DISABLED VALID 10000000 23556 9890973 MAJOR_TOM6_CODE3_CODE2_I NO NO VISIBLE DISABLED VALID 10000000 24029 9890973 SYS_AI_4nc6g08bw8db7 YES NO VISIBLE DISABLED VALID 10000000 29125 9999444 SQL> select index_name, column_name, column_position from user_ind_columns where table_name='MAJOR_TOM6' order by index_name, column_position; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------- -------------------- --------------- MAJOR_TOM6_CODE2_CODE3_I CODE2 1 MAJOR_TOM6_CODE2_CODE3_I CODE3 2 MAJOR_TOM6_CODE3_CODE2_I CODE3 1 MAJOR_TOM6_CODE3_CODE2_I CODE2 2 SYS_AI_4nc6g08bw8db7 CODE2 1 SYS_AI_4nc6g08bw8db7 CODE3 2 SYS_AI_4nc6g08bw8db7 CODE1 3
We notice a couple of key points.
Firstly, even though the previously created manual index on the columns (CODE2, CODE3) is now totally redundant because it has the same column list as the leading columns of the newly created Auto Index based on the columns (CODE2, CODE3, CODE1), Auto Indexing does NOT automatically drop the manually created index.
Auto Indexing ONLY automatically drops and logically recreates Auto Indexes.
Secondly, Auto Indexing is certainly aware of the previous workload because it has created the new Auto Index with the column list (CODE2, CODE3, CODE1) and NOT in the default CODE1, CODE2, CODE3 column order (as defined in the table definition).
This suggests Auto Indexing is indeed trying to create a new index that is able to cater for all known SQL workloads (predicates on just the CODE2, CODE3 columns and predicates on columns CODE1, CODE2, CODE3).
However, Auto Indexing does not (yet) have the capability to logically modify or drop obviously redundant manually created indexes (it can only do so on previously created Auto Indexes). This is likely one of the reasons why Oracle has provided us with the DROP_SECONDARY_INDEXES procedure in order to get rid of all those annoying manually created secondary indexes that can get in the way of an optimal indexing strategy.
More on DROP_SECONDARY_INDEXES in future posts.
Richard Foote's Oracle Blog 