TOP-N과 인덱스 스캔이왜 그렇게 동작하는지까지

사내에서 페이징처리를 위해서 고전 방식의 쿼리를 이용한 TOP-N 방식을 사용한다.

예시

SELECT *
FROM
(
	SELECT ROWNUM as row_num, T.*
	FROM
	(
		SELECT *
		FROM MY_TABLE
		WHERE :조건
		ORDER BY :인덱스_컬럼
	) T
	WHERE ROWNUM <= :pageNum * :pageCount + 1
)
WHERE row_num > (:pageNum - 1) * :pageCount

pageNum: 페이지 번호

pageCount: 한 페이지에 표기할 아이템 수

위와 같은 규칙에 맞게 작성되어있는 페이징을 처리하는 서비스의 테스트를 진행했다.

처음은 단순하게 생각했다.

PK정렬 -> 인덱스 존재 -> 인덱스로 정렬 없이 상위 N개를 먼저 읽어온다.

하지만 실제 개발서버에서 진행한 오라클의 실행계획(plan)은 예상과 달랐다.

• PK 인덱스가 있음에도 TABLE ACCESS FULL
• ORDER BY 정렬 후 WINDOW SORT
• 힌트를 사용할 경우, 비용(cost)증가
• CARDINALITY(card) 값이 실제 데이터 개수와 다름
• 조건에 따라 인덱스를 타거나 풀스캔을 진행하거나

위 경우를 하나씩 파고들어가며 Oracle 옵티마이저의 핵심 철학과 TOP-N 최적화의 내부 동작 원리를 이해할 수 있었다.

1. 페이징 쿼리와 인라인뷰의 오해

인라인뷰는 모든 데이터를 정렬하고 ROWNUM으로 자를까?

SELECT *
FROM (
	SELECT *
	FROM MY_TABLE
	ORDER BY MY_TABLE_COL_PK
    )
WHERE ROWNUM <= 1000

인라인뷰에 ORDER BY 절이 있으니 Oracle은 모든 행을 정렬한 뒤, 1000개만 자를까?

1. MY_TABLE 전체를 읽기(FULL SCAN)

2. 메모리 / 디스크 정렬 (WINDOW SORT)

3. 상위 1000개만 자르기

이것은 부분적으로만 맞다.

정렬 대상 컬럼에 적절한 인덱스가 있다면 Oracle은 이렇게 동작하지 않는다.

2. TOP-N이란?

TOP-N은 정렬된 결과의 상위 N개만을 가져오는 방식이다.

즉, 정렬 전체를 다 만들지 않고 상위 N개를 판별할 수 있는 순간 STOPKEY로 즉시 중단하는 최적화이다.

이 최적화가 발생할 경우 플랜

COUNT STOPKEY
WINDOW NOSORT STOPKEY

STOPKEY가 등장한다면 Oracle은 중간에 읽기를 끊었다는 신호

3. TOP-N이 효율적으로 작동하는 조건

TOP-N 최적화는 모든 상황에서 효율적인 것은 아니다.

핵심: ORDER BY 컬럼이 인덱스 정렬 순서와 동일한 형태로 존재해야 한다.

예시

즉, 정렬 가능한 인덱스가 있을 때만 TOP-N 최적화가 제대로 동작할 수 있다.

4. 적절한 인덱스 혹은 PK가 있는데도 WINDOW SORT / FULL SCAN이 나온 이유

실제 발생한 플랜

SELECT STATEMENT optimizer=all_rows (cost=113 card=1000 bytes=220000)
 VIEW (cost=113 card=1000 bytes=220000)
  WINDOW SORT PUSHED RANK (cost=113 card=221 bytes=18564)
   TABLE ACCESS FULL OF 'MY_TABLE' (TABLE) (cost=112 card=221 bytes=18564)

"SELECT *" 를 사용

인덱스를 정렬 기준으로 사용할 수 있어도 쿼리에 SELECT * 문법을 사용하면 모든 컬럼을 테이블에서 찾아야한다.

즉, 인덱스를 따라 N개의 ROWID를 찾음 -> ROWID로 테이블을 다시 랜덤 엑세스(N번의 랜덤 I/O)

Oracle은 “랜덤 I/O 1000번” vs “풀스캔 1번”을 비교해
풀스캔이 더 쌀 것 같으면 TABLE ACCESS FULL 을 선택한다.

그 결과:

→ 인덱스는 정렬 순서를 제공하나
→ 결과 행의 모든 컬럼을 얻기 위해 결국 테이블 재접근이 필요
→ 랜덤 액세스 비용이 비싸면 옵티마이저가 풀스캔 선택

5. 힌트로 강제로 인덱스 스캔 → 더 안 좋은 COST?

인덱스 정렬을 위한 힌트 사용

--인덱스 컬럼정렬 힌트 사용
SELECT /*+ index_asc(t MY_TABLE_COL_PK) */*
FROM (
	SELECT *
	FROM MY_TABLE t
	ORDER BY MY_TABLE_COL_PK
    )
WHERE ROWNUM <= 1000

플랜결과

SELECT STATEMENT optimizer=all_rows (cost=409 card=1000 bytes=220000)
 VIEW (cost=409 card=1000 bytes=220000)
  WINDOW NOSORT STOPKEY (cost=409 card=221 bytes=18564)
   TABLE ACCESS BY INDEX ROWID OF 'MY_TABLE' (TABLE) (cost=409 card=221 bytes=18564)
    INDEX FULL SCAN OF 'MY_TABLE_COL_PK' (INDEX (UNIQUE)) (cost=8 card=221)

하지만 COST가 더 올라감.

왜일까?

이유는 매우 단순하다.

인덱스 FULL SCAN 방식 = 전체 풀스캔보다 더 많은 페이지 랜덤 액세스

인덱스는 보통:

• 블록이 더 작음
• 페이지 수가 더 많음
• 물리적 순서가 데이터 순서와 다름

즉, 인덱스 FULL SCAN은 테이블 FULL SCAN보다 오히려 더 많은 I/O가 발생할 수 있다.

그 다음엔 ROWID로 테이블 랜덤 액세스

그러니 힌트로 인덱스를 타게 만들면

“불필요하게 인덱스를 훑고 + 테이블을 다시 랜덤 액세스하는 비효율”

이 될 수도 있다.

6. 플랜에서 인덱스 스캔을 타는 경우

SELECT *
FROM (
	SELECT *
	FROM MY_TABLE
	ORDER BY MY_TABLE_COL_PK
    )
WHERE ROWNUM <= 50

--혹은
SELECT *
FROM MY_TABLE
ORDER BY MY_TABLE_COL_PK
FETCH FIRST 50 ROWS ONLY;

where 절의 ROWNUM을 50으로 제한해보자

이런 경우 옵티마이저가 “50개만 읽으면 되니까?” 하고 TOP-N 최적화를 자동으로 적용할 때가 있다.

왜 자동으로 적용되었을까?

• 테이블이 충분히 크고
• PK 인덱스가 정렬순서와 같고
• 상위 소량의 행(50개)만 필요할 때

이런 경우 Oracle은 다음 과정을 선택한다:

1. 인덱스를 DESC로 스캔해 상위 50개 ROWID 추출
2. 50개의 ROWID만 테이블에서 랜덤 액세스
3. STOPKEY로 종료

이 작업은 풀스캔보다 훨씬 빠를 수 있다.

즉, 데이터량과 TOP-N의 N 값의 상대적 크기가 최적화 방향을 결정한다.

7. 왜 인라인 뷰의 card수가 실제 테이블의 row 수와 다를까

실제 테이블 rowcount = 1000+
그런데 실행계획에서: card = 221이렇게 나왔다.

이유

cardinality는 실제 접근 행수가 아니라
옵티마이저가 통계 기반으로 “예상”한 행 수이다.

따라서:

• 통계가 오래됨
• 히스토그램 없음
• 인덱스 컬럼 분포가 균등하다고 오해
• WHERE 조건의 선택도가 다르게 해석됨

이런 이유로 실제와 다르게 보인다.

8. 그럼 지금 사용 중인 인라인 페이징이 나쁜가?

결론

❌ 인라인 뷰 + ROWNUM 페이징 자체가 나쁜 것이 아니다.

⭕ ORDER BY 컬럼 인덱스 유무가 성능을 좌우한다.

ORDER BY 인덱스가 없다면
→ WINDOW SORT + FULL SCAN은 정상적이며 불가피하다.

ORDER BY 인덱스가 있다면
→ TOP-N 최적화가 적용되어 인덱스 스캔 + STOPKEY 가 가능해진다.

9. TOP-N 최적화가 발휘되는 데이터량 기준

정확한 기준 값은 없지만 경험적으로 다음과 같다.

즉, 데이터가 많아질수록 TOP-N 최적화는 더 큰 힘을 발휘한다.

🏁 마무리

1. 인라인뷰의 정렬 가능한 인덱스 유무가 중요하다.
2. STOPKEY는 상위 N개에서 “조기 종료”를 가능하게 한다.
3. 인덱스 스캔이 항상 더 빠른 것이 아니며, SELECT * 이라면 테이블 랜덤 액세스 비용 때문에 FULL SCAN이 더 빠를 수도 있다.
4. Cardinality(card)는 실제 행 수가 아닌 통계 기반 추정치이다.
5. PK 인덱스는 정렬에 유리하나, 모든 상황에서 인덱스를 타는 것은 아니다.
6. TOP-N은 데이터가 많을수록 효과가 크다.

Oracle 실행계획은 단순히 “풀스캔이면 느린 쿼리다”가 아니라 왜 풀스캔이 선택되었는지를 이해하는 것이 중요했다.

TOP-N, STOPKEY, 인덱스 스캔의 상관관계를 확실히 이해하기 좋은시간이었습니다.

실무에서 자주 사용되는 ORACLE SQL 숫자형 제어 함수 알아보기

✅ 1. FLOOR(number)

• 소수점 이하 버림 (내림)
• FLOOR(123.987) → 123

✅ 2. CEIL(number)

• 소수점 이하 올림 (천장 함수)
• CEIL(123.001) → 124

✅ 3. ROUND(number [, digits])

• 반올림
• ROUND(123.456) → 123
• ROUND(123.456, 1) → 123.5
• ROUND(123.456, -1) → 120 ← 자릿수 내림

✅ 4. TRUNC(값, 자릿수)

• 소수점 이하 절삭 (버림) — FLOOR와 비슷하지만 다름
• TRUNC(123.987) → 123
• TRUNC(123.987, 1) → 123.9
• TRUNC(123.987, -1) → 120

🔹 TRUNC는 자릿수를 지정해 절삭 가능하고,
🔹 FLOOR는 무조건 정수로 내림

✅ 5. MOD(n1, n2)

• 나머지 연산
• MOD(10, 3) → 1

✅ 6. SIGN(number)

• 부호를 반환
• SIGN(-100) → -1
• SIGN(0) → 0
• SIGN(123) → 1

✅ 7. ABS(number)

• 절댓값 반환
• ABS(-42) → 42

✅ 8. POWER(m, n) / EXP(n) / LN(n) / LOG(n)

• 지수/로그 관련
• POWER(2, 3) → 8
• EXP(1) → e ≈ 2.71828
• LN(e) → 1
• LOG(100, 10) → 2

✅ 9. NVL(number, 0) / COALESCE()

• null 방지 처리
• NVL(x, 0) → x가 NULL이면 0 반환
• COALESCE(x, y, z) → 처음 NULL이 아닌 값 반환

Top 5 정리

사내 업무 중 운영에서 timeout이 나고 있는 서비스를 발견했다.(내부 서비스이고 자주 사용하는 것이 아니라서 발견이 늦었다.)

(서비스의 실행 시간이 60초 이상이라면, timeout error를 뱉게 되어있다.)

로그를 확인해보느 조회 쿼리에서 60초 이상 걸리는 것을 확인했다.

해당 업무의 특성상, 고객 이력 테이블을 조회하는데 개발 환경에서 plan을 확인하니 테이블을 full scan하고 있었다.

운영 환경 경우, 더 많은 데이터가 있을 것이므로 개선이 필요했다.

쿼리 성능 저하의 주된 원인은 "LAPD의 사용"

타겟 테이블은 고객 이력을 관리하는 테이블이었기에 대용량 데이터를 가지고 있는데 LPAD를 사용하여 좌변을 모두 가공하므로써, 인덱스 사용이 불가능했다.

특정 기간을 조건으로 걸고있었는데 해당 부분이 아래와 같이 사용하고 있었다.

WHERE
	LPAD(날짜컬럼, 6) = '202408'

LPAD는 지정한 길이 만큼 특정문자를 채우는데 위처럼 문자를 지정하지 않으면 공백으로 채운다.

🛠️ 개선

WHERE
	날짜컬럼 LIKE '202408' || '%'

LIKE문을 사용해서 인덱스를 탈 수 있게 수정했다.

✔️ 결과

최종적으로 buffer gets 이 3,000K -> 300K 로 약 10배 정도 메모리 성능이 좋아졌다.

속도는 조건마다 상이할 수 있겠지만 3초 이내로 결과를 낼 수 있었다.

위 사진의 출처: https://yoo-hyeok.tistory.com/98

SQL join 문이 가장 잘 정리 되어 있는 그림인 것 같습니다.

join: 동일한 column을 기준으로 테이블을 하나로 합칩니다.

left(right) join 과 left(right) outer join의 차이점

실행 결과는 동일 합니다.

left join의 경우 동일한 column으로 하나로 합쳐지고 left 쪽(A)의 모든 데이터를 포함하고 B의 데이터는 null로 표시 됩니다.

left join 결과

SELECT * from A left join B on A.id = B.id;

id를 기준으로 A 테이블의 손상 없이 두 테이블을 합칩니다.

B 테이블에 존재하지 않는 id의 데이터는 null로 표시됩니다.

이 null 값을 where 조건을 사용하면 A 혹은 B만 가지고 있는 데이터를 가져올 수 있습니다.

A와 B 테이블 공통 데이터 가져오기 (inner join과 동일)

SELECT * from A left outer join B on A.id = B.id where B.id is not null;

위 코드의 결과는 inner join을 사용한 것과 동일 합니다.

SELECT * from A inner join B on A.id = B.id;

A 테이블에만 있는 데이터만 가져오기

SELECT * from A left outer join B on A.id = B.id where B.id is null;

패키지 설치 도구인 homebrew를 이용해서 postgresql을 설치하고 DBMS 도구인 DBeaver에 연결하는 것까지 정리해 보겠습니다.

Homebrew나 DBeaver를 설치하는 부분은 생략합니다.

☝️homebrew로 postgresql 설치하기

1. 터미널에 접속합니다.

2. 버전 확인하기

brew search postgresql

위 명령어를 입력하면 homebrew에서 설치 가능한 postgresql의 버젼을 확인할 수 있습니다.

@ 뒤에 있는 숫자가 버전을 의미합니다.

3. 설치하기

brew install postgresql

위 명령어를 입력하면 최신 버전의 posrgresql을 설치할 수 있습니다.

만약, 특정 버전을 설치하고 싶다면, @까지 포함한 전체 이름을 적어주면 됩니다.

ex) brew install postgresql@9.6

* homebrew의 문제로 이전 버전이 설치되지 않은 버그가 종종 있습니다.

4. 설치 확인하기

brew list

위 명령어를 사용하면 brew를 이용해 설치한 프로그램들을 확인할 수 있습니다.

✌️ postgresql 실행 및 database 생성

1. brew 명령어로 postgresql 실행

brew services start postgresql

위 명령어를 입력하면 postgresql 서비스가 실행됩니다.

2. postgresql 쉘 들어가기

psql postgres

위 명령어를 입력하면 postgresql 을 조작할 수 있는 쉘에 입장 합니다.

위 그림처럼 postgres=# 혹은 postgre=> 이 하단 왼쪽에 보인다면 성공입니다.

#은 관리로 접속을 의미합니다.

이제 몇가지 명령어를 입력으로 DB를 조작합니다.

\du 를 입력하면 database user(사용자)를 확인할 수 있습니다.

Role name 중 test는 제가 임의로 만들어 놓은 것이기 때문에 없을 수 있습니다.

2. db 사용자 추가하기

CREATE ROLE 원하는이름 WITH LOGIN PASSWORD '비밀번호';

위 명령어를 통해서 db 사용자를 추가할 수 있습니다.

비밀번호를 입력할 때는 따움표('')안에 넣어주시면 됩니다.

test / 1234로 db 사용자를 생성했습니다.

성공하면 CREATE ROLE 이라는 문구가 자동으로 표시 됩니다.

본인이 만든 ROLE이 잘 생성되었는지 확인하려면 \du 명령어를 입력하면 됩니다.

4. 테이블 생성 권한 추가하기

ALTER ROLE 이름 CREATEDB;

"이름"에는 본인이 생성한 ROLE name을 입력하시면 됩니다.

위 명령을 통해서 database를 만들 수 있는 권한을 부여합니다.

5. 생성한 사용자로 접속하기

이제 \q 를 입력하여 현재 관리자 쉘에서 나옵니다.

psql postgres -U 이름

위 명령어를 통해서 본인이 생성한 "이름"의 사용자로 접속합니다.

전 아까만든 test 라는 이름의 사용자로 접속하였고 접속 후엔 postgres=> 가 기본으로 입력 됩니다. 관리자의 postgres=# 과 다른 모습.

6. database 생성하기

\l 을 입력하면 현재 사용자의 database를 확인할 수 있습니다.

위 그림은 현재 test라는 사용자가 가지고 있는 database 리스트 입니다.

CREATE DATABASE 데이터베이스이름;

 위 명령어를 입력하면 현재 사용자에 database를 생성할 수 있습니다.

다시 \l 명령어를 입력하면 test_db가 새롭게 생성된걸 확인할 수 있습니다.

🤟DBeaver에 연결하기

DBeaver를 키신 후 1번과 2번을 순차적으로 클릭 후 next를 눌러 줍니다.

그림과 같이 본인이 생성한 사용자의 이름 / 패스워드를 입력하고 해당 사용자에서 생성한 데이터베이스 이름을 Database 란에 입력해 줍니다.

만약, 드라이버가 설치가 되지 않았을 경우 오류가 발생 할 수 있으므로 "Edit Driver Settings"를 클릭하신 후 아래 그림과 같이 Libraries 탭에 들어가 설치해주시면 됩니다.

따로 경고 문구나 오류가 나타나지 않는다면, 이미 정상 설치가 된 것이므로 넘어가시면 됩니다.

DBeaver 왼쪽 상단에 초록색 박스에 흰색 v 체크가 되어있다면 정상 연결이 된 것이므로 사용하시면 됩니다.

추가로 SQL작성을 위해서  편집기를 생성한 후에 해당 편집기를 쿼리문이 실행될 db선택해주셔야 합니다.

그림 참고

DBeaver / MySQL 무한 로딩 해결법

최근 팀원들과 진행하는 개인 프로젝트에서 DB조작을 하는경우가 많다보니 DBeaver가 무한로딩이 걸리는 경우를 종종 목격하게 됐습니다.

보통 쿼리문의 요류나 외부요인에 의해서 정상 종료되지 않은 상태에서 다른 곳에서 SQL명령을 실행했기에 DBeaver가 이를 인식하지 못하고 계속 실행을 요청하다가 무한 로딩(dead lock)이 걸리는 경우 였습니다. 

예시

Java 애플리케이션을 통해 query 실행 -> 오류 -> "DB 대기 중"

DBeaver를 통해 query 실행 -> "DB 대기 중"이 끝날때 까지 기다림... -> 무한 로딩

이런 모습 혹은 반대의 경우입니다.

가장 간단한 해결법은 사용하는 DB 저의 경우 MySQL 서비스를 재시작하는 것 (개인 프로젝트나 DB백업이 잘 되어있는 경우 추천)

재시작 방법

윈도우의 경우,

윈도우 검색창 : 서비스 입력 -> 서비스(앱) 실행 -> MySQL 서비스 다시 시작

후에 DBeaver에서 쿼리를 실행 하기 전 / 후로 "커밋"을 해주는 것이 좋습니다.(상황에 따라서 다를 수도 있습니다.)

보통은 DBeaver에서 쿼리를 실행 한 후 커밋을 해줘야 외부(Java)에서 쿼리를 실행할 때, 정상적으로 DB를 인식할 수 있습니다.