"ORACLE 인덱스"의 두 판 사이의 차이
DB CAFE
(→인덱스 관련 뷰) |
|||
2번째 줄: | 2번째 줄: | ||
= 인덱스 = | = 인덱스 = | ||
− | ==INDEX 정보== | + | == INDEX 정보 == |
---- | ---- | ||
+ | |||
=== 인덱스 관련 뷰=== | === 인덱스 관련 뷰=== | ||
* DBA|ALL|USER_INDEXES | * DBA|ALL|USER_INDEXES | ||
* DBA|ALL|USER_IND_COLUMNS | * DBA|ALL|USER_IND_COLUMNS | ||
− | ===사용자 인덱스 구성 컬럼 정보=== | + | === 사용자 인덱스 구성 컬럼 정보 === |
현재 사용자의 인덱스와 각 인덱스를 구성하는 컬럼 정보 확인 | 현재 사용자의 인덱스와 각 인덱스를 구성하는 컬럼 정보 확인 | ||
+ | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT A.INDEX_NAME | SELECT A.INDEX_NAME | ||
22번째 줄: | 24번째 줄: | ||
ORDER BY 1, 3; </source> | ORDER BY 1, 3; </source> | ||
− | ===전체 INDEX 보기 === | + | === 전체 INDEX 보기 === |
+ | |||
DBA권한으로 확인가능하는 전체 인덱스 및 인덱스구성 컬럼 정보 조회 | DBA권한으로 확인가능하는 전체 인덱스 및 인덱스구성 컬럼 정보 조회 | ||
41번째 줄: | 44번째 줄: | ||
===특정 테이블의 인덱스 확인 === | ===특정 테이블의 인덱스 확인 === | ||
현재 사용자의 특정 테이블 정보 확인 | 현재 사용자의 특정 테이블 정보 확인 | ||
+ | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT C.TABLE_NAME , C.INDEX_NAME , C.COLUMN_NAME , C.COLUMN_POSITION , T.NUM_ROWS | SELECT C.TABLE_NAME , C.INDEX_NAME , C.COLUMN_NAME , C.COLUMN_POSITION , T.NUM_ROWS | ||
60번째 줄: | 64번째 줄: | ||
</source> | </source> | ||
− | ===인덱스에 대한 컬럼 조회 === | + | === 인덱스에 대한 컬럼 조회 === |
사용자의 인덱스 컬럼 구성 정보 | 사용자의 인덱스 컬럼 구성 정보 | ||
71번째 줄: | 75번째 줄: | ||
</source> | </source> | ||
− | ===PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기=== | + | === PRIMARY KEY를 REFERENCE 하는 FOREIGN KEY 찾기 === |
특정테이블의 PK를 참조하고 있는 외부키(FK) 정보 조회 | 특정테이블의 PK를 참조하고 있는 외부키(FK) 정보 조회 | ||
<source lang="sql"> | <source lang="sql"> | ||
83번째 줄: | 87번째 줄: | ||
</source> | </source> | ||
− | ===중복인덱스 체크 === | + | === 중복인덱스 체크 === |
중복된 컬럼으로 사용중인 인덱스 정보 조회 | 중복된 컬럼으로 사용중인 인덱스 정보 조회 | ||
<SOURCE LANG='SQL'> | <SOURCE LANG='SQL'> | ||
109번째 줄: | 113번째 줄: | ||
</SOURCE> | </SOURCE> | ||
− | ===테이블의 PK를 구성하는 컬럼 조회 === | + | === 테이블의 PK를 구성하는 컬럼 조회 === |
사용자 테이블의 기본키(PK) 정보 | 사용자 테이블의 기본키(PK) 정보 | ||
<source lang="sql"> | <source lang="sql"> | ||
121번째 줄: | 125번째 줄: | ||
</source> | </source> | ||
− | ===인덱스의 Delete Space 조회 === | + | === 인덱스의 Delete Space 조회 === |
인덱스의 Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다. | 인덱스의 Delete Space% 값이 20% 가 넘으면, 그 인덱스는 다시 작성하는 것이 좋다. | ||
<source lang="sql"> | <source lang="sql"> | ||
130번째 줄: | 134번째 줄: | ||
</source> | </source> | ||
− | ===Index가 없는 Table 조회 === | + | === Index가 없는 Table 조회 === |
인덱스 없는 테이블 정보 조회 | 인덱스 없는 테이블 정보 조회 | ||
<source lang="sql"> | <source lang="sql"> | ||
143번째 줄: | 147번째 줄: | ||
ORDER BY OWNER, TABLE_NAME; | ORDER BY OWNER, TABLE_NAME; | ||
</source> | </source> | ||
+ | ---- | ||
== 인덱스 생성== | == 인덱스 생성== | ||
− | === 인덱스 | + | * 생성시 주의점 |
+ | ## 테이블과 인덱스가 같은 테이블스페이스에 있으면 안됨. 성능 저하!, 전용 인덱스 테이블스페이스를 사용할 것! | ||
+ | ## index 생성시 redolog에도 기록이 되는데 인덱스가 클 경우 시간이 너무 오래걸리기 때문에 생성시 | ||
+ | nologging 옵션을 줘서 리빌드 후 alter로 변경. | ||
+ | |||
+ | === 인덱스 생성문 === | ||
<source lang=sql> | <source lang=sql> | ||
− | CREATE INDEX | + | CREATE INDEX 인덱스명 |
− | + | ON 테이블명(칼럼명) | |
+ | PCTFREE * | ||
+ | STORAGE(INITIAL * NEXT * PCTINCREASE * MAXEXTENTS *) | ||
+ | TABLESPACE T/S명 ; | ||
+ | |||
+ | -- PCTUSED 옵션은 index는 지워지지 않기 때문에 필요 없음. | ||
+ | -- STORAGE 행은 ASSM일 경우 알아서 자동적으로 설정되기 때문에 신경 쓸 필요는 없음. | ||
</source> | </source> | ||
− | ===PRIMARY KEY 재생성 방법 === | + | === PRIMARY KEY 재생성 방법 === |
-- PRIMARY KEY DROP | -- PRIMARY KEY DROP | ||
<source lang='sql'> | <source lang='sql'> | ||
272번째 줄: | 288번째 줄: | ||
== 인덱스 변경== | == 인덱스 변경== | ||
+ | |||
+ | == 인덱스 리빌드 == | ||
+ | |||
+ | <source> | ||
+ | ALTER INDEX 인덱스명 REBUILD TABLESPACE T/S명 ; | ||
+ | </source> | ||
+ | |||
---- | ---- | ||
<comments /> | <comments /> |
2019년 4월 2일 (화) 14:00 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 인덱스[편집]
1.1 INDEX 정보[편집]
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 인덱스 생성[편집]
- 생성시 주의점
- 테이블과 인덱스가 같은 테이블스페이스에 있으면 안됨. 성능 저하!, 전용 인덱스 테이블스페이스를 사용할 것!
- index 생성시 redolog에도 기록이 되는데 인덱스가 클 경우 시간이 너무 오래걸리기 때문에 생성시
nologging 옵션을 줘서 리빌드 후 alter로 변경.
1.2.1 인덱스 생성문[편집]
CREATE INDEX 인덱스명
ON 테이블명(칼럼명)
PCTFREE *
STORAGE(INITIAL * NEXT * PCTINCREASE * MAXEXTENTS *)
TABLESPACE T/S명 ;
-- PCTUSED 옵션은 index는 지워지지 않기 때문에 필요 없음.
-- STORAGE 행은 ASSM일 경우 알아서 자동적으로 설정되기 때문에 신경 쓸 필요는 없음.
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 PK 생성 스크립트 SQL[편집]
SELECT 'ALTER TALLE '||OWNER||'.'||TABLE_NAME||' ADD CONSTRAINTS '||CONSTRAINT_NAME||' PRIMARY KEY ('||COLS||');' AS DDL
FROM (
SELECT C.OWNER
, C.TABLE_NAME
, S.CONSTRAINT_NAME
-- , S.CONSTRAINT_TYPE
, LISTAGG(C.COLUMN_NAME,',') WITHIN GROUP (ORDER BY POSITION) COLS
-- , C.POSITION
FROM DBA_CONS_COLUMNS C
, DBA_CONSTRAINTS S
WHERE C.CONSTRAINT_NAME = S.CONSTRAINT_NAME
AND S.CONSTRAINT_TYPE = 'P'
AND C.TABLE_NAME IN (
'TB_EMP'
)
GROUP BY C.OWNER
, C.TABLE_NAME
, S.CONSTRAINT_NAME
)
1.2.4 테이블/PK 인덱스 추출 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' -- PK만 출력시
)
GROUP BY OWNER
, TABLE_NAME
, CONSTRAINT_NAME
ORDER BY 1,2,3
;
1.3 인덱스 사이즈[편집]
-- INDEX 사이즈 조사
MERGE INTO MIG_TABLES A
USING (
SELECT C.OWNER,C.TABLE_NAME
-- ,X.SEGMENT_NAME
,SUM(X.SIZE_MB) SIZE_MB
FROM DBA_INDEXES C
, (
SELECT A.OWNER
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
, ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"
FROM DBA_SEGMENTS A
, DBA_INDEXES B
WHERE A.SEGMENT_NAME = B.INDEX_NAME
AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')
AND A.OWNER = B.OWNER
-- AND A.OWNER = '유저아이디'
AND EXISTS (SELECT 1
FROM MIG_TABLES C
WHERE C.TABLE_NAME = B.TABLE_NAME
AND C.OWNER = B.OWNER
AND C.SYSTEM_CODE = 'LC1'
AND C.USE_YN = 'Y'
)
GROUP BY A.OWNER
, A.SEGMENT_NAME
, A.SEGMENT_TYPE
) X
WHERE C.OWNER = X.OWNER
AND C.INDEX_NAME = X.SEGMENT_NAME
-- ORDER BY 1,2,3
GROUP BY C.OWNER,C.TABLE_NAME
-- HAVING COUNT(*) > 1
) B
ON (A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET
A.ASIS_INDEX_SIZE = B.SIZE_MB
;
1.4 인덱스 삭제[편집]
DROP INDEX [인덱스명]