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"')
And I’m using Nenad Noveljic conversion to base 32 from:
Converting HASH_VALUE to SQL_ID - All-round Database Topics
The V$SQL.SQL_ID is a 64-bit number in a base32 notation. And the V$SQL.HASH_VALUE stores its least significant 32-bits…
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;
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
2548399815876788559SQL> select ltrim(SYS.DBMS_SPD_INTERNAL.UB8_TO_SQLID( 2548399815876788559 ),'0') from dual;
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
) within group (order by level desc) into base32
connect by level <= ceil(log(32,n+1));
within group(order by column_position) cols
--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:
Also many sessions on the same topic: