Oracle中实现MySQL show index from table命令SQL脚本分享

实验数据初始化:

create table t as select * from hr.employees;
create index inx_t1 on t(employee_id,first_name desc,last_name);
create index inx_t2 on t(job_id,hire_date);

显示该表所有索引的信息。

以dba登录

set linesize 300;
set pagesize 100;
col c1 format a20;
col c2 format a20;
col c3 format a20;
col c4 format a20;
col c5 format a20;
col index_name format a20;
select index_name,
max(decode(column_position,1,column_name||','||column_length||','||descend,null)) c1,
max(decode(column_position,2,column_name||','||column_length||','||descend,null)) c2,
max(decode(column_position,3,column_name||','||column_length||','||descend,null)) c3,
max(decode(column_position,4,column_name||','||column_length||','||descend,null)) c4,
max(decode(column_position,5,column_name||','||column_length||','||descend,null)) c5
from (
select index_name,column_name,column_length,column_position,descend
from dba_ind_columns
where table_owner='lihuilin'
and table_name='t'
order by index_name,column_position
) group by index_name;

以普通用户登录

set linesize 300;
set pagesize 100;
col c1 format a20;
col c2 format a20;
col c3 format a20;
col c4 format a20;
col c5 format a20;
col index_name format a20;
select index_name,
max(decode(column_position,1,column_name||','||column_length||','||descend,null)) c1,
max(decode(column_position,2,column_name||','||column_length||','||descend,null)) c2,
max(decode(column_position,3,column_name||','||column_length||','||descend,null)) c3,
max(decode(column_position,4,column_name||','||column_length||','||descend,null)) c4,
max(decode(column_position,5,column_name||','||column_length||','||descend,null)) c5
from (
select index_name,column_name,column_length,column_position,descend
from user_ind_columns
where table_name='t'
order by index_name,column_position
) group by index_name;

但是可以看到,以倒序创建的索引字段，都是以sys等命名。

oracle把这种倒序创建的索引字段看成函数索引。

它的信息保存在user_ind_expressions视图。

user_ind_expressions视图的column_expression字段类型是long型。

王工的版本可以解决这个问题

create or replace function long_2_varchar (
   p_index_name in user_ind_expressions.index_name%type,
   p_table_name in user_ind_expressions.table_name%type,
   p_column_position in user_ind_expressions.table_name%type)
   return varchar2
as
   l_column_expression long;
begin
   select column_expression
     into l_column_expression
     from user_ind_expressions
    where index_name = p_index_name
          and table_name = p_table_name
          and column_position = p_column_position;

   return substr (l_column_expression, 1, 4000);
end;
/

set linesize 300;
set pagesize 100;
col c1 format a20;
col c2 format a20;
col c3 format a20;
col c4 format a20;
col c5 format a20;
col index_name format a20;
select index_name,
         max (decode (column_position, 1, column_name || ' ' || descend, null))
            c1,
         max (decode (column_position, 2, column_name || ' ' || descend, null))
            c2,
         max (decode (column_position, 3, column_name || ' ' || descend, null))
            c3,
         max (decode (column_position, 4, column_name || ' ' || descend, null))
            c4,
         max (decode (column_position, 5, column_name || ' ' || descend, null))
            c5
    from ( select a.index_name,
                   replace (
                      decode (
                         descend,
                         'desc', long_2_varchar (b.index_name,
                                                 b.table_name,
                                                 b.column_position),
                         a.column_name),
                      '"',
                      '')
                      column_name,
                   a.column_length,
                   a.column_position,
                   descend
              from user_ind_columns a
                   left join
                   user_ind_expressions b
                      on a.index_name = b.index_name
                         and a.table_name = b.table_name
             where a.table_name = 't'
          order by index_name, column_position)
group by index_name;