인덱스 정보[편집]

1. 1. 13 INDEX 보기

  <syntaxhighlight lang="sql">

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

1. 1. 14 전체 INDEX 보기

  <syntaxhighlight lang="sql">

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

1. 1. 15 특정 테이블의 인덱스 확인

  <syntaxhighlight lang="sql">

/*

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

• /

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

1. 1. 16 인덱스에 대한 컬럼 조회

  <syntaxhighlight lang="sql"> SELECT TABLE_NAME

    , INDEX_NAME
    , COLUMN_POSITION
    , COLUMN_NAME

 FROM USER_IND_COLUMNS ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION; </syntaxhighlight>

1. 1. 17 PRIMARY KEY 재생성 방법

  <syntaxhighlight lang="sql"> --- 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; </syntaxhighlight>

1. 1. 18 PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기

  <syntaxhighlight lang="sql"> 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#; </syntaxhighlight>

1. 1. 19 중복인덱스 체크

  <syntaxhighlight lang="sql"> 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#; </syntaxhighlight>

1. 1. 20 테이블의 PK를 구성하는 컬럼 조회

  <syntaxhighlight lang="sql"> 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'; </syntaxhighlight>

1. 1. 21 Index가 없는 Table 조회

  <syntaxhighlight lang="sql"> 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; </syntaxhighlight>

1. 1. 51 인덱스의 Delete Space 조회   

SELECT NAME

&nbsp;&nbsp;&nbsp;&nbsp; , LF_ROWS<br/> &nbsp;&nbsp;&nbsp;&nbsp; , DEL_LF_ROWS<br/> &nbsp;&nbsp;&nbsp;&nbsp; , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%"

&nbsp; FROM INDEX_STATS WHERE NAME = UPPER('&INDEX_NAME');

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