- 전체
- Sample DB
- database modeling
- [표준 SQL] Standard SQL
- G-SQL
- 10-Min
- ORACLE
- MS SQLserver
- MySQL
- SQLite
- postgreSQL
- 데이터아키텍처전문가 - 국가공인자격
- 데이터 분석 전문가 [ADP]
- [국가공인] SQL 개발자/전문가
- NoSQL
- hadoop
- hadoop eco system
- big data (빅데이터)
- stat(통계) R 언어
- XML DB & XQuery
- spark
- DataBase Tool
- 데이터분석 & 데이터사이언스
- Engineer Quality Management
- [기계학습] machine learning
- 데이터 수집 및 전처리
- 국가기술자격 빅데이터분석기사
- 암호화폐 (비트코인, cryptocurrency, bitcoin)
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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를 이용해 아래와 같은 결과 추출
|
1
2
3
4
5
6
7
8
|
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을 생성
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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으로 구현되어 있다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
