从系统视图中获取索引对应的列（转换成一条记录）

SELECT TABLE_NAME,
       INDEX_NAME,INDEX_COLUMNS
FROM (      
SELECT TABLE_NAME,
       INDEX_NAME,
       DECODE(LV, MAX(LV) OVER(PARTITION BY INDEX_NAME), INDEX_COLUMNS, '') INDEX_COLUMNS
  FROM (SELECT TABLE_NAME,
               INDEX_NAME,
               LEVEL LV,
               LTRIM(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','), ',') INDEX_COLUMNS
          FROM (SELECT TABLE_NAME,
                       INDEX_NAME,
                       COLUMN_NAME,
                       ROW_NUMBER() OVER(PARTITION BY TABLE_NAME, INDEX_NAME ORDER BY COLUMN_NAME) PNO,
                       ROW_NUMBER() OVER(PARTITION BY TABLE_NAME, INDEX_NAME ORDER BY COLUMN_NAME) - 1 SNO
                  FROM USER_IND_COLUMNS)
       
        CONNECT BY PRIOR INDEX_NAME = INDEX_NAME
               AND PRIOR TABLE_NAME = TABLE_NAME
               AND PRIOR PNO = SNO)

) WHERE INDEX_COLUMNS IS NOT NULL AND TABLE_NAME = UPPER('table_name')

 

在这个oracle层查询中，最讨厌的问题是如何取出所有的列，如果只是简单的对循环嵌套出来的列取最大值，是得不到相应结果的，只能多层嵌套了。各位过客有好的想法，尽可提出哦，多多讨论！