[이것이 오라클이다] 09장 3교시: [Oracle] 인덱스의 생성/변경/삭제

* Youtube Link - https://www.youtube.com/watch?v=mzvtZU4Y0ko&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=25

 

9.4 인덱스 생성/변경/삭제

- 자동 생성 인덱스: PRIMARY KEY, UNIQUE

- 직접 생성 인덱스: CREATE문을 사용해서 만들 수 있음

9.4.3 인덱스 제거

실습

PK에 다시 이름을 지어서 만들기 위해

01. ALTER TABLE userTBL

DROP PRIMARY KEY CASCADE; 를 실행하였다.

CASCADE를 통해서 PK와 FK를 지울 수 있다.

그리고 

02. ALTER TABLE userTBL

ADD CONSTRAINT FK_userTbl_buyTbl

FOREIGN KEY (userID)

REFERENCES userTBL(userID);

FK를 지정할 수 있다.

- USER_INDEXES 및 USER_IND_COLUMNS의 INNER JOIN

INDEX의 이름과 INDEX_TYPE 그리고 UNIQUENESS를 확인하면 된다.

- USER_INDEXES

- LEAF_BLOCKS: 1개

- DISTINCT_KEYS: 10개 (PK인 userID들의 종류의 개수)

- NUM_ROWS: 10개 (행 개수)

로 조회가 되었다.

1 블럭은 8k이다. 

총 10건의 데이터이기 때문에 1블럭이면 충분하다. (8k)

- 계획 설명

3번째 버튼인 '계획 설명...(F10)' 버튼을 클릭하면

SELECT문의 계획 설명을 볼 수 있다.

인덱스를 사용한 것을 볼 수 있다. UNIQUE SCAN을 하였다.

인덱스가 생성되지 않은 열을 WHERE절을 통해서 검색을 해보니

FULL TABLE SCAN을 한 것을 볼  수 있다.

INDEX가 없으므로 테이블 전체를 검색했다는 의미이다.

엄청나게 오래 걸렸다. 데이터가 많다면.

- 직접 인덱스 생성

중복을 허용하는 인덱스를 생성하였다.

만일 중복된 데이터가 있는 열은 UNIQUE INDEX를 만들 수 없다.

사용자 이름은 중복된 데이터가 없으므로 UNIQUE INDEX가 잘 만들어진 것을 볼 수 있다.

전체적으로 확인해 보면

이렇게 NONUNIQUE INDEX 1건, UNIQUE INDEX 2건이 만들어진 것을 볼 수 있다.

- UNIQUE INDEX 데이터 입력 오류

USERNAME 컬럼은 UNIQUE INDEX가 걸려있으므로

중복된 데이터를 입력하고자 하면 제약조건에 걸려 오류가 발생한다.

- 두 컬럼을 합쳐서 하나의 INDEX로 생성

잘 생성된다.

그리고 확인을 해보면

BIRTHYEAR, USERNAME 두 컬럼을 합쳐서 IDX_USERTBL_USERNAME_BIRTHYEAR이라는 하나의 INDEX로 만든 것을 볼 수 있다.

그리고 하나의 인덱스로 합쳐진 컬럼들을 조합을 해서 WHERE 절에서 사용을 하면 

실제로 INDEX가 실행이 된다.

굉장히 결과가 빨리 나왔을 것이다.

USERNAME 컬럼에 INDEX가 없더라도

조합되었던 INDEX, USERNAME + BIRTHYEAR 인덱스를 사용을 하면

차선책으로 조합되어 있는 IDNEX를 찾아서 더 빨리 찾게 한다.

무조건 이 것을 사용하는 것은 아니며 오라클이 선택해서 사용을 한다.

- 선택도가 낮은 인덱스

국번을 인덱스로 만들었다.

국번의 경우 종류가 굉장히 적다.

그러면 인덱스를 사용해서 검색을 하면 굉장히 많은 시간이 소요가 되게 되는 것이다. 종류가 적기 때문이다.

데이터의 중복도가 높다. 선택도가 낮다. 이러한 경우 인덱스를 안 만드는 것이 낫다.

- 인덱스의 삭제

INDEX의 삭제는 DROP INDEX를 통해서 삭제가 가능하다.

그러나 PK의 경우 DROP INDEX로 삭제를 할 수 없다.

USER_CONSTRAINTS 테이블에서 조회를 해보면

다음과 같이 BUYTBL에 FK로 지정되어 있기 때문이라는 것을 알 수 있다.

다음과 같이 ALTER TABLE userTBL DROP PRIMARY KEY CASCADE; 명령어를 통해서

CASCADE로 FK로 지정된 것들을 전부 다 같이 삭제해준다.

그러면 PK로 자동 생성되는 INDEX도 삭제할 수 있다.

무조건 팍팍 지우는 것이 바람직하다고 볼 수는 없다. 오류를 통해서 내용을 확인하고 지우는 것이 바람직하다.

 

감사합니다.