SELECT-LIST 컬럼 가공시 정렬연산 수행 확인 및 개선방법

인덱스가 Id,  ch_date, ch_order 순으로 생성되어 있을 경우 MIN 값을 구해도 별도의 정렬연산을 수행하지 않는다. 수직적 탐색을 통해서 가장 왼쪽지점에서 보는 최소 값이 바로 구하고자 하는 값이기 때문이다.


1
SELECT MIN(ch_date) FROM scott.SORT_TEST WHERE ID = ‘C’;
cs

MAX의 경우도 마찬가지이다. MIN과 다른 점은 왼쪽에서 찾는게 아니라 가장 오른쪽에 있는 데이터를 찾는다는 점이다.

1
SELECT MAX(ch_date) FROM scott.SORT_TEST WHERE ID = ‘C’;
cs

그래서 두 개의 실행계획을 살펴보면 인덱스 리프 블록의 왼쪽(MIN) 또는 오른쪽 (MAX)에서 레코드 하나(FIRST ROW)만 읽고 멈춘다.

1
SELECT MAX(TO_DATE(ch_date)) FROM scott.SORT_TEST WHERE ID = 'C';
cs

이 경우에는 MAX일지라도 ch_date가 내부적으로 변경을 한 뒤에 최대값을 찾기 때문에 정렬을 한뒤에 진행이 가능하다.


하지만 이걸 반대로 바꿔서 진행하면 최대 값을 찾고 그 값을 TO_DATE()로 변경하기 때문에 큰 문제 없이 FIRST 항목만 찾게된다.

출처 : 친절한 SQL 튜닝

댓글()
  1. 2019.03.30 14:54 댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

인덱스를 이용한 sort 연산 생략

인덱스 Range Scan이 가능하려면 가공하려는 컬럼의 데이터가 정렬되어 있어야 가능하다. 인덱스는 그렇기 때문에 정렬이 되어있다. 그래서 인덱스를 사용하는 이유가 있다. 

테이블 생성

1
2
3
4
create table sort_test (
id char,
ch_date date,
ch_order varchar(10));
cs


제약조건 추가

1
ALTER TABLE SCOTT.SORT_TEST ADD CONSTRAINT ODER_PRIMARY PRIMARY KEY (ID,CH_DATE,CH_ORDER);
cs


데이터


실행계획

PK를 기준으로 알아서 정렬이 되어있다. ( ID -> CH_DATE -> CH_ORDER 순서대로)


만약 별도로 order by를 sql에 지정한다고 해도 옵티마이저는 sort order by를 진행하지 않는다. 왜냐면 이미 pk 인덱스 생성시에 결과 집합에 대한 order by가 된 상태로 진행되기 때문이다.


만약 이렇게 정렬 연산을 생략가능하게 구성되어 있지 않다면 SORT ORDER BY가 추가 될 것이다.

인덱스에서 값을 찾을 때 인덱스 리프 블록에서 각 블록들은 더블 링크드 리스트로 연결되어있다. ASC 정렬인 경우에는 왼쪽에서 오른쪽 DESC인 경우에는 오른쪽에서 왼쪽으로 진행된다. 

각 DESC (내림차순)으로 사용하도록 쿼리를 작성하고 실행계획을 확인해도 별도의 SORT 작업은 진행하지 않는것을 알 수있다.

ORDER BY를 이용한 정렬 컬럼 가공
인덱스에서 사용되는 컬럼을 가공하면 INDEX가 타지 않는다고 알고있다. 근데 앞에서 살펴본 부분은 where조건에서 컬럼을 가공한 경우였다.

이번에는 인덱스에서 선언한 구조와 반대되는 쿼리를 짜보자. 위의 쿼리에서 PK 인덱스는 ID,CH_DATE,CH_ORDER 순서로 인덱스를 만들었다. 그렇기 때문에 ID -> CH_DATE -> CH_ORDER 순서로 정렬을 하면서 데이터를 찾는다.

하지만 다음과 같은 쿼리는 어떨까?

생성된 인덱스와 다른 기준으로 CH_DATE -> CH_ORDER 으로 정렬을 진행하였기 때문에 다시 정렬연산이 필요로 하게된다.


정리하면 인덱스 생성시에 sort가 되기 때문에 order by를 하여도 별도의 sort 연산을 하지 않는다.


댓글()

인덱스 Range Scan이 되기 위한 선행 조건

인덱스 Range Scan이 되기 위한 선행 조건


학교이름, 나이, 이름, 주소로 구성된 테이블이 있다고 가정해보자.



빠른 검색을 위해서 인덱스를 학교 이름, 나이, 이름으로 구성해서 만들었다고 가정해보자.


CREATE  INDEX SCOTT.student_idx

ON SCOTT.STUDENT_TEST ("SCHOOL_NAME" ASC,"AGE" ASC,"NAME" ASC);


인덱스 구성의 순서로 인해 학교순으로 정렬하고, 나이로 정렬하고, 이름으로 정렬해서 데이터를 찾는다.


그렇기 때문에 이름을 조건으로 데이터를 검색하였을 때 결국 모든 리프노드를 다 검색해야한다.





그렇기 때문에 인덱스를 Range Scan  하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야한다.



그렇다면 만약 인덱스에 사용된 컬럼이 가공 되었으면 인덱스 Range Scan이 지정이 되지 않는거가??


다음 예를 살펴보자.


SELECT * FROM student_test WHERE name = '정철' and substr(SCHOOL_NAME, 0, 1) = :SCHOOL_NAME;


위와 같이 쿼리를 수행하려고 할 때 아래와 같이 인덱스를 구성해보자.


CREATE  INDEX SCOTT.student_idx

ON SCOTT.STUDENT_TEST ("NAME" ASC,"AGE" ASC,"SCHOOL_NAME" ASC);


그리고 실행쿼리에 대해 실행계획을 확인해 보면 인덱스 Range Scan이 가능한 것을 알 수 있다.


인덱스에 사용되는 컬럼이 조작되면 인덱스 Range Scan이 되지 않는다고 알고 있었는데 의아할 수도 있다.





인덱스 Range Scan이 가능한 이유는 인덱스를 구성하는 첫 번째 컬럼이 가공되지 않았기 때문이다.


인덱스 Range Scan을 사용하기 위해서는 인덱스를 사용하는 첫 번째 컬럼이 가공되지 않으면 사용이 가능하다.





인덱스를 타기만 하면 튜닝이 종료되는건가??


대부분의 개발자가 실행계획 확인 없이 SQL 작성한다. 그리고 인덱스 Range Scan이 지정된 것만 확인하면 추가적으로 확인하지 않는다.


위의 테이블에서 인덱스를 다음과 같이 지정해보자.


CREATE  INDEX SCOTT.student_idx

ON SCOTT.STUDENT_TEST ("NAME" ASC,"SCHOOL_NAME" ASC);


그리고 학생 검색을 위해 다음 쿼리 두 개를 살펴보자.


SELECT * FROM student_test WHERE name = '정철' and substr(SCHOOL_NAME, 0, 1) = :SCHOOL_NAME;

 SELECT * FROM student_test WHERE name = '정철' and SCHOOL_NAME LIKE :SCHOOL_NAME;

두 개의 쿼리 모두 인덱스 Range 스캔을 사용하지만 조건에 사용된 컬럼이 가공되었기 때문에 성능에 문제가 있다.


이를 해결하는 방법은 추후에 공부해보자.

댓글()
  1. 동구 2018.07.08 18:41 댓글주소  수정/삭제  댓글쓰기

    실행계획을 믿었다가 프로덕트 DB에서 속도가 안나오는거 보고 예전엔 많이 의아했는데....
    통계작업으로 인한 수치와 옵티마이저에 의해 언제든지 달라질 수 있다는 것 ㅜ

인덱스 기본 사용법과 인덱스 스캔이 불가능한 경우 소

우리가 색인을 통해 단어를 찾는 순간을 생각해보자.

ㄱ.

가나

가방 장식

가시 방석

ㄴ.

나방

나방 나무

누에고치

나무 장식

누나


여기서 누에고치라는 단어를 찾을 때, 위에서 순차적으로 진행한다고 가정하였을 때 큰 어려움 없이 발견할 수있다. 이 방식을 Index Range Scan이라고 한다.

반대로 장식이 포함된 단어를 찾아보자. 찾기 어려운 건 아니여도 모든 색인을 전부 확인해봐야한다. 이렇게 모든 색인을 다 확인하고 나서 찾을 수 있는 방식을 Index Full Scan 방식이라고 한다.

그렇기 때문에 인덱스의 기준이되는 데이터 즉 컬럼을 가공하게되면 Range Scan이 불가능해진다. 정리하면 인덱스 기준이 가공되면 인덱스 스캔의 시작점을 찾는 수직적 탐색이 불가능해지기 때문이다.


몇 가지 쿼리를 예로 들어보자.


1
2
3
4
create table student (
name varchar2(255),
birth date);
cs


학생 테이블이 있을 때 생일이 1월로 시작하는 사용자를 찾기위해 다음과 같은 쿼리를 사용한다고 가정해보자.


1
select * from student where substr(birth, 52= '01';
cs



어디서 부터 스캔을 멈춰야할지 인덱스 스캔을 할 수가 없다. 또 다른 예를 들어서 확인해보자.



1
2
3
select * from student where nvl(birth, CURRENT_DATE) < '2018-08-12';
 
select * from student where name like '%edu%';
cs



어디서 인덱스 스캔을 멈추어야하는지 알 수없기 때문에 마찬가지로 인덱스 스캔을 진행할 수가 없다.





OR이나 IN절의 경우에는 내부적으로 UNION ALL으로 내부적으로 나뉘어서 각자 자신의 쿼리를 사용하여 인덱스를 사용할 수 있다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
where 전화번호 in ( tel_no1, tel_no2 )
 
 
 
                |
// 아래와 같이 변경
                ▽
 
 
 
select * from 고객 where 전화번호 = tel_no1
 
union all 
 
select * from 고객 where 전화번호 = tel_no2

cs


댓글()

오라클 SGA 캐시 탐색 메커니즘 정리

Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다.


버퍼캐시의 구조는 해시 구조로 관리된다. 해시 함수를 통해서 나온 결과를 사용하여 해시체인(Bucket)을 찾고 그 해시체인에 연결되어 있는 버퍼 정보를 사용하여 버퍼 캐시 정보를 찾아 갈 수 있다.




만약 해시체인을 찾아주는 해시 알고리즘이 mod(x, 5)라고 할 때, 25번째 블록을 찾고 싶은 경우 해시함수를 사용해서 5로 나누고 나온 나머지 0이 해시체인 0번째를 의미한다. 그 해시체인에는 버퍼 헤더들이 들어있다. 25라는 버퍼헤더를 찾고 그 버퍼헤더가 가리키고 있는 버퍼 블록을 접근하여 데이터를 탐색하면 된다.


만약 27번째 블록을 읽으려고 해시함수를 거쳐서 2번째 해시체인에 갔으나, 버퍼 헤더 27이 없다면?? 다른 해시 체인을 뒤져야 하는가?


그럴 필요 없이 해시체인에 27번째 블록을 디스크에서 읽어서 연결해주면 된다.



정리하면 버퍼캐시에서 블록을 찾을 때 해시 알고리즘으로 버퍼 헤더를 찾고 거기서 포인터로 버퍼 블록을 액세스 하는 방식이다.


여기서 사용되는 해시 구조의 특징은 다음과 같다.



버퍼캐시의 해시구조 특징

  • 같은 입력 값은 항상 동일한 체인(버킷)에 연결 됨

  • 다른 입력값이 동일한 체인(버킷)에 연결될 수 있음

-> 6, 11의 경우 1번 해시체인에 연결될 수 있음

  • 해시 체인 내에는 정력이 보장되지 않는다.


버퍼캐시는 SGA의 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 공유자원이다. 말 그대로 공유자원이기 때문에 누군나 접근이 가능하고 동시접근이 이루어질수도 있다.


그럴경우에 순차적으로 접근하도록 도와주는 매커니즘이 있는데 이를 직렬화(serialization) 메커니즘이다. 이 직렬화 메커니즘을 사용하면 해당 블록을 사용하는 프로세스가 있으면 그 프로세스만 사용이 가능한 방식이다. 쓰레드에서 동기화와 같은 원리인 것 같다.


댓글()

오라클의 Table Full Scan과 Index Range Scan

테이블에 저장된 데이터를 읽는 방식은 두 가지이다.

Table Full Scan은 해당 테이블에 전체 블록을 읽어서 사용자가 원하는 데이터를 찾는 방식이다. 그리고 Index Range Scan은 인덱스를 이용하여 데이터를 일정부분읽어서 ROWID로 테이블 레코드를 찾아가는 방식이다. ROWID는 테이블 레코드가 디스크 상에 어디 저장됐는지를 가리키는 위치 정보이다.

상당수의 툴(Toad, Orange)에서 데이터를 Full Scan 하는 경우에 실행계획에서 빨간색으로 경고를 보여준다.

그래서 Table Full Scan에 경우 더 느리다는 고정관념이 있으나 모두 그런것은 아니다. Index를 이용한 스캔방식이 더 느린 경우도 있다.

Table Full Scan에 경우 읽고자 하는 데이터의 블록을 Multi Block I/O로 읽기 때문에 프로세스가 데이터를 바로 처리할 수 있으나, Index의 경우 Single Block I/O로 데이터를 읽는다. 그렇기 때문에 데이터를 모두 읽는 I/O Call이 끝날 때까지 정작 프로세스는 대기 상태에 들어가기 때문에 비효율적인 상태가 된다.

그리고 인접한 데이터 500개를 찾을 경우 Single Block I/O를 사용하는 Index의 경우에는 같은 블록을 500번 방문하는 안좋은 경우가 발생할 수 있다.

경우에 따라 Full Scan이 더 좋은 방법일 수 있고, Index Scan이 좋은 방법일 수 있다. 앞으로 더 공부하면서 어떤경우가 더 좋고 나쁜지 알아봐야겠다.

댓글()

Single Block I/O와 Multi Block I/O

캐시 모두 데이터를 적재해두면 속도도 빠르고 좋으나, 메모리에 한계가 있기 때문에 항상 적재하고 있을수는 없다. 그래서 캐시에서 찾지 못한 경우 I/O 콜을 사용하여 블록씩 데이터를 읽어들이는데 한번에 한 블록씩 가지고 오는 것을 Single Block I/O라고 한다. 그리고 여러 블록을 한번에 가지고 오는 것을 Multi Block I/O라고 한다.



기본적인 인덱스와 테이블 블록을 읽어들일때는 Single Block I/O 방식이 사용된다. 하지만 대량의 데이터를 테이블에서 가지고와야 할 때는 Multiblock I/O가 좋고 그 단위가 크면 대량의 블록에서 데이터를 한번에 가지고 올 수 있기에 프로세스가 잠자는 횟수를 줄일 수 있어 좋다.

그렇기 때문에 대용량 데이터를 Full Scan할때 Multiblock I/O 단위를 크게 설정하면 좋다.


오라클에서는 한번에 옮길 수있는 multiblock에 대해서 db_file_multiblock_read_count 파라미터로 정한다.


만약 OS에서 정한 레벨 I/O 단위가 1MB라고 하였을 때, db_file_multiblock_read_count 값이 128일 경우에 오라클의 레벨 I/O가 8KB이기 때문에 최대로 옮길 수 있다. (8Kb * 128 = 1MB)


그래서 대부분 O/S에 따라서 결정되는데 4 ~ 32 정도로 많이 사용한다. 하지만 이 설정은 Full Scan 시 인접 데이터까지 가지고 올 수 있기에 속도가 빠르나 데이터를 읽어들이는 속도가 증가하고 한정된 Buffer Pool을 많이 차지하게 되어 자주 쓰는 데이터를 버퍼에서 밀어버리기 때문에 효율을 감소시킬수 있기때문에 업무에 맞게 조정하여 사용하여야 한다.

댓글()

시퀀셜 액세스와 랜덤 액세스

데이터베이스에서 데이터를 블록단위로 읽는다. 1 ~ 3 byte와 같이 작은 데이터를 읽을때도 하나의 블록을 읽어들인다. 그리고 테이블뿐만 아니라 인덱스도 블록단위로 읽어들인다.


데이터베이스의 총 블록 사이즈를 알고 싶으면 다음 쿼리를 통해 확인해 볼 수있다.

1
select value from v$parameter where name = 'db_block_size';
cs


그럼 테이블과 인덱스를 블록단위로 읽는 방식에 대해서 알아보자.


테이블 또는 인덱스를 읽는 방식


시퀀셜 액세스 (Sequential Access)

논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽어들이는 방식이다. 인덱스 리프블록은 앞뒤를 가리키는 주소값으로 서로 연결되어 있는데 이를 이용하여 순차적으로 스캔하는 방식이다.

테이블 블록은 서로 논리적으로 서로 연결고리를 가지고 있지는 않지만 세그먼트 헤더가 각 익스텐트들에 대한 주소를 맵의 형태로 가지고 있어 그를 이용하여 Table Full Scan이 가능하도록 하는 구조이다.


랜덤 액세스 (Random Access)

논리적, 물리적 순서가 아닌 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식이다.



그림을 보면 알겠지만, 시퀀셜 액세스의 경우 각 엘리먼트들의 그룹을 순차적으로 접근하는 반면에 랜덤 액세스는 조각으로 쪼개져있으며 저장이 가능한 공간에 저장되어 있어서 필요한 블록을 읽어 들일때마다 한 블록씩 접근해야 한다.

댓글()