행위

ORACLE 인덱스

DB CAFE

1 인덱스

1.1 INDEX 정보


1.1.1 인덱스 관련 뷰

  • DBA|ALL|USER_INDEXES
  • DBA|ALL|USER_IND_COLUMNS

1.1.2 사용자 인덱스 구성 컬럼 정보

현재 사용자의 인덱스와 각 인덱스를 구성하는 컬럼 정보 확인
SELECT A.INDEX_NAME 
     , A.UNIQUENESS 
     , TO_CHAR(COLUMN_POSITION, '999') AS POS 
     , SUBSTRB(COLUMN_NAME, 1, 33) AS COLUMN_NAME 
  FROM USER_INDEXES A 
     , USER_IND_COLUMNS B 
 WHERE A.INDEX_NAME = B.INDEX_NAME 
   AND A.TABLE_OWNER = UPPER('E_LUCIS') 
   AND A.TABLE_NAME = UPPER('&테이블명') 
 ORDER BY 1, 3;

1.1.3 전체 INDEX 보기

DBA권한으로 확인가능하는 전체 인덱스 및 인덱스구성 컬럼 정보 조회
SELECT SUBSTRB(A.TABLE_NAME, 1, 22) AS TABLE_NAME 
     , SUBSTRB(A.INDEX_NAME, 1, 23) AS INDEX_NAME 
     , SUBSTRB(A.UNIQUENESS, 1, 7) AS UNIQUE 
     , TO_CHAR(COLUMN_POSITION, '999') AS POS 
     , SUBSTRB(COLUMN_NAME, 1, 20) AS COLUMN_NAME 
  FROM DBA_INDEXES A 
     , DBA_IND_COLUMNS B 
 WHERE A.INDEX_NAME = B.INDEX_NAME 
   AND A.TABLE_OWNER = B.TABLE_OWNER 
   AND A.TABLE_OWNER = 'E_LUCIS' 
ORDER BY 1, 2, 3;

1.1.4 전체 INDEX 리빌드 대상 조회

-------------------------------------------------------------------------------
-- 인덱스 조회
-- BLEVEL(인덱스깊이) : 4이상이면 REBUILD 고려 -
-- LEAF_BLOCKS(리프블록수) : 4이상이면 REBUILD 고려 -
-------------------------------------------------------------------------------
SELECT
       INDEX_NAME       AS "인덱스명"
     , INDEX_TYPE       AS "인덱스타입"
     , TABLE_OWNER      AS "오너"
     , TABLE_NAME       AS "테이블명"
     , TABLE_TYPE       AS "테이블타입"
     , UNIQUENESS       AS "UNIUE여부"
     , BLEVEL           AS "인덱스깊이"
     , LEAF_BLOCKS      AS "리프블록수"
     , TABLESPACE_NAME  AS "테이블스페이스"
     , INI_TRANS        AS "동시트랜잭션수"                 -- 동시에 엑세스 가능한 트랜잭션의 초기 개수
     , MAX_TRANS        AS "MAX트랜잭션수"                  -- 동시엑세스 가능한 MAX 트랜잭션 수
  FROM USER_INDEXES
 WHERE TABLE_OWNER      = 'SHE'
   AND INDEX_TYPE       = 'NORMAL'
   AND INDEX_NAME       LIKE '%%'
ORDER BY BLEVEL DESC, LEAF_BLOCKS DESC
;

1.1.5 특정 테이블의 인덱스 확인

현재 사용자의 특정 테이블 정보 확인 
SELECT C.TABLE_NAME , C.INDEX_NAME , C.COLUMN_NAME , C.COLUMN_POSITION , T.NUM_ROWS 
  FROM ALL_IND_COLUMNS C 
     , (SELECT TABLE_NAME, NUM_ROWS 
          FROM ALL_TABLES 
         WHERE OWNER = '&사용자' 
           AND TABLE_NAME IN (SELECT TABLE_NAME 
                                FROM USER_TABLES 
                               WHERE TABLE_NAME LIKE:IN_TABLE_NAME || '%'
                             ) 
           AND NUM_ROWS > 0
      ) T 
 WHERE C.TABLE_NAME = T.TABLE_NAME 
 ORDER BY T.NUM_ROWS DESC
     , C.TABLE_NAME
     , C.INDEX_NAME
     , C.COLUMN_POSITION;

1.1.6 인덱스에 대한 컬럼 조회

사용자의 인덱스 컬럼 구성 정보 
SELECT TABLE_NAME , INDEX_NAME , COLUMN_POSITION , COLUMN_NAME 
  FROM USER_IND_COLUMNS 
 ORDER BY TABLE_NAME
        , INDEX_NAME
        , COLUMN_POSITION;

1.1.7 PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기

특정테이블의 PK를 참조하고 있는 외부키(FK) 정보 조회
SELECT C.NAME CONSTRAINT_NAME 
  FROM DBA_OBJECTS A 
     , CDEF$ B 
     , CON$ C 
 WHERE A.OBJECT_NAME = UPPER('&테이블명') 
   AND A.OBJECT_ID = B.ROBJ# 
   AND B.CON# = C.CON#;

1.1.8 중복인덱스 체크

중복된 컬럼으로 사용중인 인덱스 정보 조회 
SELECT O1.NAME || '.' || N1.NAME REDUNDANT_INDEX, O2.NAME || '.' || N2.NAME SUFFICIENT_INDEX 
  FROM SYS.ICOL$ IC1 , SYS.ICOL$ IC2 , SYS.IND$ I1 , SYS.OBJ$ N1 , SYS.OBJ$ N2 , SYS.USER$ O1 , SYS.USER$ O2 
 WHERE IC1.POS# = 1 
   AND IC2.BO# = IC1.BO# 
   AND IC2.OBJ#!= IC1.OBJ# 
   AND IC2.POS# = 1 
   AND IC2.INTCOL# = IC1.INTCOL# 
   AND I1.OBJ# = IC1.OBJ# 
   AND BITAND(I1.PROPERTY, 1) = 0 
   AND (SELECT MAX(POS#) * (MAX(POS#) + 1) / 2 
          FROM SYS.ICOL$ 
         WHERE OBJ# = IC1.OBJ#) = (SELECT SUM(XC1.POS#) 
                                     FROM SYS.ICOL$ XC1 , SYS.ICOL$ XC2 
                                    WHERE XC1.OBJ# = IC1.OBJ# 
                                      AND XC2.OBJ# = IC2.OBJ# 
                                      AND XC1.POS# = XC2.POS# 
                                      AND XC1.INTCOL# = XC2.INTCOL#) 
           AND N1.OBJ# = IC1.OBJ# 
           AND N2.OBJ# = IC2.OBJ# 
           AND O1.USER# = N1.OWNER# 
           AND O2.USER# = N2.OWNER#;

1.1.9 테이블의 PK를 구성하는 컬럼 조회

사용자 테이블의 기본키(PK) 정보
SELECT A.TABLE_NAME, B.CONSTRAINT_NAME, C.COLUMN_NAME 
  FROM USER_TABLES A 
     , USER_CONSTRAINTS B 
     , USER_CONS_COLUMNS C 
 WHERE A.TABLE_NAME = B.TABLE_NAME 
    AND B.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
    AND B.CONSTRAINT_TYPE = 'P';

1.1.10 인덱스의 Delete Space 조회

인덱스의 Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다.
SELECT NAME , LF_ROWS , DEL_LF_ROWS 
     , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%" 
  FROM INDEX_STATS 
 WHERE NAME = UPPER('&INDEX_NAME');

1.1.11 Index가 없는 Table 조회

인덱스 없는 테이블 정보 조회
SELECT OWNER, TABLE_NAME 
  FROM (SELECT OWNER, TABLE_NAME 
          FROM DBA_TABLES 
         MINUS 
        SELECT TABLE_OWNER, TABLE_NAME 
          FROM DBA_INDEXES
       ) 
 WHERE OWNER NOT IN ('SYS', 'SYSTEM') 
 ORDER BY OWNER, TABLE_NAME;

1.2 인덱스 생성

  • 생성시 주의점
  1. 테이블과 인덱스가 같은 테이블스페이스에 있으면 안됨. 성능 저하!, 전용 인덱스 테이블스페이스를 사용할 것!
  2. index 생성시 redolog에도 기록이 되는데 인덱스가 클 경우 시간이 너무 오래걸리기 때문에 생성시

nologging 옵션을 줘서 리빌드 후 alter로 변경.

1.2.1 인덱스 생성문

CREATE INDEX 인덱스명
    ON 테이블명(칼럼명)
PCTFREE *
STORAGE(INITIAL * NEXT * PCTINCREASE * MAXEXTENTS *) 
TABLESPACE T/S명 ;

-- PCTUSED 옵션은 index는 지워지지 않기 때문에 필요 없음.
-- STORAGE 행은 ASSM일 경우 알아서 자동적으로 설정되기 때문에 신경 쓸 필요는 없음.

1.2.2 PRIMARY KEY DISABLE/ENABLE

-- PK DISABLE 
ALTER TABLE TB_PK_TEST
    DISABLE CONSTRAINT PK_PK_TEST;
 
-- PK ENABLE
ALTER TABLE TB_PK_TEST
    ENABLE CONSTRAINT PK_PK_TEST;

1.2.3 PRIMARY KEY 재생성

-- PRIMARY KEY DROP 
ALTER TABLE EMP DROP PRIMARY KEY;
-- PRIMARY KEY 생성 
ALTER TABLE EMP
    ADD CONSTRAINT EMP_PK 
PRIMARY KEY(EMPNO) USING INDEX 
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) 
TABLESPACE USERS;

1.2.4 PK 생성 스크립트 SQL

SELECT 'ALTER TALLE '||OWNER||'.'||TABLE_NAME||' ADD CONSTRAINTS '||CONSTRAINT_NAME||' PRIMARY KEY ('||COLS||');' AS DDL
  FROM (            
        SELECT C.OWNER 
             , C.TABLE_NAME
             , S.CONSTRAINT_NAME
        --    , S.CONSTRAINT_TYPE
             , LISTAGG(C.COLUMN_NAME,',') WITHIN  GROUP (ORDER BY POSITION) COLS
        --   , C.POSITION      
          FROM DBA_CONS_COLUMNS C
             , DBA_CONSTRAINTS S
         WHERE C.CONSTRAINT_NAME = S.CONSTRAINT_NAME 
           AND S.CONSTRAINT_TYPE = 'P'   
           AND C.TABLE_NAME IN (
                 'TB_EMP'
                )
          GROUP BY C.OWNER
              , C.TABLE_NAME
              , S.CONSTRAINT_NAME         
          )

1.2.5 테이블/PK 인덱스 추출 SQL 생성

오라클 DBMS_METADATA.GET_DDL() 내장패키지/함수를 이용한 테이블,인덱스 추출 
sqlplus spool 기능으로 일괄생성하여 추출/백업 스크립트를 편리하게 작성할수 있다.

SELECT OWNER
     , TABLE_NAME     
     , CONSTRAINT_NAME
--     , 'SELECT dbms_metadata.get_ddl(''TABLE'','''||CONSTRAINT_NAME||''','''||OWNER||''') FROM DUAL;'  TABLE_DDL     
     , 'SELECT dbms_metadata.get_ddl(''INDEX'','''||CONSTRAINT_NAME||''','''||OWNER||''') FROM DUAL;'  INDEX_DDL     
--     , LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY POSITION) PK_COLS     
  FROM (      
SELECT A.OWNER
     , A.TABLE_NAME
     , B.CONSTRAINT_NAME
     , C.COLUMN_NAME
     , C.POSITION 
  FROM DBA_TABLES A
     , DBA_CONSTRAINTS B
     , DBA_CONS_COLUMNS C
 WHERE A.TABLE_NAME = B.TABLE_NAME
   AND (A.OWNER,A.TABLE_NAME) IN (SELECT OWNER,TABLE_NAME 
                     FROM MIG_TABLES X
                    WHERE SYSTEM_CODE = 'LC1'
                      AND A.TABLE_NAME = X.TABLE_NAME 
                  )        
   AND B.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
   AND B.CONSTRAINT_TYPE = 'P'  -- PK만 출력시

 )
 GROUP BY OWNER
     , TABLE_NAME
     , CONSTRAINT_NAME     
 ORDER BY 1,2,3     
;

1.3 인덱스 사이즈

-- INDEX 사이즈 조사

MERGE INTO MIG_TABLES A
USING (
        SELECT C.OWNER,C.TABLE_NAME
        --      ,X.SEGMENT_NAME
              ,SUM(X.SIZE_MB) SIZE_MB  
          FROM DBA_INDEXES C 
             , (
                SELECT  A.OWNER
                      , A.SEGMENT_NAME
                      , A.SEGMENT_TYPE      
                      , ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"      
                  FROM DBA_SEGMENTS A
                     , DBA_INDEXES B
                 WHERE A.SEGMENT_NAME = B.INDEX_NAME
                   AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')
                   AND A.OWNER = B.OWNER
                --     AND A.OWNER = '유저아이디'
                   AND EXISTS (SELECT 1
                                 FROM MIG_TABLES C
                                WHERE C.TABLE_NAME = B.TABLE_NAME
                                  AND C.OWNER      = B.OWNER
                                  AND C.SYSTEM_CODE = 'LC1'
                                  AND C.USE_YN = 'Y'
                              )     
                 GROUP BY  A.OWNER
                        , A.SEGMENT_NAME
                        , A.SEGMENT_TYPE
                ) X
          WHERE C.OWNER      = X.OWNER
            AND C.INDEX_NAME = X.SEGMENT_NAME
        --  ORDER BY 1,2,3
        GROUP BY C.OWNER,C.TABLE_NAME       
-- HAVING COUNT(*) > 1
        ) B
       ON (A.OWNER = B.OWNER 
      AND A.TABLE_NAME = B.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET
A.ASIS_INDEX_SIZE = B.SIZE_MB         
;

1.4 인덱스 삭제

DROP INDEX [인덱스명]

1.5 인덱스명 변경

ALTER INDEX OWNER.INDEX_NAME RENAME TO TO_INDEX_NAME;

1.6 인덱스 리빌드

ALTER INDEX 인덱스명 REBUILD TABLESPACE T/S명 ;

1.6.1 인덱스 리빌드 생성 뷰 샘플

인덱스 테이블스페이스를 사용하지 않는 OWNER,INDEX 점검 및 인덱스 테이블스페이스로 이동
CREATE OR REPLACE FORCE VIEW V_DBA_CHK_INVALID_TS
(
    TS_NAME
  , ORG_TS_NAME
)
BEQUEATH DEFINER
AS
    SELECT    'ALTER INDEX '
           || OWNER
           || '.'
           || INDEX_NAME
           || ' REBUILD TABLESPACE TS_'
           || OWNER
           || '_I01;'         TS_NAME
         , TABLESPACE_NAME    ORG_TS_NAME
      FROM DBA_INDEXES
     WHERE     1 = 1
           AND OWNER IN (SELECT USERNAME
                           FROM TB_MGR_USER -- 사용자 테이블 생성  
                          WHERE SCHEMA_YN = 'Y')
           AND NOT REGEXP_LIKE (TABLESPACE_NAME, 'I01$')
           AND NOT REGEXP_LIKE (INDEX_NAME, '^SYS|PK$|^PK');

1.7 인덱스 DISABLE/ENABLE

  • 중요) 인덱스 DISABLE 후 TRUNCATE 를 실시하면 인덱스가 자동으로 ENABLE 됨에 주의 할것
-- 세션 변경 (import 명령어시에도 해당 옵션 사용 가능) 
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

-- INDEX 사용중지
ALTER INDEX IX_PK_TEST_01 UNUSABLE;

-- INDEX 리빌드 
ALTER INDEX IX_PK_TEST_01 REBUILD;

1.8 인덱스 관리 프로시져

CREATE OR REPLACE PROCEDURE SP_ADD_INDEX
/*
-- 2019/08/05
-- 인덱스 추가 테이블 
-- 권한 추가시 권한관리테이블(TB_MGR_GRANT)에 관리 대상을 추가한다.
-- BY CY.KIM
-- ----------------------------------------------------------------
-- 인덱스 정보 동기화 관리테이블 <-> 운영인덱스 간 MERGE   
-- SP_ADD_INDEX(p_iowner=>'SCOTT',p_option => 'M' ,p_exec=>1);
 
--
-- INDEX 관리테이블(TB_MGR_INDEX)
CREATE TABLE TB_MGR_INDEX
(
  TABLE_OWNER      VARCHAR2(128 BYTE)           NOT NULL,
  TABLE_NAME       VARCHAR2(128 BYTE)           NOT NULL,
  INDEX_OWNER      VARCHAR2(128 BYTE)           NOT NULL,
  INDEX_NAME       VARCHAR2(128 BYTE)           NOT NULL,
  COLUMN_CNT       NUMBER,
  INDEX_COLUMNS    VARCHAR2(4000 BYTE),
  INDEX_TYPE       VARCHAR2(27 BYTE),
  UNIQUENESS       VARCHAR2(9 BYTE),
  TABLESPACE_NAME  VARCHAR2(30 BYTE),
  PARTITIONED      VARCHAR2(3 BYTE),
  CREATED          DATE                         DEFAULT SYSDATE               NOT NULL
);

CREATE TABLE TB_MGR_INDEX_LOG
(
  TABLE_OWNER      VARCHAR2(128 BYTE)           NOT NULL,
  TABLE_NAME       VARCHAR2(128 BYTE)           NOT NULL,
  INDEX_OWNER      VARCHAR2(128 BYTE)           NOT NULL,
  INDEX_NAME       VARCHAR2(128 BYTE)           NOT NULL,
  COLUMN_CNT       NUMBER,
  INDEX_COLUMNS    VARCHAR2(4000 BYTE),
  INDEX_TYPE       VARCHAR2(27 BYTE),
  UNIQUENESS       VARCHAR2(9 BYTE),
  TABLESPACE_NAME  VARCHAR2(30 BYTE),
  PARTITIONED      VARCHAR2(3 BYTE),
  CREATED          DATE                         NOT NULL,
  CREATED_SQL      VARCHAR2(2000 BYTE),
  P_OPTION         VARCHAR2(100 BYTE)
)
*/

(  
   p_iowner    in varchar2 default ''
 , p_iname    in varchar2 default ''
 , p_itype    in varchar2 default ''    -- index type NORMAL,FUNCTION BASED ,IOT
 , p_towner   in varchar2 default null   
 , p_tname    in varchar2 default null
 , p_uniq     in varchar2 default ''     -- index type UNIQUE,NONUNIQUE     
 , p_cols     in varchar2  default ''
 , p_cols_cnt in varchar2 default '' 
 , p_tsname   in varchar2 default 'TS_SCOTT_I01'
 , p_part     in varchar2 default ''  -- Partition
 , p_option   in varchar2 default 'A' -- D:Drop all Index, C:Create all Index  , A:Add create one index ,R:Remove index one,M:MERGE
 , p_dblink   in varchar2 default ''  -- DB링크명    
 , p_exec     in number default 0  
) 

IS
/* 1.권한관리테이블에 추가 대상  */
    CURSOR ADD_OBJECT IS
        -- 1.신규 추가된  권한 TB_MGR_INDEX 입력         
       SELECT TABLE_OWNER
             , TABLE_NAME
             , INDEX_OWNER
             , INDEX_NAME
             , COLUMN_CNT
             , INDEX_COLUMNS
             , INDEX_TYPE
             , CASE UNIQUENESS WHEN 'NONUNIQUE' THEN '' END AS UNIQUENESS
             , TABLESPACE_NAME
             , PARTITIONED
         FROM TB_MGR_INDEX@DL_SCOTT_DEV_A
        WHERE A.TABLE_OWNER = p_towner
          AND A.TABLE_NAME  = p_tname         
         ;
                                                   
    V_SQL   VARCHAR2(2000);
        
    V_TABLE_OWNER		VARCHAR2(100);
    V_TABLE_NAME		VARCHAR2(100);
    V_INDEX_OWNER		VARCHAR2(100);
    V_INDEX_NAME		VARCHAR2(100);
    V_COLUMN_CNT		VARCHAR2(100);
    --[5]
    V_INDEX_COLUMNS		VARCHAR2(300);
    V_INDEX_TYPE		VARCHAR2(100);
    V_UNIQUENESS		VARCHAR2(100);
    V_TABLESPACE_NAME	VARCHAR2(100);
    V_PARTITIONED		VARCHAR2(100); 
    --[10] 
    V_ROWCNT    NUMBER;
    V_MSG       long;     
    
BEGIN

--    DBMS_OUTPUT.ENABLE;
    dbms_output.enable(3000);
--    IF p_dblink is not null THEN
--       p_dblink := '@'||p_dblink
--    END IF;
       
    -- ------------------------ 전체 인덱스 MERGE M ----------------------------------
    IF p_option = 'M' THEN
    dbms_output.put_line('[SUCESS] ');
    V_SQL:='MERGE INTO TB_MGR_INDEX T
            USING (
               SELECT A.TABLE_OWNER , A.TABLE_NAME , A.INDEX_OWNER , A.INDEX_NAME , A.COLUMN_CNT , A.INDEX_COLUMNS , A.INDEX_TYPE , A.UNIQUENESS , A.TABLESPACE_NAME , A.PARTITIONED 
                 FROM VW_INDEXES'||case when p_dblink is not null then '@'||p_dblink else '' end||' A
                WHERE NOT EXISTS (SELECT 1 FROM DBA_CONSTRAINTS'||case when p_dblink is not null then '@'||p_dblink else '' end||' B  
                                   WHERE B.INDEX_NAME  = A.INDEX_NAME
                                     AND B.INDEX_OWNER = A.INDEX_OWNER
                                     AND B.TABLE_NAME  = A.TABLE_NAME
                                     AND B.CONSTRAINT_TYPE = ''P'' -- PK 
                                 )      
                 AND A.INDEX_OWNER IN ('''||p_iowner||''')
                   ) S
                ON ( T.TABLE_OWNER = S.TABLE_OWNER
                     AND T.TABLE_NAME  = S.TABLE_NAME
                     AND T.INDEX_OWNER = S.INDEX_OWNER
                     AND T.INDEX_NAME  = S.INDEX_NAME
                   )
              WHEN MATCHED THEN
            -- INDEX명이 같으면 컬럼 UPDATE 
            UPDATE SET T.INDEX_COLUMNS = S.INDEX_COLUMNS
                     , T.COLUMN_CNT    = S.COLUMN_CNT
                 WHERE T.INDEX_COLUMNS <> S.INDEX_COLUMNS
              WHEN NOT MATCHED THEN 
            -- INDEX명이 다르면 INSERT 
            INSERT ( TABLE_OWNER , TABLE_NAME , INDEX_OWNER , INDEX_NAME , COLUMN_CNT , INDEX_COLUMNS , INDEX_TYPE , UNIQUENESS , TABLESPACE_NAME , PARTITIONED ) 
            VALUES ( S.TABLE_OWNER , S.TABLE_NAME , S.INDEX_OWNER , S.INDEX_NAME , S.COLUMN_CNT , S.INDEX_COLUMNS , S.INDEX_TYPE , S.UNIQUENESS , S.TABLESPACE_NAME , S.PARTITIONED )';

            IF p_exec <> 0 THEN
                
                EXECUTE IMMEDIATE V_SQL;
                V_ROWCNT :=  sql%rowcount;
                
                dbms_output.enable(3000);
                dbms_output.put_line( V_ROWCNT || ' rows merged' );
                dbms_output.put_line('[SUCESS] '||V_SQL);     
                
                COMMIT;                         
            ELSE
                dbms_output.enable(3000); 
                dbms_output.put_line('[SQL] '||V_SQL);      
            END IF;
    -- ------------------------  단건 인덱스 생성 A ----------------------------------      
    ELSIF p_option = 'A' THEN
    
          V_SQL :=  'CREATE '||p_uniq||' INDEX '||p_iowner||'.'||p_iname||' ON '||p_towner||'.'||p_tname||'('||p_cols||')'||' TABLESPACE '||p_tsname;         
          
            IF p_exec <> 0 THEN
                EXECUTE IMMEDIATE V_SQL;
                
                -- 입력 
--                INSERT INTO TB_MGR_INDEX 
--                       (OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME, INDEX_COLS, USE_YN) 
--                VALUES (p_iowner,p_iname  ,p_itype    ,p_iowner     , p_tname   ,p_tsname        ,p_cols,'Y');
--                COMMIT; 
                
                SP_INS_MGR_INDEX@DL_SCOTT_DEV_ 
                                  p_iowner
                                , NVL(p_towner,p_iowner)
                                , p_tname
                                , p_iname
                                , p_uniq
                                , p_itype
                                , p_cols
                                , p_cols_cnt
                                , p_tsname
                                , p_part                 
                                );
                
                             
                
                -- 로그 입력                
                INSERT INTO TB_MGR_INDEX_LOG A (
                             A.TABLE_OWNER     
                           , A.TABLE_NAME      
                           , A.INDEX_OWNER     
                           , A.INDEX_NAME      
                           , A.COLUMN_CNT                
                           ----[5]                
                           , A.UNIQUENESS      
                           , A.TABLESPACE_NAME 
                           , A.INDEX_COLUMNS   
                           , A.PARTITIONED
                           , A.P_OPTION        
                           ----[10]
                           , A.CREATED_SQL             
                  ) VALUES (
                              nvl(p_towner,p_iowner)
                            , p_tname
                            , p_iowner
                            , p_iname
                            , p_cols_cnt
                            ----[5]
                            , p_uniq                
                            , p_tsname
                            , p_cols
                            , p_part
                            , p_option
                            ----[10]                              
                            , V_SQL
                  );
                COMMIT;
                dbms_output.enable(500); 
                dbms_output.put_line('[SUCESS] '||V_SQL);                
            ELSE
                dbms_output.enable(100); 
                dbms_output.put_line('[SQL] '||V_SQL);      
            END IF;
    -- ------------------------  단건 인덱스 삭제 R 인경우 ----------------------------------                  
    ELSIF p_option = 'R' THEN

          V_SQL :=  'DROP INDEX '||p_iowner||'.'||p_iname;    
          
          IF p_exec <> 0 THEN               
              EXECUTE IMMEDIATE V_SQL;         
              -- 삭제  
--              DELETE FROM TB_MGR_INDEX WHERE TABLE_OWNER = p_iowner AND TABLE_NAME = p_tname AND INDEX_NAME = p_iname;
--              COMMIT;
                
                SP_DEL_MGR_INDEX@DL_SCOTT_DEV_
                                  p_iowner
                                , nvl(p_towner,p_iowner)
                                , p_tname
                                , p_iname
                                , 'D'
                                , 1
                                );
            
                INSERT INTO TB_MGR_INDEX_LOG A (
                             A.TABLE_OWNER     
                           , A.TABLE_NAME      
                           , A.INDEX_OWNER     
                           , A.INDEX_NAME      
                           , A.COLUMN_CNT                
                           ----[5]                
                           , A.UNIQUENESS      
                           , A.TABLESPACE_NAME 
                           , A.INDEX_COLUMNS   
                           , A.PARTITIONED
                           , A.P_OPTION        
                           ----[10]
                           , A.CREATED_SQL             
                  ) VALUES (
                              nvl(p_towner,p_iowner)
                            , p_tname
                            , p_iowner
                            , p_iname
                            , p_cols_cnt
                            ----[5]
                            , p_uniq                
                            , p_tsname
                            , p_cols
                            , p_part
                            , p_option
                            ----[10]                              
                            , V_SQL
                  );
                COMMIT;
                dbms_output.enable(500); 
                dbms_output.put_line('[SUCESS] '||V_SQL);                 
                
            ELSE
                dbms_output.enable(100); 
                dbms_output.put_line('[SQL] '||V_SQL);
            END IF;
    -- ------------------------  다중건 생성 p_option C,D 인경우 ----------------------------------                               
    ELSE        
        FOR V_ROW IN ADD_OBJECT
        LOOP    
            V_TABLE_OWNER		:= V_ROW.TABLE_OWNER;
            V_TABLE_NAME		:= V_ROW.TABLE_NAME;
            V_INDEX_OWNER		:= V_ROW.INDEX_OWNER;
            V_INDEX_NAME		:= V_ROW.INDEX_NAME;
            V_COLUMN_CNT		:= V_ROW.COLUMN_CNT;
            V_INDEX_COLUMNS		:= V_ROW.INDEX_COLUMNS;
            V_INDEX_TYPE		:= V_ROW.INDEX_TYPE;
            V_UNIQUENESS		:= V_ROW.UNIQUENESS;
            V_TABLESPACE_NAME	:= V_ROW.TABLESPACE_NAME;
            V_PARTITIONED		:= V_ROW.PARTITIONED;
--            V_USE_YN := V_ROW.USE_YN;           
                      
          -- INDEX 테이블에 추가
     
          -- UX일때는  UNIQUE 인덱스 
            IF p_option = 'C' THEN      
                V_SQL :=  'CREATE '||V_UNIQUENESS||' INDEX '||V_INDEX_OWNER||'.'||V_INDEX_NAME||' ON '||V_TABLE_OWNER||'.'||V_TABLE_NAME||
                '( '
--                ||CASE WHEN V_INDEX_TYPE IN ('FUNCTION-BASED DOMAIN','FUNCTION-BASED NORMAL') THEN 'q''{' END 
                ||V_INDEX_COLUMNS
--                ||CASE WHEN V_INDEX_TYPE IN ('FUNCTION-BASED DOMAIN','FUNCTION-BASED NORMAL') THEN '}''' END                
                ||' ) TABLESPACE '||V_TABLESPACE_NAME;             
            ELSIF p_option = 'D' THEN
                V_SQL :=  'DROP INDEX '||V_INDEX_OWNER||'.'||V_INDEX_NAME;            
            ELSE     
                V_SQL :=  '[ERROR] p_option :'||p_option||' not supported';
            END IF;
          
          
            IF p_exec <> 0 THEN
                EXECUTE IMMEDIATE V_SQL;
                
                IF p_option = 'D' THEN
                    -- 인덱스 관리테이블 ROW 삭제 
--                    DELETE FROM TB_MGR_INDEX WHERE OWNER = V_TABLE_OWNER AND INDEX_NAME = V_INDEX_NAME;
--                    COMMIT;
                    -- 테이블 전체 삭제 
--                    SP_DEL_MGR_INDEX(V_TABLE_OWNER,nvl(V_TABLE_OWNER,V_TABLE_OWNER),V_TABLE_NAME, V_INDEX_NAME, 'D',1);
                    SP_DEL_MGR_INDEX@DL_SCOTT_DEV_
                                      V_INDEX_OWNER
                                    , V_TABLE_OWNER
                                    , V_TABLE_NAME
                                    , V_INDEX_NAME
                                    , 'D'
                                    , 1
                                    );                    
                ELSE
                    -- 테이블 전체 입력     
                    SP_INS_MGR_INDEX@DL_SCOTT_DEV_ 
                                      V_INDEX_OWNER
                                    , V_TABLE_OWNER
                                    , V_TABLE_NAME
                                    , V_INDEX_NAME
                                    , V_UNIQUENESS
                                    , V_INDEX_TYPE
                                    , V_INDEX_COLUMNS
                                    , V_COLUMN_CNT
                                    , V_TABLESPACE_NAME
                                    , V_PARTITIONED
                                    );                       
                                                                            
                END IF;
                
                  -- LOG 기록 
                INSERT INTO TB_MGR_INDEX_LOG A (
                                             A.TABLE_OWNER     
                                           , A.TABLE_NAME      
                                           , A.INDEX_OWNER     
                                           , A.INDEX_NAME      
                                           , A.COLUMN_CNT                
                                           ----[5]                
                                           , A.UNIQUENESS      
                                           , A.TABLESPACE_NAME 
                                           , A.INDEX_COLUMNS   
                                           , A.PARTITIONED
                                           , A.P_OPTION        
                                           ----[10]
                                           , A.CREATED_SQL             
                                  ) VALUES (
                                              V_TABLE_OWNER
                                            , V_TABLE_NAME
                                            , V_INDEX_OWNER
                                            , V_INDEX_NAME
                                            , V_COLUMN_CNT
                                            , V_UNIQUENESS
                                            , V_TABLESPACE_NAME
                                            , V_INDEX_COLUMNS
                                            , V_PARTITIONED
                                            , p_option
                                            ----[10]                              
                                            , V_SQL
                                  );
                COMMIT;    
                dbms_output.enable(500); 
                dbms_output.put_line('[SUCESS] '||V_SQL);                       
                    
            ELSE
                dbms_output.enable(100); 
                dbms_output.put_line('[SQL] '||V_SQL);      
            END IF;  
              

        END LOOP;                        
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
       V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); 
      -- LOG 기록 
    IF p_option <> 'M' THEN
        INSERT INTO TB_MGR_INDEX_LOG A (
                                     A.TABLE_OWNER     
                                   , A.TABLE_NAME      
                                   , A.INDEX_OWNER     
                                   , A.INDEX_NAME      
                                   , A.COLUMN_CNT                
                                   ----[5]                
                                   , A.UNIQUENESS      
                                   , A.TABLESPACE_NAME 
                                   , A.INDEX_COLUMNS   
                                   , A.PARTITIONED
                                   , A.P_OPTION        
                                   ----[10]
                                   , A.CREATED_SQL             
                          ) VALUES (
                                      nvl(p_towner,p_iowner)
                                    , p_tname
                                    , p_iowner
                                    , p_iname
                                    , p_cols_cnt
                                    ----[5]
                                    , p_uniq                
                                    , p_tsname
                                    , p_cols
                                    , p_part
                                    , p_option
                                    ----[10]                              
                                    , V_MSG||':'||V_SQL
                          );
                        COMMIT;
    END IF;
END;
/