"ORACLE 인덱스"의 두 판 사이의 차이
DB CAFE
23번째 줄: | 23번째 줄: | ||
− | <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> | + | <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> | ||
51번째 줄: | 63번째 줄: | ||
== 인덱스에 대한 컬럼 조회 == | == 인덱스에 대한 컬럼 조회 == | ||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT TABLE_NAME , INDEX_NAME , COLUMN_POSITION , COLUMN_NAME | SELECT TABLE_NAME , INDEX_NAME , COLUMN_POSITION , COLUMN_NAME | ||
61번째 줄: | 72번째 줄: | ||
== PRIMARY KEY 재생성 방법 == | == PRIMARY KEY 재생성 방법 == | ||
− | |||
-- PRIMARY KEY DROP | -- PRIMARY KEY DROP | ||
<source lang='sql'> | <source lang='sql'> | ||
68번째 줄: | 78번째 줄: | ||
-- PRIMARY KEY 생성 | -- PRIMARY KEY 생성 | ||
− | |||
<source lang='sql'> | <source lang='sql'> | ||
ALTER TABLE EMP | ALTER TABLE EMP | ||
78번째 줄: | 87번째 줄: | ||
== PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기 == | == PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기 == | ||
− | + | <source lang="sql"> | |
− | + | SELECT C.NAME CONSTRAINT_NAME | |
− | + | FROM DBA_OBJECTS A | |
− | <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> | + | , CDEF$ B |
− | + | , CON$ C | |
− | + | WHERE A.OBJECT_NAME = UPPER('&테이블명') | |
− | + | AND A.OBJECT_ID = B.ROBJ# | |
+ | AND B.CON# = C.CON#; | ||
+ | </source> | ||
=== 중복인덱스 체크 === | === 중복인덱스 체크 === | ||
114번째 줄: | 125번째 줄: | ||
== 테이블의 PK를 구성하는 컬럼 조회 == | == 테이블의 PK를 구성하는 컬럼 조회 == | ||
− | + | <source lang="sql"> | |
− | + | SELECT A.TABLE_NAME, B.CONSTRAINT_NAME, C.COLUMN_NAME | |
− | <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> | + | 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> | ||
123번째 줄: | 140번째 줄: | ||
SELECT NAME , LF_ROWS , DEL_LF_ROWS , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%" | SELECT NAME , LF_ROWS , DEL_LF_ROWS , (DEL_LF_ROWS / LF_ROWS) * 100 AS "DELETE SPACE%" | ||
FROM INDEX_STATS | FROM INDEX_STATS | ||
− | WHERE NAME = UPPER('&INDEX_NAME'); </source> | + | WHERE NAME = UPPER('&INDEX_NAME'); |
+ | </source> | ||
--Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다. | --Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다. | ||
== Index가 없는 Table 조회 == | == Index가 없는 Table 조회 == | ||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT OWNER, TABLE_NAME | SELECT OWNER, TABLE_NAME | ||
139번째 줄: | 156번째 줄: | ||
ORDER BY OWNER, TABLE_NAME; | ORDER BY OWNER, TABLE_NAME; | ||
</source> | </source> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
==테이블/인덱스 추출 SQL== | ==테이블/인덱스 추출 SQL== |
2018년 8월 29일 (수) 12:14 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
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
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.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 인덱스의 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.8 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.9 테이블/인덱스 추출 SQL[편집]
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
;