ORACLE INDEX 정보조회 FUNCTION 만들기

인덱스 조회용 쿼리를 필요할때마다 불러와서 실행하기가 불편해서 타이핑이 가능한 수준으로 줄였음 Package와 Function 활용

--실행

SELECT * FROM TABLE(PKG_DBA_REPOSITORY.GET_INDEX_INFO('테이블명'));

ORACLE INDEX 정보조회 FUNCTION 만들기

SELECT * FROM TABLE(PKG_DBA_REPOSITORY.GET_INDEX_INFO('테이블명'));

CREATE OR REPLACE PACKAGE PKG_DBA_REPOSITORY IS
TYPE INDEX_INFO_RECORD IS RECORD
(
INDEX_NAME VARCHAR2(100),
INDEX_TYPE VARCHAR2(100),
PRIMARY_KEY VARCHAR2(100),
UNIQUENESS VARCHAR2(100),
INDEX_POS1 VARCHAR2(100),
INDEX_POS2 VARCHAR2(100),
INDEX_POS3 VARCHAR2(100),
INDEX_POS4 VARCHAR2(100),
INDEX_POS5 VARCHAR2(100),
INDEX_POS6 VARCHAR2(100),
INDEX_POS7 VARCHAR2(100),
INDEX_POS8 VARCHAR2(100),
INDEX_POS9 VARCHAR2(100),
INDEX_POS10 VARCHAR2(100)

);
--Return 하게될 Row type Array
TYPE INDEX_INFO_TABLE IS TABLE OF INDEX_INFO_RECORD;


FUNCTION GET_INDEX_INFO(IN_TABLE_NAME IN VARCHAR2) RETURN INDEX_INFO_TABLE PIPELINED;
END PKG_DBA_REPOSITORY;


CREATE OR REPLACE PACKAGE BODY PKG_DBA_REPOSITORY IS

FUNCTION GET_INDEX_INFO(IN_TABLE_NAME IN VARCHAR2)
RETURN INDEX_INFO_TABLE PIPELINED
IS
v_menu INDEX_INFO_RECORD;
BEGIN
FOR func_cur IN
(
select index_name
,index_type
,decode(constraint_name, null, ' ', 'pk') primary_key
,uniqueness
,nvl(max(a),' ') index_pos1
,nvl(max(b),' ') index_pos2
,nvl(max(c),' ') index_pos3
,nvl(max(d),' ') index_pos4
,nvl(max(e),' ') index_pos5
,nvl(max(f),' ') index_pos6
,nvl(max(g),' ') index_pos7
,nvl(max(h),' ') index_pos8
,nvl(max(i),' ') index_pos9
,nvl(max(j),' ') index_pos10
from
(
select a.index_name
,a.index_type
,a.uniqueness
,c.constraint_name
,DECODE(column_position, 1, column_name, '') a
,DECODE(column_position, 2, column_name, '') b
,DECODE(column_position, 3, column_name, '') c
,DECODE(column_position, 4, column_name, '') d
,DECODE(column_position, 5, column_name, '') e
,DECODE(column_position, 6, column_name, '') f
,DECODE(column_position, 7, column_name, '') g
,DECODE(column_position, 8, column_name, '') h
,DECODE(column_position, 9, column_name, '') i
,DECODE(column_position, 10, column_name, '') j
from all_indexes a, all_ind_columns b, all_constraints c
where a.table_name = IN_TABLE_NAME
and a.table_name = b.table_name
and a.index_name = b.index_name
and a.table_name = c.table_name(+)
and a.index_name = c.constraint_name(+)
and c.constraint_type(+) = 'P'
order by index_name, column_position
)
group by index_name, index_type,constraint_name, uniqueness
order by primary_key desc, index_name
)
LOOP
v_menu.INDEX_NAME := func_cur.index_name;
v_menu.INDEX_TYPE := func_cur.index_type;
v_menu.PRIMARY_KEY := func_cur.primary_key;
v_menu.UNIQUENESS := func_cur.uniqueness;
v_menu.INDEX_POS1 := func_cur.index_pos1;
v_menu.INDEX_POS2 := func_cur.index_pos2;
v_menu.INDEX_POS3 := func_cur.index_pos3;
v_menu.INDEX_POS4 := func_cur.index_pos4;
v_menu.INDEX_POS5 := func_cur.index_pos5;
v_menu.INDEX_POS6 := func_cur.index_pos6;
v_menu.INDEX_POS7 := func_cur.index_pos7;
v_menu.INDEX_POS8 := func_cur.index_pos8;
v_menu.INDEX_POS9 := func_cur.index_pos9;
v_menu.INDEX_POS10 :=func_cur.index_pos10;
PIPE ROW (v_menu);
END LOOP;
END GET_INDEX_INFO;
END PKG_DBA_REPOSITORY;