공간정보 뷰에서 데이터가 없으면 지점이 사라지는 문제와 SQL 튜닝 과정 — KEEP (DENSE_RANK LAST …) 활용하기

이번 글은 근무를 하면서 마주친 개발 이슈에 대한 내용을 기록해보았습니다.

 

 제가 개발하고 유지보수 하는 서비스에는 공간정보(좌표)를 지도에 표시하는 기능이 있습니다. 지점 정보가 담긴 테이블과 1분 단위로 지점에서 측정되는 값을 담은 테이블을 조인한 View를 통해 공간정보를 표시하고 있습니다. 근데 값을 측정하는 하드웨어 장비가 100개가 넘고 다양한 변수로 값을 보내주지 못하는 상황이 발생하면서 문제를 마주하게 됐습니다.

 

바로 “최근 측정 데이터가 없는 지점은 지도에서 아예 사라진다”는 이슈였습니다. 원인은 View 내부 script에 “최근 시간 조건”에 의해 최근에 측정값이 없는 지점들은 결과 row 자체가 없었던 것입니다. 이 글에서 해당 문제 해결과 성능까지 개선한 과정을 기록했습니다.

 

1) 문제 상황: 데이터가 없으면 지점이 지도에서 사라진다

공간정보를 표시하는 View의 구조입니다.

• 지점(좌표) 테이블: LOCATION_TABLE
• 측정(시계열) 테이블: MEASURE_TABLE
• 개선사항: 지점은 항상 보여야 하고, 측정값은 있으면 표시 / 없으면 미수신 상태로 처리

그런데 현재는  View가 “최근 1시간 데이터”를 기준으로 측정 테이블을 먼저 좁힌 뒤, 그 결과를 기준으로 JOIN을 수행(그 중에 최근 시간 - MAX값) 하면 최근 데이터가 없는 지점은 View 결과에서 row가 사라지고, 결국 지도에서도 지점이 표시되지 않습니다.

즉, “측정값이 없으면 지점 자체가 사라지는 현상”이 발생합니. 전체 쿼리문이 제공되면 이해가 더 편하겠지만 회사 서비스의 내부 로직이라 공개할수 없네요 양해를 구합니다 ...ㅎㅎ

 

2) 첫 번째 시도: 시간 조건을 없애면 해결될까?

가장 단순한 접근은 “최근 시간 조건 때문에 지점이 사라지니, 시간 조건을 없애자”였습니다.  제거하니 지점은 다시 모두 표시됐습니다.

하지만 곧바로 새로운 문제가 발생했습니다. 쿼리가 급격하게 느려진것....

시간 조건을 없애면 측정 테이블 전체 범위에서 지점별 최신 시간을 매번 계산해야 하고, 그 과정에서 대량 데이터에 대한 Full Scan + Group By가 발생하게 됩니다. 기능은 맞았지만, 운영 환경에서는 받아들이기 어려운 성능이 됩니다. 처음 지도가 나오면 한 10초 정도는 공간 표시가 안되다가 표시되고 그랬어요ㅜ

 

3) 문제의 핵심 정리

문제 해결을 위한 핵심은 다음과 같습니다.

1. 지점은 항상 보여야 한다.
2. 측정 데이터는 있으면 붙이고, 없으면 NULL이어도 된다.
3. 하지만 측정 테이블 전체를 매번 뒤지면 안 된다.

지점 테이블을 기준으로 조회하면서
측정 데이터는 “지점별 최신 값 1건만” 빠르게 붙이자.

 

4) 해결 전략: JOIN 구조를 바꾸자

• 지점 테이블이 기준(LEFT)이고
• 측정 테이블은 최신 데이터만 추려서(작게 만들어서) 붙인다(LEFT JOIN)

이렇게 하면

• 최근 데이터가 없어도 지점 row는 유지되고
• 측정값만 NULL로 붙고
• JOIN 대상 데이터 양이 크게 줄어 성능이 안정됩니다.

 

5) 여기서 등장하는 KEEP (DENSE_RANK LAST …)

문제는 “지점별 최신 데이터 1건”을 어떻게 뽑느냐였습니다. Oracle에서는 이 용도로 KEEP (DENSE_RANK LAST ORDER BY ...) 패턴을 많이 사용한다는 걸 찾았습니다.

기본 형태

MAX(값) KEEP (
  DENSE_RANK LAST ORDER BY 기준컬럼
)

의미를 풀어 쓰면 다음과 같습니다.

기준컬럼으로 정렬했을 때 가장 마지막(LAST) 시점에 해당하는 행(들) 중에서
그때의 값을 가져온다.

시계열 데이터에서 “최신 시각에 해당하는 값”을 뽑는 데 매우 유용합니다.

 

6) 작은 예제로 이해하기

측정 테이블이 아래처럼 생겼다고 가정합니다.

DEVICE_ID	MEASURE_TIME	MEASURE_VALUE
A	10:00	12
A	11:00	15
B	09:30	8

지점별 최신 값은 아래 쿼리로 뽑을 수 있습니다.

SELECT
  DEVICE_ID,
  MAX(MEASURE_TIME) AS MEASURE_TIME,
  MAX(MEASURE_VALUE) KEEP (
    DENSE_RANK LAST ORDER BY MEASURE_TIME
  ) AS MEASURE_VALUE
FROM MEASURE_TABLE
GROUP BY DEVICE_ID;

결과는 다음과 같습니다.

DEVICE_ID	MEASURE_TIME	MEASURE_VALUE
A	11:00	15
B	09:30	8

이렇게 하면 “각 지점(DEVICE_ID)별 최신 시각의 값”을 한 번에 얻을 수 있습니다.

 

7) 최종 JOIN 구조(원리 중심)

이제 지점 테이블을 기준으로, 최신 측정값만 작은 결과셋으로 만든 뒤 LEFT JOIN 한다. 또한 측정 테이블을 통째로 보지 않도록, 집계 대상 자체를 최근 구간으로 제한합니다.

FROM LOCATION_TABLE L
LEFT JOIN (
  SELECT
    DEVICE_ID,
    MAX(MEASURE_TIME) AS MEASURE_TIME,
    MAX(MEASURE_VALUE) KEEP (
      DENSE_RANK LAST ORDER BY MEASURE_TIME
    ) AS MEASURE_VALUE
  FROM MEASURE_TABLE
  WHERE MEASURE_TIME >= SYSDATE - INTERVAL '1' HOUR
  GROUP BY DEVICE_ID
) M
ON L.DEVICE_ID = M.DEVICE_ID

이 구조의 포인트는 “WHERE를 없앴다/ON으로 옮겼다”가 아니라, JOIN 전에 데이터를 줄였고(집계 대상 제한), 지점 테이블을 기준으로 LEFT JOIN 했다는 점 입니다.

 

8) 왜 이 방식이 빠른가?

1. 측정 테이블 전체를 JOIN하지 않는다 (JOIN 전에 지점별 최신 1건으로 축소)
2. 집계 대상 자체를 최근 범위로 제한하여 Full Scan 비용을 줄인다
3. 문자열 결합/파싱(SUBSTR 등) 같은 불필요한 연산을 제거한다
4. 인덱스 활용 가능성이 높아진다 (예: (DEVICE_ID, MEASURE_TIME))

정리하면, 성능의 핵심은 다음 한 문장입니다.

“JOIN 전에 데이터를 얼마나 줄였는가”가 성능을 좌우한다.

 

9) 정리

공간정보 서비스에서 “측정 데이터가 없다고 지점이 사라지면 안 되는” 요구사항이 있다면, 조인 방향과 집계 방식을 먼저 의심해보는 것이 좋습니다.

• 시간 조건을 무작정 제거하면 성능 문제가 생길 수 있다
• 지점이 항상 보여야 한다면 지점 테이블을 기준으로 LEFT JOIN이 필요하다
• 측정값은 지점별 최신 1건만 추려서 붙이면 기능과 성능을 동시에 만족할 수 있다
• Oracle에서는 KEEP (DENSE_RANK LAST ...)가 최신값 조회에서 매우 유용하다

감사합니다.

 

 

ENG ver.

 

https://jaemoi8.tistory.com/55

Solving Missing Location Issues in Spatial Views and SQL Performance Tuning with KEEP (DENSE_RANK LAST)