Table Function

업무를 수행하다 보면 Result Set 전체를 인자 값으로 받아서 결과를 Return하고자 하는 경우가 종종 있다. 이때 Oracle Table Function을 사용하면 이를 간단히 해결할 수 있다. Oracle Table Function은 Result Set(Multi column + Multi Row)의 형태를 인자 값으로 받아들여 값을 Return할 수 있는 PL/SQL Function이고, Pipelined Table Function은 Oracle Table Function과 마찬가지로 Result Set의 형태로 인자 값을 제공하거나 전체 집합을 한번에 처리하지 않고 Row 단위로 한 건씩 처리하는 Function으로 PL/SQL의 부분범위 처리를 가능하게 해주는 Function이다. 그럼 Table Function과 Pipelined Table Function을 살펴보도록 하자. 

 

Table Function은 어떻게 사용하는가?

 

Table Function은 Function으로 정의되며 Function의 Input으로 Row들의 집합을 취할 수 있고 출력으로 Row들의 집합을 생성할 수 있다. Query의 FROM 절에서 ‘TABLE’이라는 키워드로 접근 가능하며 Return Type은 Nested Table 또는 Varray 형태이다. 간단한 예제를 통해 Table Function을 어떻게 사용하는지 확인해 보자.

 

● Return 받을 행을 받는 Object Type을 생성

SQL>create or replace type obj_type as object
 2 ( c1 int,
 3 c2 int
 4 );
유형이 생성되었습니다.

 

● Collection Type 생성

SQL>create or replace type table_type as table of obj_type;
유형이 생성되었습니다.

 

● Table Function을 생성 - 원하는 만큼의 Row를 출력하는 Function

SQL>create or replace function table_func(p_start int, p_end int)
 2 return table_type
 3 is 
 4 v_type table_type := table_type();
 5 begin
 6 for i in p_start..p_end loop
 7 v_type.extend;
 8 v_type(i) := obj_type(i,i);
 9 end loop;
 10 return v_type;
 11 end;
 12 /
함수가 생성되었습니다.

 

● FROM 절에 ‘TABLE’이라는 Keyword를 이용해 아래와 같은 결과 추출

SQL>select * from TABLE(table_func(1,3)); 
C1         C2
---------- ----------
1           1
2           2
3           3

 

 

Pipelined Table Function은 어떻게 사용하는가?

 

다음은 Pipelined Table Function에 대해 알아보도록 하자. Pipelined Table Function은 한 행 단위로 즉시 값을 리턴하는 함수로, 9i 이상에서만 가능하며 수행 속도가 향상되었고 부분범위 처리가 가능하다. 간단한 예제를 통해 Pipelined Table Function을 어떻게 사용하는지 확인해 보자.

 

● Return 받을 행을 받는 Object Type을 생성

SQL>create or replace type obj_type1 as object
 2 ( c1 int,
 3 c2 int
 4 );
유형이 생성되었습니다.

 

● Collection Type 생성

SQL>create or replace type table_type1 as table of obj_type1;
유형이 생성되었습니다.

 

● Pipelined Table Function을 생성 - 원하는 만큼의 Row를 출력하는 Function

SQL>create or replace function pipe_table_func(p_start int, p_end int)
 2  return table_type1
 3  pipelined 
 4  is 
 5  v_type obj_type1;
 6  begin
 7  for i in p_start..p_end loop
 8  v_type := obj_type1(i, i);
 9  pipe row(v_type);
 10 end loop; 
 11 end;
 12 /
함수가 생성되었습니다.

 

● FROM 절에 ‘TABLE’이라는 Keyword를 이용해 수행한다면 아래와 같은 결과 추출

SQL>select * from TABLE(pipe_table_func(1,3));
C1         C2
---------- ----------
1           1
2           2
3           3

 

 

Pipelined Table Function은 하나의 Row를 받아서 바로 처리하므로 수행 속도가 빠르다. 이에 비해 Table Function은 전체 Row가 처리된 이후에 동작되므로 Pipelined Table Function에 비해 이전 처리된 Row를 Cache할 Memory를 더 요구하게 된다.

 

Table Function & Pipelined Table Function을 비교하자

 

위에서 생성한 Function을 이용해 Table Function & Pipelined Table Function을 확인할 수 있다.

 

● Table Function만을 사용한 table_func에 큰 Row를 Return하는 Test

SQL> select * from TABLE(table_func(1, 1000000));

 

● 커서가 깜빡거린 이후 일정 시간 경과 후(모든 결과가 계산됨) Row가 출력

 

● Pipeline Table Function만을 사용한 table_func에 큰 Row를 Return하는 Test

SQL> select * from TABLE(pipe_table_func(1, 1000000));

 

● 한 Row씩 처리하므로 바로 결과 값들이 출력되기 시작

 

이처럼 Table Function은 전체 데이터 처리를 수행하지만 Pipelined Table Function은 부분 범위 처리를 수행한다는 것을 확인할 수 있다. 
우리가 Oracle 10g부터 사용하는 dbms_xplan Package의 Function들도 Pipelined Table Function으로 구현되어 있다.

 

SQL>explain plan for
2 select * from test;
해석되었습니다.

SQL>select * from TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 1357081020
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------
| 0 | SELECT STATEMENT |       | 50 | 650 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL | TEST | 50 | 650 | 2 (0)| 00:00:01 |

 

이처럼 Pipelined Table Function을 적재적소에 사용해 강력한 PL/SQL Query를 잘 이용하길 바란다.

출처 : 한국 마이크로 소프트웨어 [2010년 3월호]

제공 : DB포탈사이트 DBguide.net

[출처] [본문스크랩] Table Function & Pipelined Table|작성자 아삽

[출처] [본문스크랩] Table Function & Pipelined Table|작성자 아삽