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

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')

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 함수형 안덱스가 포함됨 전체 인덱스 컬럼 추출[편집]