[211004] INDEX (TABLE SCAN / INDEX SCAN)

INDEX

 

SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해 생성하는 오라클 객체

내부 구조는 B*트리 형식으로 구성되어 있음

 

• 장점

검색 속도가 빨라지고 시스템에 걸리는 부하를 줄여 시스템 전체 성능 향상

 

• 단점

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

 

 주로 어떠한 검색을 많이 하는지, 거기에 맞춰 인덱스 구성을 할 수 있는지 여부 따져보고 인덱스로 속도 향상을 할 수 있는 상황이면 인덱스 설정 또는 기존 인덱스 수정하는 방법으로 검색 속도를 높임

 일반적으로 프라이머리 키 설정된 ID로 기본 인덱스를 건다. 그런데 우리가 특정 검색 조건에 의해 셀렉트할 때 속도가 느려진다면 인덱스를 새로 설정해서 효율적이게 할 수 있다.

검색 종류에 따라 인덱스 새로 변경 시켜줄 때 사용하므로 초반보다는 차후에 주로 이루어진다. 

 500페이지짜리 책 안에서 특정 내용을 찾으려 할때 첫번째 페이지부터 순차적으로 훑어볼텐데 뒤에 별도의 색인이 있고 그걸 따라간다면 더 빨리 찾을 수 있을 것. 

 

INDEX 표현식 CREATE [UNIQUE] INDEX 인덱스 명 ON 테이블 명(컬럼 명, 컬럼 명 | 함수 명, 함수 계산식);    -- 인덱스 중복 설정 가능 SELECT * FROM USER_IND_COLUMNS; 컬럼으로 지정하는 것이 아닌 함수 명, 함수 계산식이 들어가는 것도 할 수는 있지만 권장하지 않음.

INDEX 구조 SELECT ROWID, EMPLOYEE_ID, FIRST_NAME FROM EMPLOYEES;

 - IDX_EMPID

KEY	ROWID
200	AAAE7UAABAAALC5AAA
201	AAAE7UAABAAALC5AAB
202	AAAE7UAABAAALC5AAC

AAAE7U   AAB   AAALC5  AAA

데이터            파일번호    BLOCK번호    ROW번호
오브젝트번호  

 

▶ INDEX 종류

1. 고유 인덱스(UNIQUE INDEX)
   중복 값이 포함될 수 없음, PRIMARY KEY 제약조건을 생성하면 자동으로 생성됨

2. 비고유 인덱스(NONUNIQUE INDEX)
   빈번하게 사용되는 일반 컬럼을 대상으로 생성, 주로 성능 향상을 위한 목적

3. 단일 인덱스(SINGLE INDEX)
   한 개의 컬럼으로 구성한 인덱스

4. 결합 인덱스(COMPOSITE INDEX)
   두 개 이상의 컬럼으로 구성한 인덱스

5. 함수 기반 인덱스(FUNCTION-BASED INDEX)
   SELECT절이나 WHERE절에 산술 계산식/함수식이 사용된 경우, 계산식은 인덱스의 적용을 받지 않음

 

 

▶ INDEX 재생성

DML 작업(특히 DELETE 명령)을 수행한 경우, 해당 인덱스 엔트리가 논리적으로만 제거되고 실제 엔트리는 남아있게 되므로 제거된 인덱스가 필요 없는 공간을 차지하고 있지 않도록 인덱스를 재생성 해야 함

표현식 ALTER INDEX 인덱스명 REBUILD;

 

참고

▶ INDEX 활용한 정렬

    인덱스를 활용하여 ORDER BY 가 아닌 WHERE 조건만 가지고 정렬 할 수 있다. 

SELECT * FROM EMPLOYEE ORDER BY EMP_NO; SELECT * FROM EMPLOYEE WHERE EMP_NO > ‘0’; * 둘 다 주민번호 기준 오름차순 정렬이지만 ORDER BY로 정렬하는 것보다 인덱스를 활용하는 것이 더 좋은 성능을 보임

---

TABLE SCAN / INDEX SCAN

TABLE SCAN : 300 페이지 책이 있고 처음 1 페이지부터 300 페이지까지 쭉 훝는 것

INDEX SCAN : 인덱스를 보고 원하는 페이지에 찾아가는 것

 

 테이블 스캔으로 검색하게 했을 때 속도가 느려졌으면 인덱스를 만들어서 인덱스 스캔하도록 만들어야겠다고 생각하면 된다. 기본적으로 스캔할 데이터 양이 아~주 많은 경우에 인덱스를 사용하는 것이 효율적이다.

 

실습용 오라클 클라우드 접속

메뉴 - 자율데이터베이스 - 구획 - testDB - Tools - Database Actions

 

사용자계정 접속 (사용자 이름 : ADMIN  비밀번호 : L******12345) - SQL - 쿼리작성

ADMIN 계정으로 접속한 것이기 때문에 당연히 보이지 않는다. (테이블은 USER1 계정에 만들었음)

따라서 FROM USER1.EMPLOYEES 로 작성 후 F10 또는 실행 설명 계획 아이콘 클릭.

TABLE SCAN

 

INDEX SCAN

 

참고

<VC CODE 로 실행 설명 계획 보기>

SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES'; -- 실행 설명 계획을 보기 위한 Oracle 구문 (수정 전) EXPLAIN PLAN SET STATEMENT_ID = 'PLAN0' INTO PLAN_TABLE FOR SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID <= 200;                                 -- 필요에 따라 교체할 부분 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'PLAN0', 'ALL')); -- 실행 설명 계획을 보기 위한 Oracle 구문 EXPLAIN PLAN SET STATEMENT_ID = 'PLAN1' INTO PLAN_TABLE FOR SELECT EMPLOYEE_ID      , FIRST_NAME || ' ' || LAST_NAME      , SALARY   FROM (SELECT * FROM EMPLOYEES ORDER BY SALARY)  WHERE ROWNUM <= 5; SELECT STATEMENT_ID 실행계획명, PLAN_ID 계획ID      , ID, PARENT_ID 상위ID      , DEPTH 깊이, POSITION      , OPERATION || ' ' || OPTIONS 실행내용      , COST 비용      , CARDINALITY 조회수      , BYTES 용량   FROM PLAN_TABLE  WHERE STATEMENT_ID = 'PLAN1'; EXPLAIN PLAN SET STATEMENT_ID = 'PLAN2' INTO PLAN_TABLE FOR SELECT A.EMPLOYEE_ID      , A.FIRST_NAME      , A.LAST_NAME      , A.DEPARTMENT_ID      , C.DEPARTMENT_NAME AS 부서명      , B.JOB_TITLE AS 직무명   FROM EMPLOYEES A JOIN JOBS B     ON A.JOB_ID = B.JOB_ID   JOIN DEPARTMENTS C     ON A.DEPARTMENT_ID = C.DEPARTMENT_ID; SELECT STATEMENT_ID 실행계획명, PLAN_ID 계획ID      , ID, PARENT_ID 상위ID      , DEPTH 깊이, POSITION      , OPERATION || ' ' || OPTIONS 실행내용      , COST 비용      , CARDINALITY 조회수      , BYTES 용량      , TIME 시간   FROM PLAN_TABLE  WHERE STATEMENT_ID = 'PLAN2'; SELECT * FROM PLAN_TABLE; drop table plan_table; -- 실행할 때마다 증가 되므로 삭제해가면서 확인 delete from plan_table where statement_id='PLAN0'; delete from plan_table where statement_id='PLAN1'; delete from plan_table where statement_id='PLAN2'; -- 결과 테이블에서 첫번째 행 SELECT STATEMENT 쪽 전체비용 보기 (참고용)