행위

ORACLE 인덱스

DB CAFE

Dbcafe (토론 | 기여)님의 2023년 1월 30일 (월) 17:02 판 (인덱스 생성)
thumb_up 추천메뉴 바로가기


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.1.12 INDEX INVISIBLE[편집]

  1. 인덱스를 실제 삭제하기 전에 "사용 안 함" 상태로 변경 하는것
  2. 인덱스가 많은 경우 DML문장에 나쁜 영향을 주기 때문에 사용하지 않는 인덱스는 삭제할때 유용
  3. 인덱스를 삭제하려고 했을 때, 정말 사용하는지 사용하지 않는 것인지를 정확하게 알수 없음.
  4. INVISIBLE 상태에서 DML 작업 시 인덱스 내용은 계속 반영 됨.

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.2.6 온라인 인덱스 생성[편집]

  1. DML이 실시간으로 발생하더라도 테이블 LOCK 없이 INDEX 생성 또는 REBUILD 가능
  • 사용 예
-- 일반적으로 사용하는 인덱스 생성 DDL
CREATE INDEX 스키마.인덱스이름
ON 스키마.테이블이름 (컬럼들)
TABLESPACE 테이블스페이스
STORAGE
(
  INITIAL 64K
  NEXT 1M
)
NOLOGGING 
ONLINE      -- 온라인 옵션
;

ALTER INDEX 스키마.인덱스이름 LOGGING;

1.2.6.1 ONLINE 인덱스 작업이 불가능한 경우[편집]

  1. 컬럼의 길이가 너무 길어 online 작업 불가
  • If 8K block size then maximum index key length=3218
  • If 16K block size then maximum index key length=6498
  • How the maximum index key length is measured by?
    • Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)
  • 에러 내용

<spurce lang=sql> ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다 ORA-01450: 키의 최대 길이(3215)를 초과했습니다 </source>

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;
/