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 특정 테이블의 인덱스 확인[편집]

현재 사용자의 특정 테이블 정보 확인 

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.5 인덱스에 대한 컬럼 조회[편집]

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

SELECT TABLE_NAME , INDEX_NAME , COLUMN_POSITION , COLUMN_NAME 
  FROM USER_IND_COLUMNS 
 ORDER BY TABLE_NAME
        , INDEX_NAME
        , COLUMN_POSITION;

1.1.6 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.7 중복인덱스 체크[편집]

중복된 컬럼으로 사용중인 인덱스 정보 조회 

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.8 테이블의 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.9 인덱스의 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.10 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 인덱스 생성[편집]

CREATE INDEX [인덱스명] ON TABLE (col1,col2,..)

S 

1.2.2 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.3 테이블/인덱스 추출 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'

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