关于19c自动索引的名字

关于19c自动索引的名字

版权声明：本文为Buddy Yuan原创文章，转载请注明出处。原文地址：关于19C自动索引的名字
在Oracle 19c中，自动索引创建的名字一般会叫“SYS_AI_”+一串HASH数字，这个名字一般肉眼不好分辨。DBA_INDEXES中有一个AUTO列来标记自动创建的索引。这个后面的名称就像SQL_ID一样，即使删除并重新创建，相同的索引（在相同的表和列上）将始终具有相同的名称，即使我们在其他数据库中创建时也会是这个名字。

首先我们来查一下，我们自动索引的名字，通过auto=yes来查看。

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 AUT
------------------------------ ------------------------------ ---------- ---
TEST                           SYS_AI_3y9qptbsup78y                73126 YES

在SQLDeveloper通过DDL命令，可以查看到创建的语法。

SQL> ddl "SYS_AI_3y9qptbsup78y"

  CREATE INDEX "TEST"."SYS_AI_3y9qptbsup78y" ON "TEST"."TEST" ("ID1") AUTO 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 INVISIBLE COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "AUTOINDEX_T1"  ;

这里语法中，可以看到schema名字，表名字，列名字。然后我们可以通过SYS_OP_COMBINED_HASH函数来计算出hash值。这里一定要注意第三列是带””的，和上面的创建索引的地方对应。

SQL> select SYS_OP_COMBINED_HASH('TEST','TEST','"ID1"') hash from dual;

                 HASH
---------------------
  4550559541119065374

可以使用下列函数，参考下列地址https://nenadnoveljic.com/blog/converting-hash_value-to-sql_id/，把上述数字转换成base32的表示方式。

SQL> select &&d base10,
  2      listagg(
  3          substr(
  4            case &&b when 32 then 
  5              '0123456789abcdfghjkmnpqrstuvwxyz'
  6            else
  7              '0123456789abcdefghjkmnpqrstuvwxyz'
  8            end,
  9            mod(
 10              trunc(&&d/power(&&b,level-1)),
 11              &&b
 12            ) + 1 ,
 13            1
 14          ) 
 15      ) within group (order by level desc) base&&b 
 16    from dual 
 17    connect by level <= ceil(log(&&b,&&d+1))
 18  ;
old  1: select &&d base10,
new  1: select 4550559541119065374 base10,
old  4:           case &&b when 32 then
new  4:           case 32 when 32 then
old  10:             trunc(&&d/power(&&b,level-1)),
new  10:             trunc(4550559541119065374/power(32,level-1)),
old  11:             &&b
new  11:             32
old  15:     ) within group (order by level desc) base&&b
new  15:     ) within group (order by level desc) base32
old  17:   connect by level <= ceil(log(&&b,&&d+1))
new  17:   connect by level <= ceil(log(32,4550559541119065374+1))

              BASE10 BASE32       
-------------------- -------------
 4550559541119065374 3y9qptbsup78y

通过转换成Base32，我们可以发现这一串数字就是3y9qptbsup78y，和我们创建出来的索引SYS_AI_3y9qptbsup78y吻合。当然使用sys登录的时候还可以使用SQL

SQL> select ltrim(SYS.DBMS_SPD_INTERNAL.UB8_TO_SQLID(4550559541119065374),'0') from dual;
LTRIM(SYS.DBMS_SPD_INTERNAL.UB8_TO_SQLID(4550559541119065374),'0')
--------------------------------------------------------------------------------
3y9qptbsup78y

可以使用下列语法查询

 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_OWNER='TEST'
 group by table_owner,table_name,index_name
 ); 
 /

TABLE_OWNER                    TABLE_NAME                               COLS                                     AI_INDEX_NAME
------------------------------ ---------------------------------------- ---------------------------------------- ----------------------------------------
TEST                           TEST                                     "ID1"                                    SYS_AI_3y9qptbsup78y