Pipelined Table Function 1 9 6,729

by drakula [기타] [2009.11.04 16:18:56]


Oracle 8.0이후에 "virtual tble"로서 data의 집합을(보통은 function을 이용) select from절에 사용 하였다고 합니다.

아래와 같은 방식으로요~
"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


by 오정희 [2009.11.04 16:23:37]
나도 너한테 물어보기전에 조동욱 아저씨 블로그에서 잠깐 봤는데, 내 지식이 짧아서 먼얘긴지 도통 몰겠드라고~ 오전내내 찾다 물어본겨~ㅋㅋ
아하하 아하하~ 왕땡큐야!!!

by 김정식 [2009.11.04 16:27:55]
아 정말 좋은 내용인데..
가혜 요즘 안 바쁜겨? 6시간이나 투자하다니..ㅎㅎ

by 타락천사 [2009.11.04 17:15:06]
음 가끔 요청이 와서 컴파일 하다보면 pipeline 이라는 구절이 있었는데..
크게 생각안했었는데.. 이런게 심오한 내용이 있었나..
즉, Batch작업에서 Table Function의 input에 의해서 모든 작업를 완료한 후에 row들을 return하는 대신에 생성되는 즉시 반복적으로 row들이 return되는 방식입니다.
==> 이것때문에 Pipe_function 을 쓰는건가 ? 이게 장점이야 ㅇㅇ?
늘 그렇듯이..개념이 안잡히는데 ㅡ_ㅡ;

by drakula [2009.11.04 17:20:42]
내가 너무 어렵게 적었나봐.ㅠ.ㅠ
웅~~~~ 모든 작업을 완료한 후가 아니라 반복적으로 row들이 return되고~
이 작업이 또한 parallel하게 진행된다는거징~!!!

그게, 가장 큰 장점이얌^^

by 웅 [2009.11.05 13:09:40]
우왕 자세하네...예전 욱짜에서 볼때는 그냥 이런게 있구나했는데..
땡큐~

by 김원식 [2009.11.05 14:22:46]
왠지 일할때 자주 써먹어야 할 것 같다는 예감이....

by TeLl2 [2009.11.05 17:33:13]
뭐가 뭔지 몰라도..
좋은 내용인듯...ㅋ

by 나무 [2009.11.06 17:08:43]
오 *..* 멋진 내용이당. ^^ 좋은정보 고마버~~

by 부쉬맨 [2013.01.04 15:48:09]
단순 문자열을 짤라서 세로로 보여준다는 개념으로 생각했는데
역시 속도와 성능까지...
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입