[Oracle] 테이블의 인덱스 불일치 판단

-- 운영, 배포 테이블의 인덱스 불일치 판단(인덱스명은 select 문에 포함시키면 안됨)
WITH 
운영 AS (
    SELECT B.TABLE_NAME, A.UNIQUENESS, B.COLUMN_NAME||' '||descend AS column_name_descend, B.DESCEND, B.COLUMN_POSITION, B.INDEX_NAME
    FROM USER_INDEXES A, USER_IND_COLUMNS  B
    WHERE A.INDEX_NAME = B.INDEX_NAME AND B.TABLE_NAME LIKE UPPER('X%') AND B.TABLE_NAME NOT LIKE UPPER('%_NEW') 
),
배포 AS (
    SELECT REGEXP_REPLACE(B.TABLE_NAME, '_NEW', '', 1, 0, 'i') AS TABLE_NAME , A.UNIQUENESS, B.COLUMN_NAME||' '||descend AS column_name_descend, B.DESCEND, B.COLUMN_POSITION, B.INDEX_NAME
    FROM USER_INDEXES A, USER_IND_COLUMNS  B
    WHERE A.INDEX_NAME = B.INDEX_NAME AND A.TABLE_NAME LIKE UPPER('X%_NEW')
)
SELECT  
       TABLE_NAME||' | '||DECODE(UNIQUENESS, 'UNIQUE', 'UNIQUE   ','NONUNIQUE')||' | '||LISTAGG(column_name_descend, ' / ') WITHIN GROUP(ORDER BY column_position) AS COLUMNS
FROM 운영
GROUP BY INDEX_NAME, TABLE_NAME, UNIQUENESS
MINUS -- UNION ALL -- 
SELECT 
       TABLE_NAME||' | '||DECODE(UNIQUENESS, 'UNIQUE', 'UNIQUE   ','NONUNIQUE')||' | '||LISTAGG(column_name_descend, ' / ') WITHIN GROUP(ORDER BY column_position) AS COLUMNS
FROM 배포
GROUP BY INDEX_NAME, TABLE_NAME, UNIQUENESS
;

 

-- 통과 : 운영, 수급 테이블의 인덱스 불일치 판단
WITH 
운영 AS (
    SELECT B.TABLE_NAME, A.UNIQUENESS, B.COLUMN_NAME, B.DESCEND, B.COLUMN_POSITION, B.INDEX_NAME
    FROM USER_INDEXES A, USER_IND_COLUMNS  B
    WHERE A.INDEX_NAME = B.INDEX_NAME AND B.TABLE_NAME LIKE UPPER('테이블명%') AND B.TABLE_NAME NOT LIKE UPPER('%_NEW') 
),
수급 AS (
    SELECT REGEXP_REPLACE(B.TABLE_NAME, '_NEW', '', 1, 0, 'i') AS TABLE_NAME , A.UNIQUENESS, B.COLUMN_NAME, B.DESCEND, B.COLUMN_POSITION, B.INDEX_NAME
    FROM USER_INDEXES A, USER_IND_COLUMNS  B
    WHERE A.INDEX_NAME = B.INDEX_NAME AND A.TABLE_NAME LIKE UPPER('TN_RLDLPC_RENT_MO%_BAK')
)
SELECT  
       TABLE_NAME||' | '||UNIQUENESS||' | '||LISTAGG('('||COLUMN_POSITION||')'||COLUMN_NAME||'['||DESCEND||']', ',') WITHIN GROUP(ORDER BY INDEX_NAME) AS COLUMNS
FROM 운영
GROUP BY TABLE_NAME, DESCEND, UNIQUENESS, INDEX_NAME
MINUS -- UNION ALL -- 
SELECT 
       TABLE_NAME||' | '||UNIQUENESS||' | '||LISTAGG('('||COLUMN_POSITION||')'||COLUMN_NAME||'['||DESCEND||']', ',') WITHIN GROUP(ORDER BY INDEX_NAME) AS COLUMNS
FROM 수급
GROUP BY TABLE_NAME, DESCEND, UNIQUENESS, INDEX_NAME
;



-- 테이블 및 인덱스 삭제 쿼리문 출력
SELECT '(TEST) DROP '
  ||object_type
  ||' '
  || object_name
  || DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
FROM user_objects;

-- 테이블 삭제 쿼리문 출력
SELECT 'DROP '
  ||TABLE_NAME
  ||'       CASCADE CONSTRAINTS;'
FROM USER_TABLES
WHERE TABLE_NAME LIKE 'TN_RLDLPC_%_BAK';

-- 테이블의 모든 인덱스 삭제 쿼리문 출력
SELECT 'DROP INDEX ' || INDEX_NAME || ';' FROM USER_INDEXES WHERE TABLE_NAME = 'TN_RLDLPC_RENT_APT_BAK';
--DROP INDEX TN_RLDLPC_RENT_APT_III_1;
--DROP INDEX TN_RLDLPC_RENT_APT_III_2;
--DROP INDEX TN_RLDLPC_RENT_APT_III_3;


--CREATE INDEX TN_RLDLPC_RENT_APT_III ON TN_RLDLPC_RENT_APT_BAK (SIGNGU_CD );
SELECT * FROM USER_OBJECTS; -- 사용자가 작성한 스키마 오브젝트(테이블, 인덱스, 뷰, 시퀀스, DB링크, PLSQL 함수, 프러시저)를 확인 하는 뷰
SELECT * FROM USER_TABLES; -- 사용자가 작성한 스키마 오브젝트(테이블, 인덱스, 뷰, 시퀀스, DB링크, PLSQL 함수, 프러시저)를 확인 하는 뷰
SELECT * FROM USER_CONSTRAINTS where table_name like 'TN_RLDLPC_%_BAK'; -- NOT NULL, PK 등 제약조건
SELECT * FROM USER_TAB_COLUMNS where table_name like 'TN_RLDLPC_%_BAK'; -- 테이블별 컬럼, 길이, 타입, high, low_value 등
SELECT * FROM USER_INDEXES where table_name like 'TN_RLDLPC_%_BAK'; -- 인덱스명, 인덱스타입, 테이블명, 테이블오너, 고유성, 라인수, initial-min-mact extents 등
SELECT * FROM USER_IND_COLUMNS where table_name like 'TN_RLDLPC_%_BAK'; -- 컬럼명, 인덱스명, 테이블명, 컬럼위치, 컬럼길이, 정렬 등


--CREATE INDEX 인덱스명 ON 테이블명 (컬럼명 정렬옵션, [...]);
--DROP INDEX "인덱스명"; -- '' 감싸기(X)

-- 통과(구글링) : 테이블명 - 인덱스명 - 인덱스 걸린 컬럼들(가로 ',' 나열)
select A.*, AI.UNIQUENESS,AI.TABLESPACE_NAME 
from ( SELECT AIC.INDEX_OWNER, 
              AIC.TABLE_NAME,
              AIC.INDEX_NAME, 
              listagg(AIC.COLUMN_NAME,',') within group (order by AIC.COLUMN_POSITION) cols  
       FROM  ALL_IND_COLUMNS AIC
       where AIC.TABLE_NAME LIKE upper('TN_RLDLPC%BAK')
       group by AIC.INDEX_OWNER, AIC.TABLE_NAME  , AIC.INDEX_NAME
       order by AIC.INDEX_OWNER, AIC.TABLE_NAME 
    ) A, ALL_INDEXES AI
where A.INDEX_NAME=AI.INDEX_NAME(+);


---------------------

    
-- 통과 : 마이너스 비교 쿼리
select T.*
from(
    SELECT b.table_name, a.uniqueness, b.column_name, b.descend, b.column_position
    FROM USER_INDEXES a, USER_IND_COLUMNS  b
    WHERE a.index_name = b.index_name AND b.table_name like upper('TN_RLDLPC_RENT_APT%') AND b.table_name not like upper('%new')
    
    union all
    
    SELECT b.table_name, a.uniqueness, b.column_name, b.descend, b.column_position
    FROM USER_INDEXES a, USER_IND_COLUMNS  b
    WHERE a.index_name = b.index_name AND a.table_name like upper('TN_RLDLPC_RENT_%new')
) T
GROUP BY T.table_name, T.uniqueness, T.column_name, T.descend, T.column_position;