[Oracle] 기존 테이블에서 인덱스/PK 생성-삭제문 출력하기

-- 기존(운영) 테이블에서 삭제문
SELECT 
 CASE regexp_count(INDEX_NAME, 'PK')
     WHEN 0 THEN
       'DROP INDEX '||INDEX_NAME||';' 
     ELSE 
       'ALTER TABLE '|| TABLE_NAME ||' DROP CONSTRAINT '||INDEX_NAME||';'
     END AS COLUMNS
FROM USER_INDEXES
WHERE TABLE_NAME LIKE UPPER('XX_NEW'); -- 운영에서 DROP문 추출하고 제거하면 사고 !!

 

-- 기존(운영) 테이블에서 인덱스/PK 생성문

WITH 
운영 AS (
SELECT
    table_name,
    uniqueness,
    replace(nvl(column_expression, column_name), '"', '')||' '||descend AS COLUMN_NAME_DESCEND,
    descend,
    nvl(exp_col_position, column_position)                AS column_position,
    index_name
    
FROM
    (
        SELECT /*+ no_merge(b) */
            a.table_name,
            c.index_name,
            a.column_name,
            a.column_position,
            a.descend,
            b.column_position AS exp_col_position,
            b.column_expression,
            c.uniqueness
        FROM
            all_ind_columns a
            LEFT OUTER JOIN XMLTABLE ( '/ROWSET/ROW'
                    PASSING dbms_xmlgen.getxmltype(replace('select column_expression, column_position from all_ind_expressions where index_name = '':1''',
                    ':1', a.index_name))
                COLUMNS
                    column_expression VARCHAR2(4000) PATH 'COLUMN_EXPRESSION',
                    column_position NUMBER PATH 'COLUMN_POSITION'
            )               b ON a.column_position = b.column_position
            LEFT JOIN all_indexes     c ON a.index_name = c.index_name
        WHERE
            a.table_name = 'XX'
        ORDER BY INDEX_NAME, UNIQUENESS, COLUMN_POSITION
    )
)SELECT 
    CASE regexp_count(INDEX_NAME, 'PK')
     WHEN 0 THEN
        'CREATE '||DECODE(UNIQUENESS,'UNIQUE','UNIQUE ','')||'INDEX '||
        INDEX_NAME||'2'||' ON '|| TABLE_NAME ||'_NEW ('|| 
        LISTAGG(column_name_descend, ', ') WITHIN GROUP(ORDER BY column_position)||');'  
     ELSE 
        'ALTER TABLE '|| TABLE_NAME ||' ADD CONSTRAINT '||INDEX_NAME||'2'||
        ' PRIMARY KEY ('||regexp_replace(LISTAGG(column_name_descend, ', ') 
        WITHIN GROUP(ORDER BY column_position) ,'( ASC| DESC)', '')||');'
     END AS COLUMNS
     -- 아래 GROUP BY에 column_name_descend가 존재하지 않아서 SELECT문에 쓸 수 없는데, LISTAGG() 안에 column_name_descend 쓰면 출력 가능
from 운영
GROUP BY INDEX_NAME, UNIQUENESS, TABLE_NAME
ORDER BY INDEX_NAME;



LISTAGG() 안은 가로로 나열될 컬럼이고,

어떤 컬럼끼리 가로로 나열할지 여부는 GROUP BY에서 결정된다.

가로 나열 컬럼 속에서 순서는 WITHIN GROUP(ORDER BY)로 정한다.​