랜덤 Top-N 쿼리

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

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

1 2 3

-- 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으로 인해 성능저하가 발생할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- 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의 범위를 지정할 수 있다.

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- 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를 지정해야 한다. 아래 쿼리에서 소요시간이 급격히 감소한 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- 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