행위

"인덱스 생성 추출 함수인덱스"의 두 판 사이의 차이

DB CAFE

(새 문서: == 함수 인덱스가 포함 된 인덱스 추출 생성 == # “컬럼 desc” 생성시 Function-Based Index 로 됨 # 함수형 인덱스 정보는 all_ind_expressions 뷰 column_...)
 
(→‎함수형 안덱스가 포함됨 전체 인덱스 컬럼 추출)
28번째 줄: 28번째 줄:
 
* 컬럼 desc 생성시 Function-Based Index 로 됨
 
* 컬럼 desc 생성시 Function-Based Index 로 됨
 
* 아래 예제는 컬럼에 desc 로 생성한 인덱스 조회 예시임.  
 
* 아래 예제는 컬럼에 desc 로 생성한 인덱스 조회 예시임.  
−
<source lang=sql>
+
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'
 +
                                      )
 +
                    )
  
−
with FNC_IND_COLS as (
+
SELECT A.OWNER , A.TABLE_NAME , A.INDEX_NAME
−
select *
+
    , ( SELECT LISTAGG(COLUMN_NAME,' , ')  WITHIN GROUP(ORDER BY COLUMN_POSITION)
−
  from xmltable(
+
          FROM (
−
          '/ROWSET/ROW'
+
                SELECT A.INDEX_NAME
−
          passing (select dbms_xmlgen.getxmltype('select * from all_ind_expressions
+
                      , A.INDEX_OWNER
−
                                                  where index_name = ''XDB$COMPLEX_TYPE_AK''')
+
                      , CASE A.DESCEND WHEN 'DESC' THEN B.COLUMN_EXPRESSION || ' DESC'
−
                    from dual)
+
                        ELSE A.COLUMN_NAME
−
          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'
+
                  FROM DBA_IND_COLUMNS A
−
,column_position number path 'COLUMN_POSITION' )
+
                      , 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
  
−
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>
 
</source>

2023년 12월 14일 (목) 13:14 판

thumb_up 추천메뉴 바로가기

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

  1. “컬럼 desc” 생성시 Function-Based Index 로 됨
  2. 함수형 인덱스 정보는 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

</source>

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=인덱스_생성_추출_함수인덱스&oldid=10461"