ORACLE [oracle, 오라클] Table Function & Pipelined Table 테이블 함수, 파이프라인드 함수

2020.06.14 17:31

[oracle, 오라클] Table Function & Pipelined Table 테이블 함수, 파이프라인드 함수

업무를 수행하다 보면 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을 생성

1

2

3

4

5

6

7

CREATE OR REPLACE TYPE obj_type AS object
( c1 INT,
  c2 INT
);
/
 
유형이 생성되었습니다.

Collection Type 생성

1

2

3

4

5

CREATE OR REPLACE TYPE table_type 
AS TABLE OF obj_type;
/
 
유형이 생성되었습니다.

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

CREATE OR REPLACE FUNCTION table_func (p_start int, p_end int)
  RETURN table_type
  IS
    v_type TABLE_TYPE := table_type();
  BEGIN
   
    FOR i IN p_start..p_end LOOP
      v_type.extend;
      v_type(i) := obj_type(i,i);
    END LOOP;
     
    RETURN v_type;
 END;
 /
 
함수가 생성되었습니다.

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

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을 어떻게 사용하는지 확인해 보자.

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

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

1

2

3

4

5

6

7

CREATE OR REPLACE TYPE obj_type1 AS object
( c1 INT,
  c2 INT
 );
 /
  
 유형이 생성되었습니다.

Collection Type 생성

1

2

3

4

5

CREATE OR REPLACE TYPE table_type1 
AS TABLE OF obj_type1;
/
 
유형이 생성되었습니다.

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

CREATE OR REPLACE FUNCTION pipe_table_func(p_start INT, p_end INT)
  RETURN table_type1
  PIPELINED
  IS
    v_type obj_type1;
  BEGIN
    FOR i IN p_start..p_end LOOP
      v_type := obj_type1(i, i);
      PIPE ROW(v_type);
    END LOOP;
  END;
 /
  
함수가 생성되었습니다.

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

1

2

3

4

5

6

7

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

1 2	`-- 커서가 깜빡거린 이후 일정 시간 경과 후(모든 결과가 계산됨) Row 가 출력된다.` `SELECT` `*` `FROM` `TABLE(table_func(1, 1000000));`

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

1 2	`-- 한 Row씩 처리하므로 바로 결과 값들이 출력되기 시작` `SELECT` `*` `FROM` `TABLE(pipe_table_func(1, 1000000));`

이처럼 Table Function은 전체 데이터 처리를 수행하지만 Pipelined Table Function은 부분 범위 처리를 수행한다는 것을 확인할 수 있다.

우리가 Oracle 10g부터 사용하는 dbms_xplan Package의 Function들도 Pipelined Table Function으로 구현되어 있다.

EXPLAIN PLAN FOR SELECT * FROM emp;
 
해석되었습니다.
 
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   555 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    15 |   555 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

[출처] http://www.gurubee.net/lecture/2238

본 웹사이트는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.

이 게시물을

번호	제목	글쓴이	날짜	조회 수
공지	오라클 기본 샘플 데이터베이스	졸리운_곰	2014.01.02	87182
공지	[SQL컨셉] 서적 "SQL컨셉"의 샘플 데이타 베이스 SAMPLE DATABASE of ORACLE	가을의 곰을...	2013.02.10	79397
공지	[G_SQL] Sample Database	가을의 곰을...	2012.05.20	96115
13	[데이터 수집 및 전처리] 주식 전종목 어떻게 불러올까? 거래소 종목 불러오기	졸리운_곰	2023.12.09	1707
12	[데이터 수집 및 전처리] [Python/파이썬]네이버증권API 활용 - 회사명, 종목코드 받아오기	졸리운_곰	2023.12.08	1510
11	[데이터 수집 및 전처리] 네이버 금융(차트)에서 주가 갈무리(크롤링)하기	졸리운_곰	2023.12.08	1497
10	[데이터 수집 및 전처리] 네이버 증권에서 일봉, 주봉 데이터 가져오기	졸리운_곰	2023.12.08	1505
9	[데이터 수집 및 전처리] (놀라운) 한글 데이터 짱! AwesomeKorean_Data	졸리운_곰	2023.03.07	1200
8	[데이터 수집 및 전처리] Crawling, Scraping	졸리운_곰	2022.05.21	1477
7	[데이터분석][데이터수집 전처리] MS 엑셀(Excel)에서 UTF-8 로 된 csv 파일 가져오기	졸리운_곰	2021.09.30	1417
6	카프카 설치 시 가장 중요한 설정 4가지	졸리운_곰	2021.07.13	1933
5	Prometheus Query(PromQL) 기본 이해하기	졸리운_곰	2020.12.17	1416
4	[인프라 모니터링 오픈소스] Prometheus 를 알아보자	졸리운_곰	2020.12.17	1915
3	Prometheus + Grafana 대시보드	졸리운_곰	2020.12.17	2495
2	Grafana란?	졸리운_곰	2020.12.17	2167
1	Importing wikipedia dump to MySql	졸리운_곰	2020.10.04	2501