11. ORACLE OBJECT INDEX

INDEX란? 

SQL명령문의 처리 속도를 향상시키기 위해서 컬럼 생성하는 것

 

장점: 검색 속도가 빨라짐(SELECT 문 향상)

단점: 인덱스 위한 추가 저장 공간이 필요
         데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우 성능 저하

  *검색과 데이터 변경 작업속도는 반비례
  *INDEX로 지정된 컬럼으로 검색했을 때 그 검색 결과가 전체 튜플의 10~15% 미만일 때 효과 굿

SELECT * FROM EMP WHERE DEPTNO > 20;
-- DEPTNO 에 INDEX 걸려있고 EMP TABLE 의 튜플 개수가 100라면 
--SELECT 문 결과가 15개 미만 정도 나올 때 효과적

 

 

INDEX 표현식

CREATE [UNIQUE] INDEX 인덱스 명 ON 테이블 명
(컬럼 명, 컬럼 명 | 함수 명, 함수 계산식);

 

 

INDEX 구조 

 

 

INDEX 종류

• 고유 인덱스
  - 중복값 있을 수 X
  - PK 생성하면 자동으로 생성

CREATE UNIQUE INDEX IDX_T12 ON T12(C1);

INSERT INTO T12 VALUES('1234567가', '가나다라마', '1234567가', '가나다라마');
--C1 에다가 중복되는 값을 INSERT 할 수 없음
--ORA-00001: 무결성 제약 조건(SCOTT.IDX_T12)에 위배됩니다

* UNIQUE INDEX는 중복 값이 있는 컬럼에 생성 시 에러 발생

    ex. DEPT_CODE 는 여러명의 사람이 가지고 있어서 중복값 있음

 

• 비고유 인덱스

CREATE INDEX IDX_DEPTCODE
ON EMPLOYEE(DEPT_CODE);

 

• 단일 인덱스 : 한개의 컬럼으로 구성된 인덱스
  - 위에 2개가 단일 인덱스
  
  
• 결합 인덱스(COMPOSITE INDEX) : 2개 이상의 칼럼으로 구성한 인덱스

CREATE INDEX IDX_DEPT
ON DEPARTMENT(DEPT_ID, DEPT_TITLE)

 

• 함수 기반 인덱스(FUNCTION-BASED INDEX) : SELECT 절이나 WHERE 절에 산술 계산식/함수식이 사용된 경우
  * 계산식은 인덱스의 적용을 받지 X

--애초에 자주 사용하는 계산 값 자체에 INDEX 거는 것
CREATE TABLE EMP_SAL
AS SELECT EMP_ID, EMP_NAME, SALARY, BONUS,
	(SALARY + (SALARY + NVL(BONUS, 0)))*12 연봉
FROM EMPLOYEE;

 

 

INDEX 재생성 : 인덱스 엔트리 논리만 제거하고 인덱스 다시 생성하는 것
    => 전체를 다 뜯었다가 다시 붙이는 개념(DROP의 개념은 아님..)

ALTER INDEX 인덱스명 REBUILD;

 

 

INDEX DICTIONARY

SELECT * FROM USER_INDEXES;
SELECT * FROM USER_IND_COLUMNS;
--INDEX 어디 COLUMN 에 걸려있는지 확인

 

 

INDEX 걸린것을 비교문에 사용했을 때 처리속도 빨라짐

SELECT * FROM T11 WHERE C1 = '123'; -- INDEX 걸린 비교문이라 빨라짐
SELECT * FROM T11 ORDER BY C3; --비교문에 걸려있지 않아서 속도 느림

 

 

예시

CREATE TABLE T8 (
    C1 VARCHAR2(10),
    C2 NVARCHAR2(10),
    C3 CHAR(10), 
    C4 NCHAR(10)
);

INSERT INTO T8 VALUES('1234567890', '1234567890', '1234567890', '1234567890');
INSERT INTO T8 VALUES('123456789가', '123456789가', '123456780', '123456789가');
--ORA-12899: "SCOTT"."T8"."C1" 열에 대한 값이 너무 큼(실제: 12, 최대값: 10)
INSERT INTO T8 VALUES('1234567가', '123456789가', '123456789', '123456789가');
--ORA-12899: "SCOTT"."T8"."C3" 열에 대한 값이 너무 큼(실제: 12, 최대값: 10)
INSERT INTO T8 VALUES('1234567가', '123456789가', '1234567가', '123456789가');
--ok
--글자를 한 글자로 인식 -> 한글을 한개의 글자로 인식(3BYTE 아님)

INSERT INTO T8 VALUES('1234567가', '가나다라마바사아자차', '1234567가', '가나다라마바사아자차');

INSERT INTO T8 VALUES('1234567가', '가나다라마바사아자AB', '1234567가', '가나다라마바사아자차');
--ORA-12899: "SCOTT"."T8"."C2" 열에 대한 값이 너무 큼(실제: 11, 최대값: 10)
--알파벳도 한개의 글자로 인식
INSERT INTO T8 VALUES('1234567가', '가나다라마','1234567가',  '가나다라마바사아자차');
INSERT INTO T8 VALUES('1234567가', '가나다라마','1234567',  '가나다라마바사아자차');





CREATE TABLE T9 (
    C1 VARCHAR2(10),
    C2 NVARCHAR2(1000),
    C3 CHAR(10), 
    C4 NCHAR(1000)
);
--*Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
--           otherwise, the length specified was > 4000.


CREATE TABLE T10 (
    C1 VARCHAR2(4000),
    C2 NVARCHAR2(1000),
    C3 CHAR(10), 
    C4 NCHAR(1000)
);


CREATE TABLE T11 (
    C1 VARCHAR2(4000),
    C2 NVARCHAR2(1000),
    C3 CHAR(2000), 
    C4 NCHAR(1000)
);

--*Cause:    for datatypes CHAR and RAW, the length specified was > 2000;
--           otherwise, the length specified was > 4000.
--CHAR: 2000까지, VARCHAR2 는 4000까지