인덱스 생성 추출 함수인덱스

1 함수 인덱스가 포함 된 인덱스 추출 생성[편집]

“컬럼 desc” 생성시 Function-Based Index 로 됨
함수형 인덱스 정보는 all_ind_expressions 뷰 column_expression 컬럼 참조
1. 문제는 column_expression 컬럼 타입이 Long 형임
2. 오라클에서 Long => varchar 로 변경하는 뽀족한 수가 없음
3. 그래서 오라클 xmltable 이용하여 문자형으로 추출함 (아래 sql 참조)

1.1 함수형 인덱스 조회[편집]

all_ind_expressions
dba_ind_expressions

SELECT *
FROM XMLTABLE(
        '/ROWSET/ROW'
        PASSING (SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT * FROM ALL_IND_EXPRESSIONS
                                                 WHERE INDEX_NAME = ''XDB$COMPLEX_TYPE_AK''')
                   FROM DUAL)
        COLUMNS INDEX_OWNER VARCHAR2(30) PATH 'INDEX_OWNER'
              , INDEX_NAME VARCHAR2(30) PATH 'INDEX_NAME'
              , TABLE_OWNER VARCHAR2(30) PATH 'TABLE_OWNER'
              , TABLE_NAME VARCHAR2(30) PATH 'TABLE_NAME'
              , COLUMN_EXPRESSION VARCHAR2(4000) PATH 'COLUMN_EXPRESSION'
              , COLUMN_POSITION NUMBER PATH 'COLUMN_POSITION' 
              )

1.2 함수형 인덱스가 포함됨 전체 인덱스 컬럼 추출[편집]

컬럼 desc 생성시 Function-Based Index 로 됨
아래 예제는 컬럼에 desc 로 생성한 인덱스 조회 예시임.

WITH FNC_IND_COLS AS (
                      SELECT *
                        FROM XMLTABLE(
                                '/ROWSET/ROW'
                                PASSING (SELECT DBMS_XMLGEN.GETXMLTYPE('SELECT * FROM ALL_IND_EXPRESSIONS
                                                                         WHERE INDEX_NAME = ''XDB$COMPLEX_TYPE_AK''')
                                           FROM DUAL)
                                COLUMNS INDEX_OWNER VARCHAR2(30) PATH 'INDEX_OWNER'
                                      , INDEX_NAME VARCHAR2(30) PATH 'INDEX_NAME'
                                      , TABLE_OWNER VARCHAR2(30) PATH 'TABLE_OWNER'
                                      , TABLE_NAME VARCHAR2(30) PATH 'TABLE_NAME'
                                      , COLUMN_EXPRESSION VARCHAR2(4000) PATH 'COLUMN_EXPRESSION'
                                      , COLUMN_POSITION NUMBER PATH 'COLUMN_POSITION' 
                                      )
                    )

SELECT A.OWNER , A.TABLE_NAME , A.INDEX_NAME
     , ( SELECT LISTAGG(COLUMN_NAME,' , ')  WITHIN GROUP(ORDER BY COLUMN_POSITION)
           FROM (
                 SELECT A.INDEX_NAME 
                      , A.INDEX_OWNER
                      , CASE A.DESCEND WHEN 'DESC' THEN B.COLUMN_EXPRESSION || ' DESC'
                        ELSE A.COLUMN_NAME
                   FROM DBA_IND_COLUMNS A
                      , FNC_IND_COLS B
                  WHERE REGEXP_LIKE(A.INDEX_OWNER,'EEDADM|CICADM')
                   AND A.INDEX_OWNER = B.INDEX_OWNER(+)
                   AND A.INDEX_NAME  = B.INDEX_NAME(+)
                   AND A.TABLE_OWNER = B.TABLE_OWNER(+)
                   AND A.TABLE_NAME  = B.TABLE_NAME(+)
                   AND A.COLUMN_POSION = B.COLUMN_POSITION(+)
                ) S
          WHERE S.INDEX_NAME = A.INDEX_NAME
            AND S.INDEX_OWNER = A.OWNER
          GROUP BY INDEX_OWNER,INDEX_NAME
       ) COLS
  FROM DBA_INDEXES A
 WHERE REGEXP_LIKE(A.OWNER,'DBCAFE')
   AND NOT REGEXP_LIKE(INDEX_NAME,'^SYS\_')
 ORDER BY TABLE_NAME
) A

인덱스 생성 추출 함수인덱스

DB CAFE

1 함수 인덱스가 포함 된 인덱스 추출 생성[편집]

1.1 함수형 인덱스 조회[편집]

1.2 함수형 인덱스가 포함됨 전체 인덱스 컬럼 추출[편집]