인덱스 생성 추출 함수인덱스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 함수 인덱스가 포함 된 인덱스 추출 생성[편집]
- “컬럼 desc” 생성시 Function-Based Index 로 됨
- 함수형 인덱스 정보는 all_ind_expressions 뷰 column_expression 컬럼 참조
- 문제는 column_expression 컬럼 타입이 Long 형임
- 오라클에서 Long => varchar 로 변경하는 뽀족한 수가 없음
- 그래서 오라클 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')
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
</source>