수열 생성

XMLTABLE 함수로 수열을 생성해보자.

DUAL CONNECT BY 방식은 행이 많은 경우 에러가 발생할 수 있다.

-- 1
SELECT COUNT (*) AS c1 FROM DUAL CONNECT BY LEVEL <= 1E7;

ORA-30009: CONNECT BY 작업에 대한 메모리가 부족합니다.

XMLTABLE 함수를 사용하면 행이 많은 경우에도 에러가 발생하지 않는다. 실행 계획에서 Pipelined Function으로 동작하는 것을 확인할 수 있다.

-- 2
SELECT COUNT (*) AS c1 FROM XMLTABLE ('1 to 10000000');

      C1
--------
10000000

1개의 행이 선택되었습니다.

--------------------------------------------------------------------
| Id  | Operation                          | Name                  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |
|   1 |  SORT AGGREGATE                    |                       |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |
--------------------------------------------------------------------

ROWNUM 슈도 칼럼을 사용하면 연속된 수열을 생성할 수 있다. 

-- 2
SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 5');

C1
--
 1
 2
 3
 4
 5

5 행이 선택되었습니다.

아래와 같이 열 명과 데이터 타입을 지정할 수도 있다.

-- 3
SELECT * FROM XMLTABLE ('6 to 10' COLUMNS c1 NUMBER PATH '.');

C1
--
 6
 7
 8
 9
10

5 행이 선택되었습니다.

쉼표(,)로 숫자를 지정할 수도 있다.

-- 4
SELECT * FROM XMLTABLE ('1,1,2,3,5' COLUMNS c1 NUMBER PATH '.');

C1
--
 1
 1
 2
 3
 5

5 행이 선택되었습니다.

숫자뿐만 아니라 문자열도 지정할 수 있다.

-- 5
SELECT * FROM XMLTABLE ('"A","C","E"' COLUMNS c1 VARCHAR2 (1) PATH '.');

C1
--
A
C
E

3 행이 선택되었습니다.

FLWOR 표현식을 사용하면 복잡한 수열을 생성할 수 있다. 아래 쿼리는 홀수 수열을 생성한다.

-- 6
SELECT *
  FROM XMLTABLE ('for $i in 1 to 10 where $i mod 2 = 1 return $i'
                 COLUMNS c1 NUMBER PATH '.');

C1
--
 1
 3
 5
 7
 9

5 행이 선택되었습니다.

for 문을 중첩할 수도 있다. 실행 계획에서 카티션 곱이 발생한 것을 확인할 수 있다.

-- 7
SELECT *
  FROM XMLTABLE ('for $i in 1 to 2, $j in 1 to 2 let $v := $i * $j return $v'
                 COLUMNS c1 NUMBER PATH '.');

C1
--
 1
 2
 2
 4

4 행이 선택되었습니다.

---------------------------------------------------------------------
| Id  | Operation                           | Name                  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                       |
|   1 |  MERGE JOIN CARTESIAN               |                       |
|   2 |   COLLECTION ITERATOR PICKLER FETCH | XQSEQUENCEFROMXMLTYPE |
|   3 |   BUFFER SORT                       |                       |
|   4 |    COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |
---------------------------------------------------------------------