方法一：
SELECT t1.table_name, t1.index_name, wmsys.wm_concat(t2.column_name) column_name
from user_indexes t1, user_ind_columns t2
where t1.table_name = t2.table_name
and t1.index_name = t2.index_name
group by t1.table_name, t1.index_name;
方法二：
SELECT t1.table_name, t1.index_name, SUBSTR (MAX (SYS_CONNECT_BY_PATH (t1.column_name, ';')), 2) column_name
FROM (SELECT a.table_name, a.index_name, a.column_name, ROW_NUMBER () OVER (PARTITION BY a.index_name ORDER BY a.column_name) rn
FROM user_ind_columns a) t1
START WITH t1.rn = 1
CONNECT BY t1.index_name = PRIOR t1.index_name AND t1.rn - 1 = PRIOR t1.rn
GROUP BY t1.table_name, t1.index_name;
|
