오라클 INDEX, 단일/복합(결합) 인덱스(Single Column/Composite Index)

 

오라클 INDEX, 단일/복합(결합) 인덱스(Single Column/Composite Index)

단일 인덱스는 하나의 칼럼으로 인덱스가 구성된 것이며, 복합 인덱스는 두개 이상의 컬럼으로 인덱스가 생성된 것 입니다.

다음 실습에서 단일컬럼 및 복합 컬럼에 대해 인덱스를 생성해 보겠습니다.

실습

단일 컬럼에 대해 인덱스를 생성하고 딕셔너리 뷰dictionary view에서 생성된 인덱스를 확인합니다.

EMP 테이블의 ename 컬럼에 대해 인덱스를 생성 합니다.

CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME);

<실행결과>

Index IDX_EMP_ENAME이(가) 생성되었습니다.

USER_INDEXES 뷰는 사용자가 생성한 인덱스 정보를 제공하는 딕셔너리 뷰 입니다.

생성된 인덱스를 확인 합니다.

SELECT TABLE_NAME, INDEX_NAME 

FROM USER_INDEXES 

WHERE TABLE_NAME = 'EMP';

<실행결과>

	TABLE_NAME	INDEX_NAME
1	EMP	PK_EMP
2	EMP	IDX_EMP_ENAME

CREATE INDEX로 ename 컬럼에 하나의 인덱스를 생성했는데 왜 2건이 조회될까요? 왜냐하면 EMP 테이블을 생성할 때 empno 컬럼을 기본키primary key로 지정하고 기본키 이름을 PK_EMP 라고 지정을 해서 그 이름으로 인덱스를 생성한 것 입니다. 오라클에서 테이블의 컬럼에 대해 기본키를 지정하면 NOT NULL + UNIQUE 제약조건이 걸리고 별도의 영역에 해당 컬럼을 순서대로 정렬해서 인덱스를 생성한다는 것을 다시 한번 상기해 주세요.

실습

2개 이상의 복합 컬럼에 대해 인덱스를 생성하고 딕셔너리 뷰에서 생성된 인덱스를 확인합니다.

EMP 테이블의 ename, sal 컬럼에 대해 인덱스를 생성 합니다.

CREATE INDEX IDX_EMP_ENAME_SAL ON EMP(ENAME, SAL);

<실행결과>

Index IDX_EMP_ENAME_SAL이(가) 생성되었습니다.

생성된 인덱스를 확인 합니다.

SELECT TABLE_NAME, INDEX_NAME 

FROM USER_INDEXES 

WHERE TABLE_NAME = 'EMP';

<실행결과>

	TABLE_NAME	INDEX_NAME
1	EMP	PK_EMP
2	EMP	IDX_EMP_ENAME
3	EMP	IDX_EMP_ENAME_SAL

 

#오라클인덱스, #인덱스, #인덱스란, #인덱스종류, #ORACLE, #ORACLE인덱스

오라클 인덱스(Oracle Index)란? 개요 및 구조, 작동방식, 필요한 이유

 

오라클 인덱스(Oracle Index)란? 개요 및 구조, 작동방식, 필요한 이유

인덱스 소개

인덱스는 테이블의 칼럼에 대해 생성되는 객체로 별도의 공간에 컬럼값들을 정렬한 후 생성 합니다. 데이터 검색 속도를 향상 시키기 위해 사용되며, 포인터를 이용하여 데이터를 랜덤 액세스random access 합니다. 예를들어 1억건 이상의 고객데이터가 있고 고객이름으로 인덱스가 생성되어 있지 않다고 했을 때  “홍길동” 고객을 검색하려면 처음 행부터 “홍길동” 고객이 출현할 때 까지 검색을 해가야 합니다. 운이 없어서 마지막 행에 “홍길동” 데이터가 있었다면 검색속도는 아주 느려질 것 입니다. 그러나 고객이름으로 인덱스가  생성되어져 있다면 랜덤 액세스로 바로 찾아 갈 수 있습니다. 이 두경우는 하늘과 땅 차이죠^^ 그만큼 인덱스는 중요하답니다.

인덱스는 한번 생성되면 오라클에 의해 내부적으로 자동 관리 됩니다.

오라클에서 Create Index로 인덱스를 생성하면 균혅바힌 트리 형태인 B*Tree(Balanced Tree) 인덱스가 생성되며 어떠한 행을 접근하든지 액세스 타임이 동일한 것이 특징 입니다. 인덱스를 생성해야 하는 컬럼은 SQL문의 WHERE절 or 조인절에 빈번히 출현되는 칼럼 입니다. 특히 칼럼값의 분포도가 10%~15% 인 경우 인덱스가 효율적 입니다. 주의할 점은 삽입, 삭제가 빈번하여 값이 자주 변경되는 테이블에는 인덱스의 사용을 자제해야 합니다. 왜냐하면 테이블의 값이 입력/수정/삭제 될 때 테이블만 갱신하는 것이 아니라 인덱스도 같이 갱신해 줘야 하기 때문 입니다.

[그림 12.1 인덱스구조]

사원(EMP) 테이블의 사원명(ename) 컬럼에 인덱스가 생성되어 있는 모습 입니다. USER라는 테이블스페이스에 EMP 테이블 데이터 원본이 존재하고, 인덱스는 별도의 이름을 가진 다른 테이블스페이스에 생성이 되어 있습니다. 그림의 맨 좌측 16진수 문자열 컬럼은 ROWID 컬럼으로 모든 행들은 물리적인 주소를 가리키는 의사컬럼이 입니다. 

EMP 테이블에서 “CLARK” 사원의 급여를 조회 합니다. 

 “CLARK” 사원의 sal 컬럼을 조회한다면 SQL은 SELECT sal FROM EMP WHERE ENAME = ‘CLARK’ 이러한 형태가 되고 인덱스를 경우하여 데이터를 조회한다고 가정을 했을 때 오라클은 원본 테이블 데이터가 있는 USERS 테이블스페이스table spaces의 EMP 테이블에 접근하는 것이 아니라 그림의 오른쪽 인덱스 영역에서 CLARK을 랜덤 액세스하여 찾고 그 ROWID 값을 이용하여 다시 원본테이블에 와서 sal 컬럼값을 추출 하는 것 입니다. 당연히 데이터 건수가 많은 경우에 효율적이고 건수가 얼마되지 않다면 비효율 적 일수도 있습니다. 여기서 건수가 많다 적다는 시스템 환경(CPU, MEMORY, DISK I/O 속도)에 따라 그 기준은 달라 집니다.

인덱스를 생성하는 기본형식은 아래와 같습니다.

[기본형식]

CREATE   [UNIQUE] INDEX   index_name  ON   table_name (Column|Expr[,Column|Expr]...);

인덱스 종류

인덱스를 만드는형태에 따라 하나의 컬럼으로 인덱스를 만드는 단일인덱스, 여러개의 컬럼으로 인덱스를 만드는 복합 인덱스, 오름차순 또는 내림차순 인덱스, SQL함수나 표현식에 생성하는 함수기반 인덱스로 구분할 수 있습니다.

실제 인덱스가 내부적으로 어떻게 구성되어 지는지에 따라서 일반적인 오라클 인덱스인 균형잡힌 트리 형태의 B*Tree 인덱스, 비트를 이용하여 컬럼 값을 간결하게 저장하는 Bitmap 인덱스, 두 테이블 조인시 조인한 결과 칼럼에 대해 비트맵 인덱스를 생성하는 Bitmap Join 인덱스, 인덱스 컬럼의 값을 역으로 변경하여 B*Tree 인덱스를 구성한 역전환키 인덱스reverse key indexes 등이 있습니다.

인덱스가 꼭 필요한 이유

인덱스는 테이블 데이터에 액세스access 할 때마다 모든 행을 검색하지 않고도 데이터를 빠르게 찾는 데 사용됩니다. 기업에서 데이터베이스를 많은 자금을 들여 도입하는 이유중 하나는 빠른 검색 속도 입니다. 테이블에 데이터가 100건 있으나 1억건 있으나 조회 속도는 비슷해야 한다는 이야기죠. 

실무 현장에서 개발할 때 개발환경의 테이블에는 몇개의 행이 없지만 실 운영 상태가 되어 데이터 이행등을 한 후에는 어마어마한 데이터가 있을 수도 있다는 사실을 명심하고 SQL문을 작성할 때는 이점에 항상 신경을 쓰고 개발을 해야 합니다.

 

#인덱스란, #인덱스, #인덱스개요, #오라클인덱스, #ORACLE인덱스, #ORACLE, #오라클INDEX, #ORACLEINDEX​