"ORACLE 인덱스"의 두 판 사이의 차이
DB CAFE
2번째 줄: | 2번째 줄: | ||
| | ||
+ | |||
11번째 줄: | 12번째 줄: | ||
##13 INDEX 보기 | ##13 INDEX 보기 | ||
− | < | + | <source 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; </source> | ||
# | # | ||
##14 전체 INDEX 보기 | ##14 전체 INDEX 보기 | ||
− | < | + | <source 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; </source> | ||
# | # | ||
##15 특정 테이블의 인덱스 확인 | ##15 특정 테이블의 인덱스 확인 | ||
− | + | <source 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; </source> | ||
+ | |||
− | |||
# | # | ||
##16 인덱스에 대한 컬럼 조회 | ##16 인덱스에 대한 컬럼 조회 | ||
− | + | <source lang="sql"> SELECT TABLE_NAME | |
− | + | ||
− | < | + | , INDEX_NAME |
+ | , COLUMN_POSITION | ||
+ | , COLUMN_NAME | ||
+ | |||
+ | FROM USER_IND_COLUMNS ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION; </source> | ||
# | # | ||
##17 PRIMARY KEY 재생성 방법 | ##17 PRIMARY KEY 재생성 방법 | ||
− | + | <source 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; </source> | ||
# | # | ||
##18 PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기 | ##18 PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기 | ||
− | + | <source 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#; </source> | ||
# | # | ||
##19 중복인덱스 체크 | ##19 중복인덱스 체크 | ||
− | + | <source 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#; </source> | ||
# | # | ||
##20 테이블의 PK를 구성하는 컬럼 조회 | ##20 테이블의 PK를 구성하는 컬럼 조회 | ||
− | + | <source 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'; </source> | ||
# | # | ||
##21 Index가 없는 Table 조회 | ##21 Index가 없는 Table 조회 | ||
− | + | <source 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; </source> | ||
+ | |||
− | |||
# | # | ||
− | ##51 인덱스의 Delete Space 조회 | + | ##51 인덱스의 Delete Space 조회 |
+ | |||
+ | <source lang="sql"> | ||
+ | |||
+ | SELECT NAME | ||
+ | |||
+ | , LF_ROWS<br/> , DEL_LF_ROWS<br/> , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%" | ||
+ | |||
+ | FROM INDEX_STATS WHERE NAME = UPPER('&INDEX_NAME'); </source><br/> --Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다. | ||
+ | |||
− | |||
− | |||
− | |||
− | |||
− | |||
# | # | ||
##21 Index가 없는 Table 조회 | ##21 Index가 없는 Table 조회 | ||
− | + | ||
− | + | ||
− | + | <source 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; </source> | |
− | < |
2018년 8월 22일 (수) 14:45 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
인덱스 정보[편집]
-
- 13 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;
-
- 14 전체 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;
-
- 15 특정 테이블의 인덱스 확인
/*
인덱스를 확인 하고자 할때 사용하는 쿼리
*/
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;
-
- 16 인덱스에 대한 컬럼 조회
SELECT TABLE_NAME
, INDEX_NAME
, COLUMN_POSITION
, COLUMN_NAME
FROM USER_IND_COLUMNS ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION;
-
- 17 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;
-
- 18 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#;
-
- 19 중복인덱스 체크
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#;
-
- 20 테이블의 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';
-
- 21 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;
-
- 51 인덱스의 Delete Space 조회
SELECT NAME
, LF_ROWS<br/> , DEL_LF_ROWS<br/> , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%"
FROM INDEX_STATS WHERE NAME = UPPER('&INDEX_NAME');
--Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다.
-
- 21 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;