实验数据初始化:
复制代码 代码如下:
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;