아래와 같은 방식으로요~
"SELECT FROM TABLE(CAST(plsql_function AS collection_type))"
보통은 대량의 Data를 처리하는 기술로 많이 사용하였는데, 이런식으로 사용할 경우 sorting 작업이 있는 경우는 임시적으로 엄청난 양의 memory를 사용하는 제한이 있었다고 합니다.
Oracle 9i Release1부터 pipelined table function이라는 것이 소개 되었습니다.
Table Function이란 row들의 집합을 생성할수 있고 physical database table(column + table)과 같은 집합 형식으로도 보여 줄수 있습니다.
Table Function은 input값을 통해 row들의 집합을 생성합니다.
Table Function의 수행은 Parallel하게 수행되어 지며, return된 row들은 중간 단계 없이 다음 과정으로 직접적으로 연결되어 집니다.(이래서 pipe라는 용어가 붙여 진거 같습니다.)
* pipeline : 패치(fetch), 디코드, 실행의 명령 사이클이 끝나기 전에 다음 명령을 처리하기 시작하는 방식.
즉, Batch작업에서 Table Function의 input에 의해서 모든 작업를 완료한 후에 row들을 return하는 대신에 생성되는 즉시 반복적으로 row들이 return되는 방식입니다.
Streaming, pipelining, parallel execution을 이용하여 table function의 performance를 향상 시켰습니다.
- multithread가 가능함으로써 table function이 동시에 실행 됩니다.
- process들 사이의 intermidiate 단계를 제거하였습니다.
- query response time의 개선 : non-pipelined table function에서는 table function에 의해서 return되어지는 전체 row 집합들이 return할 수 있기 전에 server에서 완전한 결과의 집합을 가지고 있어야 하는 방식입니다. 하지만 Pipelining은 row들이 생성되자 마자 각각 return되는것이 가능합니다.
또한 Table Function은 전체 row들의 집합을 만들지 않으므로 당연히 전체 row들의 결과를 만들어서 저장하는 만큼의 memory 사용을 감소 시킬 수 있습니다.
예제.
1부터 49의 숫자중 6개의 unique한 random number를 생성하는 방법은?
보통 일반적으로 아래와 같은 방법을 이용할것 입니다.
SCOTT>select r
2 from (select r
3 from (select rownum r
4 from all_objects
5 where rownum < 50)
6 order by dbms_random.value)
7 where rownum <= 6;
R
----------
25
26
48
14
32
43
위의 문장을 살펴 보면
1. 가장 안쪽의 Inline View에서 1..49까지 row의 결과 집합을 생성합니다.
2. 이 결과 집합을 이용하여 DBMS_RANDOM.VALUE를 이용하여 정렬을 수행 합니다.
3. 위의 결과 집합을 이용하여 6개의 row만을 뽑아 내는 Query입니다.
하지만 위의 첫번째 Inline View에서 생성하는 Row의 집합이 계속 변해야 하는 경우, Virtual Table을 생성하는 것도 만만치 않을 겁니다.
이럴 경우, Pipelined Table Function을 이용하면 아주 빠르게 처리 할 수 있습니다.
1. Virtual Table을 생성하기 위한 Table Type의 array를 생성합니다.
SCOTT>create type array
2 as table of number
3 /
2. Pipelined Function을 생성합니다. 이 Function은 Return되는 row들의 수를 제한하는 Input을 받아 들이는 Function입니다.
SCOTT>create function
2 gen_numbers(n in number default null)
3 return array
4 PIPELINED -- pipelined function지정
5 as
6 begin
7 for i in 1 .. nvl(n,999999999)
8 loop
9 pipe row(i); -- 여기서 하나씩 return하여 전달
10 end loop;
11 return; -- Data Type을 지정하면은 안됨
12 end;
13 /
3. 이제, 3개의 row를 return 받는 Query를 수행합니다.2가지 방법으로 수행 할 수 있습니다.
SCOTT>select * from TABLE(gen_numbers(3));
COLUMN_VALUE
------------
1
2
3
SCOTT>select * from TABLE(gen_numbers)
2 where rownum <= 3;
COLUMN_VALUE
------------
1
2
3
SCOTT>select *
2 from (
3 select *
4 from (select * from table(gen_numbers(49)))
5 order by dbms_random.random
6 )
7 where rownum <= 6
8 /
COLUMN_VALUE
------------
21
6
31
1
35
4
위의 질문 처럼. 아래와 같은 방식으로 Virtual Table을 생성할 수 있습니다.
SCOTT>select *
2 from (
3 select *
4 from (select * from table(gen_numbers(49)))
5 order by dbms_random.random
6 )
7 where rownum <= 6
8 /
COLUMN_VALUE
------------
21
6
31
1
35
4
또한 날짜 타입의 virtual Table 생성도 가능합니다.
SCOTT>select to_date(’25-02-2009’, ’dd-mm-yyyy’) + column_value-1
2 from table(gen_numbers(10));
TO_DATE(’2
----------
2009-02-25
2009-02-26
2009-02-27
2009-02-28
2009-03-01
2009-03-02
2009-03-03
2009-03-04
2009-03-05
2009-03-06
* COLUMN_VALUE를 사용하였는데 PIPELINED function으로 부터 돌려 받는 column의 default name입니다.
위에처럼 로우 단위의 result set도 뽑을 수 있고, Table 형태로 result set을 받을 수도 있습니다.
1. Object Type을 생성합니다.
SCOTT>CREATE OR REPLACE TYPE myObjectFormat
2 AS OBJECT
3 (
4 A INT,
5 B DATE,
6 C VARCHAR2(25)
7 )
8 /
SCOTT>CREATE OR REPLACE TYPE myTableType
2 AS TABLE OF myObjectFormat
3 /
2. pipelined function을 생성합니다.
SCOTT>CREATE OR REPLACE PACKAGE myDemoPack
2 AS
3 FUNCTION prodFunc RETURN myTableType PIPELINED;
4 END;
5 /
SCOTT>CREATE OR REPLACE PACKAGE BODY myDemoPack AS
2 FUNCTION prodFunc RETURN myTableType PIPELINED IS
3 BEGIN
4 FOR i in 1 .. 5
5 LOOP
6 PIPE ROW (myObjectFormat(i,SYSDATE+i,’Row ’||i));
7 END LOOP;
8 RETURN;
9 END;
10 END;
11 /
3.Data확인
SCOTT>ALTER SESSION SET NLS_DATE_FORMAT=’dd.mm.yyyy’;
SCOTT>SELECT * FROM TABLE(myDemoPack.prodFunc());
A B C
---------- ---------- -------------------------
1 05.11.2009 Row 1
2 06.11.2009 Row 2
3 07.11.2009 Row 3
4 08.11.2009 Row 4
5 09.11.2009 Row 5
이렇게 사용하면은 됩니다.
한번 DBMS_XPLAN.DISPLAY 의 생성문을 한번 살펴 보았습니다.
function display(table_name varchar2 default ’PLAN_TABLE’,
statement_id varchar2 default null,
format varchar2 default ’TYPICAL’,
filter_preds varchar2 default null)
return dbms_xplan_type_table
pipelined;
이런식으로 되어 있습니다. 그래서 dbms_xplan.display를 select * from table(dbms_xplan.display)
이렇게 사용 가능한 것입니다.
참고 문헌
http://download.oracle.com/docs/cd/B13789_01/appdev.101/b10800/dcitblfns.htm
http://scidb.tistory.com/entry/Pipelined-Table-Function-의-사용
http://www.oracle-developer.net/display.php?id=207
http://www.akadia.com/services/ora_pipe_functions.html