"인덱스 생성 추출 함수인덱스"의 두 판 사이의 차이
DB CAFE
(→함수형 안덱스가 포함됨 전체 인덱스 컬럼 추출) |
|||
74번째 줄: | 74번째 줄: | ||
</source> | </source> | ||
+ | [[category:oracle]] |
2024년 1월 2일 (화) 00:13 기준 최신판
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'
, 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