这是一个容易被忽视的问题。
[sql] view plain copy -- 建表和索引 CREATE TABLE t1 ( a INT, b INT ); CREATE INDEX IDX1 ON T1 (a DESC, b DESC); CREATE INDEX IDX2 ON T1 (a, b); CREATE INDEX IDX3 ON T1 (a ASC, b DESC); -- 直接查询user_ind_columns SELECT index_name, column_name, descend FROM user_ind_columns WHERE table_name = 'T1'; 结果如下： INDEX_NAME COLUMN_NAME DESCEND ------------------------------ ------------------------------ -------- IDX1 SYS_NC00004$ DESC IDX1 SYS_NC00005$ DESC IDX2 A ASC IDX2 B ASC IDX3 A ASC IDX3 SYS_NC00005$ DESC 可见当索引列定义为DESC时，user_ind_columns.COLUMN_NAME并不能正确显示列名。 [sql] view plain copy -- 查询user_ind_expressions SELECT * FROM user_ind_expressions WHERE table_name = 'T1'; 结果如下： INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION ------------------------------ ------------------------------ ------------------------------ --------------- IDX1 T1 "A" 1 IDX1 T1 "B" 2 IDX3 T1 "B" 2 从user_ind_expressions视图可以查询到索引列名。Oracle把DESC定义的索引作为函数索引，所以看到的是加了引号的列名，被当做函数表达式。 从以上分析得知，可以关联user_ind_columns和user_ind_expressions查询出所有索引列名称。这时又遇到一个问题，COLUMN_EXPRESSION列是LONG类型的，不能使用普通的函数进行处理，需要先建立一个转换函数，把LONG转换成VARCHAR。 [sql] view plain copy 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; / -- 现在可以查询列名了 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 = 'T1' ORDER BY INDEX_NAME, column_position; -- 再行列转置把一个索引的多行转成一行 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 = 'T1' ORDER BY INDEX_NAME, column_position) GROUP BY INDEX_NAME;