테이블에서 랜덤 데이터 뽑아오기

[출처]http://cafe.naver.com/jameskang/96

테이블에서 임의의 데이터를 추출하고자 할때 버전에 따라서 지원하는 기능이 달라 구현 방법도 차이
가 있다. 첫번째는 7.x이상 모든 버전에서 사용 가능한 방법을 설명하고, 두번째로 8.1.x 버전 이상에
서 사용 가능한 방법을 설명하려 한다.

1. Oracle 7.x이상 모든 버전
Step 1. Random Number를 리턴해주는 함수를 생성한다. (함수 수스는 첨부를 참조하세요.)
( 8.0.x이상에서는 DBMS_RANDOM 패키지 이용)
Step 2. 위에서 생성한 함수를 이용하여 select List에 random number를 포함하여 중간 집합을 생성
한다.
사용예제)
select empno, ename, random.rand_max(100) rand, rownum rn
from emp;

결과
EMPNO ENAME RAND RN
---------- ---------- ---------- ----------
7369 SMITH 52 1
7499 ALLEN 55 2
7521 WARD 46 3
..............

14 rows selected.

Step 3. Step의 중간 집합을 이용하여 원하는 비율의 집합을 생성한다.
사용예제)
select *
from (
select empno, ename, random.rand_max(100) rand, rownum rn
from emp
)
where rand <=30; -- 대략적인 비율

결과
EMPNO ENAME RAND RN
---------- ---------- ---------- ----------
7521 WARD 9 3
7876 ADAMS 5 11
..............

(주의사항) 추출하고자 하는 집합보다 많은 레코드를 읽어야 하는 부담이 있다.

2. Oracle 8.1.x 이상(9i 포함)
오라클은 데이터를 access 할때 Full Table Scan, Random Access 방식은 모든 버전에서 제공한
방식이다. 8.1.x 이상 버전에는 이에 추가적으로 Sample Table Scan 방식을 제공한다. 이 Scan
방식을 이용하면 Random 하게 데이터를 손쉽게 추출할수 있다.

Sample Table Scan 방식을 간략하게 설명하고 이를 이용하여 어떻게 SQL에서 Random한 데이터를
추출할수 있는지를 예제를 통하여 설명하겠다.

2.1. Sample Table Scan
Full Table Scan 방식은 모든 행을 Scan하기 위하여 테이블에 관련된 모든 블록을 차례로 읽고,
각 레코드마다 where 조건을 만족하는지를 확인한다. Random Access 방식은 인덱스를 이용하여
where 조건의 일부를 만족하는 레코드를 선택 테이블에 관련된 블록을 랜덤하게 읽고, 나머지
where 조건을 만족하는 레코드를 출력한다. Sample Table Scan 방식은 Table의 데이터중
임의로 주어진 비율 만큼의 데이터를 읽고 그중 where 조건을 만족하는 레코드를 리턴한다.

2.2. Random Sample 추출 SQL
SELECT .....
FROM table_name SAMPLE {BLOCK option} (Sample Percent) <- 이 부분임
WHERE ....
GROUP BY ....
HAVING ....
ORDER BY

- BLOCK option 사용시 블록 단위로 Sample 데이터를 주어진 비율(Sample Percent) 만큼 읽은후
where 조건을 만족하는지 확인한다.
- BLOCK option 생략시 레코드 단위로 Sample 데이터를 주어진 비율(Sample Percent) 만큼 읽은후
where 조건을 만족하는지 확인한다.
- 비율(Sample Percent)는 0.000001 와 99.999999값을 지원하며, 0 또는 100을 지원하지 않는다.
(주의사항) 레코드 건수가 작은 테이블에서 sample이나 sample block 사용시에는 일정
비율(Sample Percent)의 sample data가 return되지 않을 수있으며, 비율의 의미는
대약적인 값이다. 즉 동일 SQL을 반복해서 수행해도 항상 같은 수의 레코드를
리턴하는 것은 아니다.

사용예제)
select empno, ename
from emp sample (10);

결과
EMPNO ENAME
---------- ----------
7369 SMITH
7876 ADAMS
......

select empno, ename
from emp sample block (10);
EMPNO ENAME
---------- ----------
7900 JAMES
......

2.3. 제약사항
- 한 테이블에서 select할 경우만 사용가능하며 Join후 또는 remote table에는 사용할수 없다.
(Inline View에서 사용후 다른 테이블과 조인은 가능하다.)
- Sample 절을 사용하면 Oracle은 cost-based optimizer를 사용하며, Rule-based optimizer는
지원되지 않는다. Rule 힌트를 사용하더라도 cost-based optimizer로 풀리게 된다.
- View로 생성할수 없다. (단, Inline View에서는 사용가능하다.)
- 오라클 8.1.5.x에서는 SAMPLE BLOCK을 지원하지 않습니다.
- 레코드 건수가 작은 테이블에서는 sample이나 sample block 사용시에는 일정 비율
(Sample Percent)의 sample data가 return되지 않을 수도 있습니다.