랜덤 Top-N 쿼리

랜덤 Top-N 쿼리를 작성해보자. 랜덤 Top-N 쿼리는 무작위로 정렬된 행의 상위 n건을 조회하는 쿼리다.

예제를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT LEVEL AS c1, LPAD ('X', 4000, 'X') AS c2 FROM DUAL CONNECT BY LEVEL <= 100000;

랜덤 Top-N 쿼리는 주로 DBMS_RANDOM.VALUE 함수를 사용하지만 Context Switching으로 인해 성능저하가 발생할 수 있다.

-- 2
SELECT c1
  FROM (SELECT   *
            FROM t1
        ORDER BY DBMS_RANDOM.VALUE)
 WHERE ROWNUM <= 5;

   C1
-----
13868
78870
95529
71122
60959

5 행이 선택되었습니다.

경   과: 00:00:02.25

10.1 버전부터 expr에 대한 해시 값을 반환하는 ORA_HASH 함수를 사용할 수 있다. max_bucket와 seed_value는 0 ~ 4294967295의 범위를 지정할 수 있다.

ORA_HASH (expr [, max_bucket [, seed_value ]])

아래 쿼리는 DBMS_RANDOM.VALUE 함수 대신 ORA_HASH 함수를 사용했다. DBMS_RANDOM.VALUE 함수에 비해 소요시간이 감소한 것을 확인할 수 있다.

-- 3
SELECT c1
  FROM (SELECT   *
            FROM t1
        ORDER BY ORA_HASH (c1, TO_CHAR (SYSTIMESTAMP, 'FF9'), TO_CHAR (SYSTIMESTAMP, 'FF9')))
 WHERE ROWNUM <= 5;

   C1
-----
87694
57914
93642
38203
43376

5 행이 선택되었습니다.

경   과: 00:00:00.79

테이블 사이즈가 크다면 SAMPLE 절 사용을 고려할 수 있다. 블록 샘플링 방식과 적절한 sample_percent를 지정해야 한다. 아래 쿼리에서 소요시간이 급격히 감소한 것을 확인할 수 있다.

-- 4
SELECT c1
  FROM (SELECT   *
            FROM t1 SAMPLE BLOCK (1)
        ORDER BY ORA_HASH (c1, TO_CHAR (SYSTIMESTAMP, 'FF9'), TO_CHAR (SYSTIMESTAMP, 'FF9')))
 WHERE ROWNUM <= 5;

    C1
-----
22897
55394
22883
55367
89560

5 행이 선택되었습니다.

경   과: 00:00:00.04