행위

ORACLE 인덱스

DB CAFE

Dbcafe (토론 | 기여)님의 2018년 8월 22일 (수) 16:53 판 (→‎PRIMARY KEY 재생성 방법)
thumb_up 추천메뉴 바로가기

1 인덱스 정보[편집]

INDEX 보기   



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 전체 INDEX 보기[편집]

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

인덱스를 확인 하고자 할때 사용하는 쿼리


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

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

1.4 PRIMARY KEY 재생성 방법[편집]

-- PRIMARY KEY DROP

<syntaxhighlight lang='sql'> 

ALTER TABLE EMP DROP PRIMARY KEY;

</syntaxhighlight> 

-- PRIMARY KEY 생성

<syntaxhighlight lang='sql'> 

 ALTER TABLE EMP
   ADD CONSTRAINT EMP_PK

PRIMARY KEY(EMPNO) USING INDEX STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) TABLESPACE USERS; </syntaxhighlight>

1.5 PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기[편집]

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

  

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.6 테이블의 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.7 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.8 인덱스의 Delete Space 조회[편집]

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


1.9 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.10 테이블의 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.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.12 인덱스의 Delete Space 조회[편집]

SELECT NAME
     , LF_ROWS
     , DEL_LF_ROWS  
     , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%"
  FROM INDEX_STATS WHERE NAME = UPPER('&INDEX_NAME');

--Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다.



1.13 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;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=ORACLE_인덱스&oldid=183"