FUNCTION BASED(함수기반) INDEX 컬럼명 조회

 

인덱스 생성시, 컬럼명을 "가공" 또는" 정렬 문구"를 기입하면 오라클이 내부적으로 FUNCTION-BASED INDEX를 생성한다.

 

그런데 그 이유 때문에, 아래 작업 하다가 애로사항이 발생한다.

 

1) 테이블의 인덱스와 구성컬럼을 일괄적으로 보고 싶은 경우

2) DBA 또는 튜너가 관리 인덱스테이블과 실제 DB에 생성되어 있는 인덱스의 컬럼간 동일여부

확인하고 싶은 경우

 

왜냐하면, 가공 컬럼은 인덱스 구성 컬럼 VIEW에서 "SYS_NC0000$" 형태로 나타나기 때문이다.

이를 해결하기 위해서는 "FUNCTION" or "XML" 처리를 하면 된다.

이번 글은 함수를 별도로 만들지 않고 편한 XML 형태이다.

 

 

[1] 테스트 테이블 & 인덱스 생성

CREATE TABLE TB_FUNC
(
    COL1 VARCHAR2(10)
   ,COL2 VARCHAR2(20)
   ,COL3 VARCHAR2(30)
   ,COL4 VARCHAR2(40)
);

1) 선두 컬럼 COL1에 정렬 문구 기입 
   CREATE INDEX IX_TB_FUNC_X01 ON TB_FUNC ( COL1 DESC, COL2  );
2) COL2 SUBSTR 가공 
   CREATE INDEX IX_TB_FUNC_X02 ON TB_FUNC ( SUBSTR(COL2,1,4) );
3) COL3 DECODE 가공 
   CREATE INDEX IX_TB_FUNC_X03 ON TB_FUNC ( DECODE(COL3,1,4,2,4,5) );

 

 

 

 

[2] 인덱스 조회

: 인덱스 유형이 FUNCTION-BASED INDEX 이다.

SELECT * FROM DBA_INDEXES WHERE INDEX_NAME LIKE 'IX_TB_FUNC%';

 

 

FUNCTION-BASED INDEX

 

 

 

 

[3] 인덱스 컬럼명 조회

: 가공된 컬럼은 SYS_시작하는 컬럼명으로 표현된다. 이 정보로는 작업 중 인덱스 구성 컬럼 확인시 낭패다.

SELECT A.*
  FROM DBA_IND_COLUMNS A 
 WHERE A.INDEX_OWNER = 'DBADM'
   AND A.INDEX_NAME LIKE 'IX_TB_FUNC%' -- 인덱스명 
ORDER BY A.INDEX_NAME, A.COLUMN_POSITION  ;

 

가공된 컬럼명이 SYS_NC0000X$ 형태로 표현된다.

 

 

 

 

[4] 인덱스명 기준으로 컬럼명 가로로 나열하기

: 실제 SYS_로 시작하는 컬럼명의 원본은 DBA_IND_EXPRESSION 뷰에 존재한다.

조인하여 편하게 볼수 있도록 "XML" 로 변환하여 보자

SELECT A.INDEX_OWNER
     , A.INDEX_NAME     
     , LISTAGG( A.COLUMN_NAME_NEW, ',' ) AS COLUMN_LIST
 FROM 
    (
        SELECT A.INDEX_OWNER
             , A.INDEX_NAME
             , A.COLUMN_NAME         
             , CASE WHEN NOT REGEXP_LIKE( A.COLUMN_NAME ,'^SYS.*\$$', 'i') 
                    THEN A.COLUMN_NAME 
                    ELSE REPLACE(EXTRACTVALUE( DBMS_XMLGEN.GETXMLTYPE( 'SELECT COLUMN_EXPRESSION 
                                                                          FROM DBA_IND_EXPRESSIONS 
                                                                         WHERE INDEX_OWNER = '''|| A.INDEX_OWNER ||''' 
                                                                           AND INDEX_NAME = '''|| A.INDEX_NAME ||''' 
                                                                           AND COLUMN_POSITION = '''|| A.COLUMN_POSITION ||'''' ), '//text()'),'"','') 
                         || DECODE( A.DESCEND, 'DESC', ' DESC' )
               END COLUMN_NAME_NEW                     
          FROM DBA_IND_COLUMNS A 
         WHERE A.INDEX_OWNER = 'DBADM' -- 소유자 
           AND A.INDEX_NAME LIKE 'IX_TB_FUNC%' -- 인덱스명 
        ORDER BY A.INDEX_NAME, A.COLUMN_POSITION   
    ) A 
GROUP BY A.INDEX_OWNER, A.INDEX_NAME
ORDER BY A.INDEX_NAME ;

 

아래 인덱스 생성문에 기입한 컬럼명과 동일한 조회가 됨

 

 

★ 참고 - 인덱스 생성문

CREATE INDEX IX_TB_FUNC_X01 ON TB_FUNC ( COL1 DESC, COL2 );

CREATE INDEX IX_TB_FUNC_X02 ON TB_FUNC ( SUBSTR(COL2,1,4) );

CREATE INDEX IX_TB_FUNC_X03 ON TB_FUNC ( DECODE(COL3,1,4,2,4,5) );

 

 

 

[5] 왜 XMLTYPE으로 변환을 해야 할까?

: DBA_IND_EXPRESSIONS의 "COLUMN_EXPRESSION" 컬럼이 LONG TYPE이기 때문이다.

VARCHAR2 형태와 직접적으로 연결 또는 동일선상에서 작업 처리가 불가능하다.

 

예를 들면 아래와 같이 NVL 함수로 처리를 한다면 다음과 같은 에러를 만난다.

예시)  NVL(B.COLUMN_EXPRESSION, A.COLUMN_NAME))
ORA-00932: inconsitent datatypes: expected LONG got CHAR

 

COLUMN_EXPRESSION LONGTYPE