[ORACLE SQL] 인덱스

인덱스의 장점

- 검색 속도가 빨라진다.
- 시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킨다.

인덱스의 단점
- 인덱스를 위한 추가적인 공간이 필요하다.
- 인덱스를 생성하는데 시간이 걸린다.
- 데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우에는 오히려 성능이 저하된다.

 

Primary Key / unique

기본 키나 유일 키는 데이터 무결성을 확인하기 위해하기 위해서 수시로 데이터를 검색하기 때문에

빠른 조회를 목적으로 오라클에서 내부적으로 해당 컬럼에 인덱스를 자동으로 생성한다.

 

-- index : 검색을 빠르게 하기 위한 객체
-- create index index_name on targer_table_name (column_name)

-- index 확인을 하는 user_ind_columns
desc user_ind_columns;

select index_name, table_name, column_name
from user_ind_columns;
-- primary key 또는 unieq 속성은 자동으로 index가 생성이 된다.



insert into emp10 select * from emp10;
-- emp10 테이블에 500만개의 행을 만들어준 상태임
insert into emp10 (empno, ename) values (1111, 'COOL');

select empno, ename from emp10 where ename='COOL';  -- 2.765초

-- ename을 인덱스로 정의
create index index_emp10_ename
on emp10 (ename);
select empno, ename from emp10 where ename='COOL';  -- 1.26초로 단축

-- 인덱스 삭제
-- DROP INDEX index_name;

 

---

 

---

 

인덱스 확인방법

 

: 인덱스 확인

SELECT * FROM USER_INDEXES

WHERE TABLE_NAME='테이블명 ' ;

 

: 키가 되는 컬럼 알아보기

ELECT * FROM USER_IND_COLUMNS

WHERE TABLE_NAME='테이블명 ' ;

 

: 해당 인덱스의 컬럼만 알아보기

SELECT * FROM USER_IND_COLUMNS

WHERE INDEX_NAME='인덱스명';

 

create index index_test01 on test01(ename);
insert into test01 select * from test01;

--인덱스 확인 방법
SELECT * FROM USER_INDEXES
WHERE TABLE_NAME='TEST01';

-- 그 키가 되는 컬럼을 알아보는 방법
SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME='TEST01';

-- 해당 인덱스의 컬럼만 보고싶다면
SELECT * FROM USER_IND_COLUMNS
WHERE INDEX_NAME='INDEX_TEST01';

-- 내가 조회할땐 어쩐지 대문자만됐다;

---

 

출처 : coding-factory.tistory.com/419

[Oracle] 오라클 인덱스(Index) 사용법 총정리(생성, 조회, 삭제, 리빌드)

인덱스(Index)란?

인덱스는 데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이며 일종의 색인기술입니다. 테이블에 index를 생성하게 되면 index Table을 생성해 관리합니다. 인덱스는 테이블에 있는 하나이상의 컬럼으로 만들 수 있습니다. 가장 일반적인 B-tree 인덱스는 인덱스 키(인덱스로 만들 테이블의 컬럼 값)와 이 키에 해당하는 컬럼 값을 가진 테이블의 로우가 저장된 주소 값으로 구성됩니다.인덱스(Index) 사용 예시 

인덱스 생성

--문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)

--예제
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

--예제 컬럼 중복 X
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

위와같이 쿼리문을 작성하면 INDEX를 생성할 수 있습니다. UNIQUE 키워드를 붙이면 컬럼값에 중복값을 허용하지 않는다는 뜻입니다.

 

인덱스 조회

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';

인덱스를 생성하면 USER_INDEXES 시스템 뷰에서 조회할 수 있습니다.

방금 CUSTOMERS 테이블에 만들었던 EX_INDEX가 첫번째 ROW에 있군요.

 

인덱스 삭제

--문법
DROP INDEX [인덱스 명]
--예제
DROP INDEX EX_INDEX;

인덱스는 조회성능을 극대화하기 위해 만든 객체인데 너무 많이 만들면 insert, delete, update시에 부하가 발생해 전체적인 데이터베이스 성능을 저하합니다. 고로 안쓰는 인덱스는 삭제시키는것이 좋습니다.

 

▶인덱스(Index) 리빌드(Rebuild)사용 예시 

인덱스를 리빌드하는 이유

인덱스 파일은 생성 후 insert, update, delete등을 반복하다보면 성능이 저하됩니다. 생성된 인덱스는 트리구조를 가집니다. 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어집니다. 이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는것이 좋습니다.

 

인덱스 리빌드 할 대상 조회쿼리

SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
       DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF
FROM   USER_INDEXES I
WHERE   I.BLEVEL > 4
ORDER BY I.BLEVEL DESC

해당쿼리는 index 트리의 깊이가 4이상인 index를 조회하는 쿼리입니다. 해당 쿼리문을 실행하여 검색되는 index가 있다면 리빌딩을 하는것이 좋습니다. 

 

인덱스 리빌드

--문법
ALTER INDEX [인덱스명] REBUILD;
--예제
ALTER INDEX EX_INDEX REBUILD;

위의 쿼리를 실행시키면 인덱스가 리빌드 됩니다. 이렇게 일일히 리빌드 시키기 귀찮으신 분들은 USER_INDEXES에 있는 인덱스를 조회하여 인덱스 리빌드 쿼리를 만들어 한번에 실행시키시면 간편합니다.

 

전체 인덱스 리빌드 쿼리문 만들기

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; 'FROM USER_INDEXES;

 

인덱스(Index)를 남발하지 말아야 하는 이유

개발을 진행할때에 대개 개발서버와 운영서버를 나누어서 관리합니다. 대부분 개발서버에서 개발을 할때에는 적은량의 데이터를 가지고 로직검사를 하며 로직검사에 통과한 코드들이 운영서버에 업데이트가 되죠. 하지만 개발서버에는 잘 동작하던 로직들이 운영서버의 많은량의 데이터들을 처리하다보면 성능이슈가 많이 발생합니다. 그 성능이슈의 주요원인은 바로 데이터베이스에 있습니다. 데이터베이스 관리자는 성능문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성입니다. 그렇게 하여 인덱스를 하나 만들었다고 합시다. 그리고 또 다른 SQL에서문에서 성능이슈가 발생하여 또 인덱스를 만들었다고 합시다. 이렇게 문제가 발생할때마다 인덱스를 생성하면서 인덱스가 쌓여가는것은 결코 좋지않습니다. 인덱스를 만드는것은 하나의 쿼리문을 빠르게는 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래합니다. 그렇기에 인덱스를 생성하는것 보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야합니다. 인덱스생성은 꼭 마지막 수단으로 강구해야 할 문제입니다.