欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-01-23 11:30:44
实验数据初始化: 复制代码 代码如下: create table t as select * from hr.employees; create index inx_...

实验数据初始化:

复制代码 代码如下:

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;

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

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

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

它的信息保存在user_ind_expressions视图。

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

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;