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

所属分类: 数据库 / oracle 阅读数: 403
收藏 0 赞 0 分享

实验数据初始化:

复制代码 代码如下:

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;


更多精彩内容其他人还在看

ORACLE中如何找到未提交事务的SQL语句详解

这篇文章主要给大家介绍了关于ORACLE中如何找到未提交事务的SQL语句,文中通过示例代码介绍的非常详细,对大家学习或者使用ORACLE具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
收藏 0 赞 0 分享

Oracle分组函数之ROLLUP的基本用法

这篇文章主要给大家介绍了关于Oracle分组函数之ROLLUP的基本用法,文中通过示例代码介绍的非常详细,对大家学习或者使用Oracle数据库具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
收藏 0 赞 0 分享

Oracle表空间设置和管理浅析

这篇文章主要给大家介绍了关于Oracle表空间设置和管理的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Oracle具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
收藏 0 赞 0 分享

Oracle使用MyBatis中RowBounds实现分页查询功能

这篇文章主要介绍了Oracle使用MyBatis中RowBounds实现分页查询 ,本文通过实例代码给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
收藏 0 赞 0 分享

Oracle自定义脱敏函数的代码详解

这篇文章主要介绍了Oracle自定义脱敏函数的实例代码,代码简单易懂,非常不错,具有一定的参考借鉴价值 ,需要的朋友可以参考下
收藏 0 赞 0 分享

oracle查询截至到当前日期月份所在年份的所有月份

这篇文章主要介绍了oracle查询截至到当前日期月份所在年份的所有月份,本文通过代码给大家介绍的非常详细,具有一定的参考借鉴价值 ,需要的朋友可以参考下
收藏 0 赞 0 分享

oracle数据库导入.dmp脚本的sql 语句

这篇文章主要介绍了oracle数据库导入.dmp脚本的sql语句,非常不错,具有一定的参考借鉴价值 ,需要的朋友可以参考下
收藏 0 赞 0 分享

oracle通过1条语句插入多个值的方法示例

这篇文章主要给大家介绍了关于oracle通过1条语句插入多个值的方法,文中通过示例代码介绍的非常详细,对大家学习或者使用oracle具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
收藏 0 赞 0 分享

oracle导入导出表时因一个分号引发的惨案

这篇文章主要给大家介绍了关于oracle导入导出表时因一个分号引发的惨案,文中通过示例代码介绍的非常详细,对大家学习或者使用oracle具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
收藏 0 赞 0 分享

Oracle创建设置查询权限用户的方法

这篇文章主要给大家介绍了关于Oracle创建设置查询权限用户的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Oracle具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
收藏 0 赞 0 分享
查看更多