How 19c Auto Indexes are named?

As a SQL_ID-like base 32 hash on table owner, name, column list

The indexes created by the 19c Auto Indexing feature have a generated name like: “SYS_AI_gg1ctjpjv92d5”. I don’t like to rely on the names: there’s an AUTO column in DBA_INDEXES to flag the indexes created automatically.

But, one thing is very nice: the name is not random. The same index (i.e on same table and columns) will always have the same name. Even when dropped and re-created. Even when created in a different database. This is very nice to follow them (like quickly searching in my e-mails and find the same issue encountered in another place). Like we do with SQL_ID.

Yes, the generation of the name is similar to SQL_ID as it is the result of a 64-bit number from a hash function, displayed in base 32 with alphanumeric characters.

The hash function is SYS_OP_COMBINED_HASH applied on the table owner, table name and column list. Yes, the same function that is used by extended statistics column groups. Why not? All that is developed by the CBO team. They re-use their own functions.

So, from the previous post, I have the following indexes created by Auto Index feature:

SQL> select owner,index_name,object_id,auto 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
________ _______________________ ____________ _______
ADMIN    SYS_AI_gg1ctjpjv92d5          73,192 YES
ADMIN    SYS_AI_8u25mzzr6xw1v          73,231 YES
ADMIN    SYS_AI_26rdw45ph3hag          73,232 YES

Let’s take the first one.

SQL> ddl "SYS_AI_gg1ctjpjv92d5"
CREATE INDEX "ADMIN"."SYS_AI_gg1ctjpjv92d5" 
ON "ADMIN"."WORDS" ("SOUND") AUTO;

Here is the hash from table owner and name (without quotes) and column list (quoted):

SQL> select SYS_OP_COMBINED_HASH('ADMIN','WORDS','"SOUND"')
     from dual;
   SYS_OP_COMBINED_HASH('ADMIN','WORDS','"SOUND"')
__________________________________________________
                              17835830731812932005

And I’m using Nenad Noveljic conversion to base 32 from:

Converting HASH_VALUE to SQL_ID - All-round Database Topics

gg1ctjpjv92d5 is the base 32 hash value for this table/columns definition and the Auto Index created was: SYS_AI_gg1ctjpjv92d5

If you are connected as SYS, there’s an internal function for this base32 conversion (the one from SQL Plan Directives used to store the SQL_ID of the Dynamic Sampling query since 12cR2, which caches the result of dynamic sampling in the SPD rather than using the Result Cache as in 12cR2):

SQL> select ltrim(SYS.DBMS_SPD_INTERNAL.UB8_TO_SQLID( 17835830731812932005 ),'0') from dual;
   LTRIM(SYS.DBMS_SPD_INTERNAL.UB8_TO_SQLID(17835830731812932005),'0')
____________________________________________________________________
gg1ctjpjv92d5

For compound indexes, here is an example:

SQL> ddl "SYS_AI_26rdw45ph3hag"
CREATE INDEX "ADMIN"."SYS_AI_26rdw45ph3hag" 
ON "ADMIN"."WORDS" ("CAP", "LOW", "UPP") AUTO;

The hash of columns is calculated on a the space-free comma-separated quoted column list:

SQL> select SYS_OP_COMBINED_HASH
     ('ADMIN','WORDS','"CAP","LOW","UPP"')
     from dual;
SYS_OP_COMBINED_HASH('ADMIN','WORDS','"SOUND"')
__________________________________________________
                           2548399815876788559
SQL> select ltrim(SYS.DBMS_SPD_INTERNAL.UB8_TO_SQLID( 2548399815876788559 ),'0') from dual;
      LTRIM(SYS.DBMS_SPD_INTERNAL.UB8_TO_SQLID(2548399815876788559),'0')
____________________________________________________________________
26rdw45ph3hag

Here it is. The hash is 26rdw45ph3hag on the columns indexed by SYS_AI_26rdw45ph3hag.

Back with Nenad function, here is how to generate the AI name for any existing index:

with function TO_SQLID(n number) return varchar2 as
  --https://nenadnoveljic.com/blog/converting-hash_value-to-sql_id/
  base32 varchar2(16);
 begin
 select
    listagg(substr('0123456789abcdfghjkmnpqrstuvwxyz',
                   mod(trunc(n/power(32,level-1)),32)+1,1)
    ) within group (order by level desc) into base32
  from dual
  connect by level <= ceil(log(32,n+1));
  return base32;
end;
select table_owner,table_name,cols,'SYS_AI_'||
 to_sqlid(sys_op_combined_hash(table_owner,table_name,cols))
 AI_INDEX_NAME
from (
 select table_owner,table_name,index_name
 ,listagg('"'||column_name||'"',',')
  within group(order by column_position) cols
from dba_ind_columns
--where index_name like 'SYS_AI%'
group by table_owner,table_name,index_name
);

Of course, when the index is created its name and definition is accessible. But being sure that the name is a predictable hash will help to manage Automatic Indexes.

With this post and the previous one, you have all information to rename a manually created index to am Auto Index one and set the AUTO flag. Of course, don’t do that. Auto Index keeps metadata about the SQL Tuning Sets and Auto Index DDL actions and faking the AUTO flag will make all that inconsistent.

Those examples on this WORDS tables comes from the demo I’m preparing for my Oracle Open World session on Auto Index:

Oracle Database 19c Automatic Indexing Demystified
Thursday, Sept. 19th, 02:15 PM in Moscone West — Room 3020A

Also many sessions on the same topic:

Session Catalog