←

PL/SQL → CTEs

Stabilize the flow before you touch the code; sequence fixes so each one funds the next.

Résumé bullet

Refactored a 7.7K-line monolithic CASE-based PL/SQL query into per-process CTEs (~53% reduction, to ~3.6K), isolating 5 sub-processes so changes no longer cascade — raising weekly cost-ledger ticket throughput from 4 to 7.

Deep-dive
  • Inherited low-trust system: slow delivery, scattered requests
  • Routed all requests through one triage point — throughput ~2x
  • Refactored 7.7K-line procedure to 3.6K — days became hours
  • Built self-service page — manual requests dropped 80%+
  • Scheduled jobs: JOB_LOCK + thread-pool tuning for 2 servers
  • Each fix freed time for the next — improvements compounded
Code
-- Before: one 7.7K-line CASE-tangled procedure
-- After: per-process CTEs, isolated sub-processes
WITH proc_a AS ( SELECT ... ),  -- sub-process 1
     proc_b AS ( SELECT ... ),  -- sub-process 2
     proc_c AS ( SELECT ... )   -- sub-process 3
SELECT * FROM proc_a
JOIN proc_b USING (cost_key)
JOIN proc_c USING (cost_key);
-- changing proc_b no longer cascades into a/c
-- duplicate-run guard for 2-server scheduler:
-- JOB_LOCK table + tuned thread pool (parallel, not serial)
Say it (English)
  • ·I took over a costing system where trust was already broken.
  • ·First I stopped the chaos: all requests routed through me.
  • ·Then I refactored 7,700 lines down to 3,600 — days became hours.
  • ·Then a self-service page — manual requests dropped over 80%.
  • ·I was the filter, not the wall — and each fix funded the next.
Why it matters — Opendoor / as an engineer
  • Opendoor: decompose a monolith so changes don't cascade; throughput up.
  • Proves: legacy refactoring, isolating blast radius, measurable gains.
  • Use it for query-performance / refactoring questions.
Trade-offs (what you give up)
  • CTEs read clean but Oracle may materialize each one, killing predicate pushdown and slowing the query vs. the inline CASE.
  • JOB_LOCK is a DB-level mutex: a crashed holder leaves a stale lock and blocks all runs until manually cleared.
  • Regression tests pin current behavior, including its bugs — you can't tell a fix from a faithfully-preserved defect.
  • Self-service runs only on the dev DB, so results can silently drift from production data.
Push it further
  • Add lease/TTL + heartbeat to JOB_LOCK so a dead holder's lock auto-expires instead of wedging.
  • Use SELECT ... FOR UPDATE SKIP LOCKED or a queue table so the second server pulls work, not just waits.
  • Replace dev-DB sandbox with row-count/null golden assertions in CI to catch refactor drift automatically.
  • Materialize stable sub-processes as incremental views (or dbt models) with lineage instead of one runtime CTE chain.

Understand it step by step (한국어로 이해 → 영어로 말하기)

아무도 안 믿는 낡은 원가 시스템을 떠맡았다. 코드부터 고치지 않고, 먼저 일이 흘러가게 만든 뒤 하나씩 고쳤다. 그리고 고칠 때마다 시간이 생겨서 다음 걸 고칠 수 있었다.
  1. 1

    넘겨받은 원가 시스템은 이미 신뢰가 깨져 있었다. 처리도 느리고, 요청이 여기저기서 제멋대로 들어왔다. 뭐가 됐고 뭐가 안 됐는지 아무도 몰랐다.

    🗣 영어로 말해

    “I took over a costing system where trust was already broken.”

    checking microphone…

  2. 2

    먼저 코드를 건드리고 싶었지만 참았다. 들어오는 길이 엉망인데 코드만 고쳐봐야 또 묻힌다. 그래서 일단 들어오는 길부터 정리했다 — 모든 요청을 나 한 명을 거쳐가게 했다.

    ⚖️ Trade-off: 모든 요청이 나를 거치면, 내가 막히면 전체가 막힌다. 내가 병목이 될 위험.

    ✅ Fix: 그래서 자주 오는 요청은 템플릿으로 만들고, 쉬운 건 선임들이 처리하게 했다. 어려운 것만 나를 거쳤다. 나는 '벽'이 아니라 '거름망'이었다.

    🔧 도구:요청 단일 창구(triage) 운영자주 쓰는 요청 템플릿화

    🗣 영어로 말해

    “First I stopped the chaos: all requests routed through me.”

    checking microphone…

  3. 3

    길을 정리하니 시간이 생겼다. 그 시간으로 7천 줄짜리 거대한 프로시저를 3천6백 줄로 쪼갰다. 전엔 하나 고치면 며칠 걸렸는데, 이제 몇 시간이면 됐다.

    ⚖️ Trade-off: 그냥 막 쪼개면 원래 동작이 바뀔 수 있다. 게다가 보기 좋게 CTE로 나누면 DB가 각 조각을 따로 저장(materialize)해버려서 오히려 느려질 수도 있다.

    ✅ Fix: 먼저 지금 동작 그대로를 잡아두는 회귀 테스트를 깔고, 한 덩어리씩 조금씩 쪼갰다. 한 조각 바꾸고 검증, 또 한 조각. 한 번에 다 안 바꿨다.

    🔧 도구:회귀 테스트로 기존 동작 고정프로세스별 CTE로 분리단계별 배포·검증

    -- Before: one 7K-line CASE-tangled procedure
-- After: per-process CTEs, isolated sub-processes
WITH proc_a AS ( SELECT ... ),  -- sub-process 1
     proc_b AS ( SELECT ... ),  -- sub-process 2
     proc_c AS ( SELECT ... )   -- sub-process 3
SELECT * FROM proc_a
JOIN proc_b USING (cost_key)
JOIN proc_c USING (cost_key);
-- changing proc_b no longer cascades into a/c
    🗣 영어로 말해

    “Then I refactored 7,000 lines down to 3,600 — days became hours.”

    checking microphone…

  4. 4

    회귀 테스트에도 함정이 있다. '지금 동작'을 그대로 잡아두는 거라, 그 안에 숨은 버그까지 같이 '정답'으로 고정해버린다. 진짜 고친 건지, 버그를 그대로 보존한 건지 구분이 안 된다.

    ⚖️ Trade-off: 테스트가 통과해도 그게 '맞다'는 보장이 아니다. 버그도 충실히 따라 했을 뿐일 수 있다.

    ✅ Fix: 받아들였다 — 일단은 '안 바뀜'을 지키는 게 목표였으니까. 다음엔 행 개수·널 여부 같은 기준값 검사를 CI에 넣어서 변형을 자동으로 잡을 것.

    🔧 도구:골든 값 검사(행 수·null 등)를 CI에 추가

    -- golden assertions in CI to catch refactor drift
SELECT COUNT(*) AS rows,
       COUNT(*) - COUNT(cost_key) AS null_keys
FROM cost_ledger;
-- compare against pinned golden numbers; fail CI on drift
    🗣 영어로 말해

    “Regression tests pin today's behavior, even the bugs hidden inside it.”

    checking microphone…

  5. 5

    리팩토링으로 또 시간이 생겼다. 그 시간으로 셀프서비스 페이지를 만들었다. 사람들이 직접 원가를 돌려볼 수 있게 했더니, 나한테 오는 수동 요청이 80% 넘게 줄었다.

    ⚖️ Trade-off: 이 페이지는 실서버가 아니라 개발 DB에서 돌아간다. 그래서 결과가 실제 운영 데이터와 조용히 달라질 수 있다.

    ✅ Fix: 일부러 개발 DB로 막아둔 거다 — 사용자가 잘못 돌려도 운영엔 피해가 없고, 그냥 다시 돌리면 된다. '공짜로 실수해도 되는' 모래밭으로 설계했다.

    🔧 도구:개발 DB 전용 셀프서비스 페이지(샌드박스)

    🗣 영어로 말해

    “Then a self-service page — manual requests dropped over 80%.”

    checking microphone…

  6. 6

    마지막은 예약 작업(배치). 서버가 2대라, 아침 7시에 같은 작업이 양쪽에서 두 번 돌았다. 그래서 DB에 잠금 표(JOB_LOCK)를 둬서 한 대만 돌게 막았다.

    ⚖️ Trade-off: 이 DB 잠금은, 잠금을 쥔 서버가 중간에 죽으면 잠금이 그대로 남아버린다. 그러면 손으로 풀어주기 전까지 아무도 못 돈다.

    ✅ Fix: 다음엔 잠금에 만료시간(TTL)과 심장박동(heartbeat)을 붙여서, 쥔 서버가 죽으면 잠금이 저절로 풀리게 할 것.

    🔧 도구:JOB_LOCK 테이블로 중복 실행 방지TTL/리스 + heartbeat로 자동 만료(개선안)

    -- JOB_LOCK: only one of the 2 servers wins the morning run
INSERT INTO JOB_LOCK (job_name, locked_at)
VALUES ('cost_batch', SYSDATE);
-- PK on job_name -> second insert fails -> that server skips
-- improve: add expires_at (TTL) + heartbeat so a dead holder auto-releases
    🗣 영어로 말해

    “Two servers double-ran the morning job, so I added a JOB_LOCK so only one runs.”

    checking microphone…

  7. 7

    중복은 막았는데 이번엔 두 작업이 한 줄로 줄 서서 차례차례 돌았다(느림). 스프링 기본 스케줄러가 스레드 1개뿐이라 그렇다. 스레드 풀을 2개 이상으로 늘려서 둘이 동시에 돌게 했다.

    ⚖️ Trade-off: 스레드를 늘리고 잠금 단위를 잡는 설정을 잘못 잡으면, 오히려 동시성 문제가 다시 생길 수 있다.

    ✅ Fix: 잠금은 작업별로 따로 잡고, 풀 크기는 작업 수에 맞춰 잡았다. 그래야 막지도 않고, 두 번 돌지도 않는다.

    🔧 도구:spring.task.scheduling.pool.size >= 2작업별 잠금 분리

    # Spring default scheduler is single-threaded -> jobs run serially
spring.task.scheduling.pool.size=2
# @Scheduled jobs now run in parallel; lock per-job, not global
    🗣 영어로 말해

    “Spring's scheduler ran them one at a time, so I tuned the thread pool to run them in parallel.”

    checking microphone…

  8. 8

    핵심: 코드부터 덤비지 않았다. 먼저 일이 흘러가게 만들고(창구 정리), 거기서 번 시간으로 코드를 고치고, 또 거기서 번 시간으로 자동화하고… 고칠 때마다 다음 걸 고칠 시간이 생겼다. 나는 벽이 아니라 거름망이었다.

    🗣 영어로 말해

    “I was the filter, not the wall — and each fix funded the next.”

    checking microphone…

8단계 영어를 다 말하면 → 이 메커니즘 전체를 영어로 설명할 수 있게 된다.