ORACLE 查看表索引

== 查看索引列信息

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_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 = 'CM_XX_T_ZC_APP_POS'  
          ORDER BY INDEX_NAME, column_position)  
GROUP BY INDEX_NAME;  

== 根据列位置查找列名

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;