Oracle 当索引为DESC时获得索引列的名称

这是一个容易被忽视的问题。

[sql] view plain copy

 

1. -- 建表和索引  
2. CREATE TABLE t1  
3. (  
4.    a   INT,  
5.    b   INT  
6. );  
7.   
8. CREATE INDEX IDX1 ON T1 (a DESC, b DESC);  
9. CREATE INDEX IDX2 ON T1 (a, b);  
10. CREATE INDEX IDX3 ON T1 (a ASC, b DESC);  
11.   
12. -- 直接查询user_ind_columns  
13. SELECT index_name, column_name, descend  
14.   FROM user_ind_columns  
15.  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

 

1. -- 查询user_ind_expressions  
2. SELECT *  
3.   FROM user_ind_expressions  
4.  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

 

1. CREATE OR REPLACE FUNCTION long_2_varchar (  
2.    p_index_name        IN user_ind_expressions.index_name%TYPE,  
3.    p_table_name        IN user_ind_expressions.table_name%TYPE,  
4.    p_COLUMN_POSITION   IN user_ind_expressions.table_name%TYPE)  
5.    RETURN VARCHAR2  
6. AS  
7.    l_COLUMN_EXPRESSION   LONG;  
8. BEGIN  
9.    SELECT COLUMN_EXPRESSION  
10.      INTO l_COLUMN_EXPRESSION  
11.      FROM user_ind_expressions  
12.     WHERE     index_name = p_index_name  
13.           AND table_name = p_table_name  
14.           AND COLUMN_POSITION = p_COLUMN_POSITION;  
15.   
16.    RETURN SUBSTR (l_COLUMN_EXPRESSION, 1, 4000);  
17. END;  
18. /  
19.   
20. -- 现在可以查询列名了  
21.   SELECT a.INDEX_NAME,  
22.          REPLACE (  
23.             DECODE (  
24.                descend,  
25.                'DESC', long_2_varchar (b.index_name,  
26.                                        b.table_NAME,  
27.                                        b.COLUMN_POSITION),  
28.                a.column_name),  
29.             '"',  
30.             '')  
31.             COLUMN_NAME,  
32.          a.COLUMN_POSITION,  
33.          DESCEND  
34.     FROM user_ind_columns a  
35.          LEFT JOIN user_ind_expressions b  
36.             ON a.index_name = b.index_name AND a.table_name = b.table_name  
37.    WHERE a.table_name = 'T1'  
38. ORDER BY INDEX_NAME, column_position;  
39.   
40. -- 再行列转置把一个索引的多行转成一行  
41.   SELECT INDEX_NAME,  
42.          MAX (DECODE (COLUMN_POSITION, 1, COLUMN_NAME || ' ' || DESCEND, NULL))  
43.             c1,  
44.          MAX (DECODE (COLUMN_POSITION, 2, COLUMN_NAME || ' ' || DESCEND, NULL))  
45.             c2,  
46.          MAX (DECODE (COLUMN_POSITION, 3, COLUMN_NAME || ' ' || DESCEND, NULL))  
47.             c3,  
48.          MAX (DECODE (COLUMN_POSITION, 4, COLUMN_NAME || ' ' || DESCEND, NULL))  
49.             c4,  
50.          MAX (DECODE (COLUMN_POSITION, 5, COLUMN_NAME || ' ' || DESCEND, NULL))  
51.             c5  
52.     FROM (  SELECT a.INDEX_NAME,  
53.                    REPLACE (  
54.                       DECODE (  
55.                          descend,  
56.                          'DESC', long_2_varchar (b.index_name,  
57.                                                  b.table_NAME,  
58.                                                  b.COLUMN_POSITION),  
59.                          a.column_name),  
60.                       '"',  
61.                       '')  
62.                       COLUMN_NAME,  
63.                    a.COLUMN_POSITION,  
64.                    DESCEND  
65.               FROM user_ind_columns a  
66.                    LEFT JOIN  
67.                    user_ind_expressions b  
68.                       ON     a.index_name = b.index_name  
69.                          AND a.table_name = b.table_name  
70.              WHERE a.table_name = 'T1'  
71.           ORDER BY INDEX_NAME, column_position)  
72. GROUP BY INDEX_NAME;