인덱스

B* 트리

- 노드가 넘치면(DBMS 기준으로 block에 데이터가 다 차면) 양쪽 형제 노드로 key를 분배.

- 모든 형제노드가 full이면 위 아래 확장.

- 자료는 리프노드에 저장되어있으며 모든 자료에서 루트까지 높이가 똑같다

- leaf node 좌우로 linkedlist로 연결되어있다

- 참고로 null은 자료가 저장되지 않으며 (대소비교가 불가능하기 때문에), 복합인덱스일 경우, 한 컬럼에만 값이 있어도 인덱스로 자료가 저장된다

 

Full Table Scan

많은 양의 데이터를 검색할 때 유용하다

High water Mark 아래의 모든 I/O를 다 읽는다

Multi Block I/O가 수행되며 한번에 읽는 양은 db_file_multiblock_read_count에 정의되어있다

이때 한 extent가 10블록이고, db_file_multiblock_read_count가 8이라면,

처음에 8 block을 읽고 그 다음은 남은 2 block 밖에 읽지 못하게 된다

따라서, extent는 read_count의 배수가 되게 만드는 것이 좋다

기본적으로 인덱스는 multi-block read를 지원하지 않는다.

 

인덱스 정렬

default는 리프노드는 오름차순 정렬이며  내림차순도 가능하다

create index emp_ename_desc_idx on emp(ename desc);

 

자료가 특정 값에 쏠려있으면, reverse 키 인덱스도 가능하다

바이트를 역으로 뒤집은 인덱스이다.

인덱스를 골고루 분산시킬 수 있다.

i.e. 1234567 → 764321

단, 값을 뒤집었기 때문에 range scan이 안된

create index ord2_ordid_rev_idx on ord2(order_id) reverse;

select * from ord2 where order_id = 300;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allSTATS LAST +COST')) ;
-- 이 문장은 생략하도록 하겠음
-- |*  2 |   INDEX RANGE SCAN          | ORD2_ORDID_REV_IDX |      1 |      1 |     1   (0)|      0 |00:00:00.01 |       1 |

select * from ord2 where order_id > 300;
-- |*  1 |  TABLE ACCESS FULL| ORD2 |      1 |    105 |     3   (0)|     50 |00:00:00.01 |       3 |

 

특정 인덱스 안쓰게 설정하기

서비스상 필요없게 된 인덱스라도 바로 삭제하지 않고, 미사용으로 설정 후 서비스를 지켜보는 것이 좋다

alter index emp_ename_ix invisible; -- emp_ename_ix 미사용
alter index emp_ename_ix visible;   -- emp_ename_ix 사용

 

Index Unique Scan

컬럼이 유니크한 경우, = 연산으로 비교한 컬럼에 한해서 index unique scan을 수행한다

-- EMPLOYEE_ID가 UNIQUE인 경우
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 121;

-- |*  2 |   INDEX UNIQUE SCAN         | EMPLOYEES_IX01 |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |

employee_id가 unique가 아니라면 122까지 값을 찾아야 한다. 따라서 성능을 높이려면 unique한 컬럼은 unique로 설정해주는 것이 좋다

 

Index Range Scan

가장 일반적인 index 접근법

조건이 범위로 주어지거나 non-unique한 컬럼에 = 비교연산을 할 때 수행된다

SELECT * FROM EMP WHERE EMPNO > 5000;

-- |*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IX |      1 |      4 |     1   (0)|      6 |00:00:00.01 |       1 |

한가지 특징으로는, 출력값이 정렬된다

B*트리 리프노드

그 이유는 인덱스로 자료가 리프노드에 저장될 때 리프노드가 정렬되기 때문이다. 

order by가 있어도 별도로 order by를 할 필요가 없다

SELECT *
FROM EMP
WHERE EMPNO < 5000
ORDER BY EMPNO DESC;

-- |*  2 |   INDEX RANGE SCAN DESCENDING| EMP_EMPNO_IX |      1 |     10 |     1   (0)|      7 |00:00:00.01 |       1 |

INDEX RANGE SCAN DESCENDING 는 빨간색 화살표 반대방향으로 자료를 읽었다는 뜻이다. 이러한 방법으로 desc도 SORT ORDER BY라는 operation 없이 order by를 수행할 수 있다

 

SELECT *
FROM EMP
WHERE EMPNO > 5000 
ORDER BY JOB;

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |     3 (100)|      6 |00:00:00.01 |       2 |       |       |          |
|   1 |  SORT ORDER BY               |              |      1 |      4 |     3  (34)|      6 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP          |      1 |      4 |     2   (0)|      6 |00:00:00.01 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | EMP_EMPNO_IX |      1 |      4 |     1   (0)|      6 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------

 

위와 같은 환경에서는 job을 기준으로 order by를 하기 때문에, EMPNO로 index range scan을 통해 EMPNO > 5000이라는 조건을 찾고 SORT ORDER BY 연산으로 job 기준으로 sort를 수행하게 된다

 

Index Full Scan

모든 Leaf Block을 읽음.

Multi-Block Read가 가능한 Table Full Scan에 비해 장점이 없다고 생각할 수 있지만, 인덱스되어있는 컬럼만 읽어도 되는 경우 굳이 모든 테이블의 값을 읽어올 필요가 없다.

 

Index Fast Full Scan

유일하게 Multi-Block Read 지원한다 (너무 다량의 데이터를 scan하는 경우 일부 다른 인덱스 스캔에서도 Multi-Block read 발생하기는 한다)

COUNT, SUM 등의 쿼리에서 쓰인다

SELECT CUST_CITY, COUNT(*)
FROM CUSTS
WHERE CUST_CITY IS NOT NULL
GROUP BY CUST_CITY;

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |      1 |        |    55 (100)|     50 |00:00:00.01 |     200 |       |       |          |
|   1 |  HASH GROUP BY        |                |      1 |    620 |    55   (4)|     50 |00:00:00.01 |     200 |  3105K|  1381K|          |
|*  2 |   INDEX FAST FULL SCAN| CITY_BIRTH_IDX |      1 |  55500 |    53   (0)|  55500 |00:00:00.01 |     200 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

 

Index Join

테이블 접근 없이 인덱스만으로 where 절 수행이 가능한 경우

SELECT DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES E
WHERE DEPARTMENT_ID = 80
AND JOB_ID = 'SA_REP';

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |      1 |        |     3 (100)|      2 |00:00:00.01 |       2 |       |       |          |
|*  1 |  VIEW              | index$_join$_001 |      1 |      1 |     3  (34)|      2 |00:00:00.01 |       2 |       |       |          |
|*  2 |   HASH JOIN        |                  |      1 |        |            |      2 |00:00:00.01 |       2 |  1035K|  1035K|  718K (0)|
|*  3 |    INDEX RANGE SCAN| EMPLOYEES_IX02   |      1 |      1 |     1   (0)|      3 |00:00:00.01 |       1 |       |       |          |
|*  4 |    INDEX RANGE SCAN| EMPLOYEES_IX04   |      1 |      1 |     1   (0)|      3 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Department_id = 80 인 row_id와 JOB_ID = 'SA_REP'인 row_id를 join한다

 

Index Skip Scan

결합 인덱스의 선행컬럼이 없어도 Index가 사용될 수 있다

다음과 같은 상황을 생각해보자

CREATE INDEX ON EMPLOYEES(GENDER,AGE);
SELECT * FROM EMPLOYEES WHERE AGE BETWEEN 20 AND 29;

Gender가 조건에 없더라도 인덱스 활용이 가능하다

Index Skip Scan 설명

빨간색 경고 표시로 된 영역에는 WHERE AGE BETWEEN 20 AND 29 를 만족하는 조건이 나올 수 없다

따라서 인덱스가 활용가능하다

 

인덱스 활용 불가

1. 부정 비교

SELECT *
FROM EMP
WHERE DEPTNO <> 20;

 

2. 컬럼 변형

-- CUST_ID는 numeric
SELECT *
FROM CUSTS
WHERE CUST_ID LIKE '7%';

 

 

2. 컬럼 변형

-- CUST_ID는 numeric
SELECT *
FROM CUSTS
WHERE CUST_ID LIKE '7%';

문자, 숫자 비교 있을 때는 무조건 숫자를 문자로 바꾼다, cust_id가 문자로 변형되어 index 불가

Function Based Index를 활용하여 변형되는 컬럼을 인덱스 설정한다

 

3. IS NULL

NULL은 인덱스로 저장되지 않아 인덱스 활용 불가

서비스에 중요한 경우 function based index 활용 가능하다

SELECT * FROM CUSTS
WHERE CUST_EMAIL IS NULL;

CREATE INDEX CUST_EMAIL_FBI ON CUSTS(NVL(CUST_EMAIL,'NULL'));

 

4. 와일드 카드가 문자 앞에

와일드카드가 앞에 있으면 인덱스 활용이 불가

마찬가지로 function based index 활용 가능하다

SELECT * FROM EMP WHERE ENAME LIKE '%S';

CREATE INDEX EMP_ENAME_FBI ON EMP(REVERSE(ENAME));
SELECT * FROM EMP WHERE REVERSE(ENAME) LIKE 'S%';

 

 

강의정보

https://www.multicampus.com/search?siteAppntSrcwdNm=&siteRalSrcwdNm=&kwd=%EC%97%85%EB%AC%B4%EC%97%90+%EB%B0%94%EB%A1%9C+%EC%93%B0%EB%8A%94+SQL+%ED%8A%9C%EB%8B%9D&input_kwd=%EC%97%85%EB%AC%B4%EC%97%90+%EB%B0%94%EB%A1%9C+%EC%93%B0%EB%8A%94+SQL+%ED%8A%9C%EB%8B%9D&category=TOTAL 

멀티캠퍼스