"Table function"의 두 판 사이의 차이
DB CAFE
(새 문서: 업무를 수행하다 보면 Result Set 전체를 인자 값으로 받아서 결과를 Return하고자 하는 경우가 종종 있다. 이때 Oracle Table Function을 사용하면...) |
|||
239번째 줄: | 239번째 줄: | ||
-------------------------------------------------------------------------- | -------------------------------------------------------------------------- | ||
이처럼 Pipelined Table Function을 적재적소에 사용해 강력 한 PL/SQL Query를 잘 이용하길 바란다. | 이처럼 Pipelined Table Function을 적재적소에 사용해 강력 한 PL/SQL Query를 잘 이용하길 바란다. | ||
+ | |||
+ | |||
+ | 간단한 예를 통해 Table Function을 Join에서 어떻게 사용하는지 알아 보자. | ||
+ | |||
+ | 다음과 같이 Object Type과 Collection Type을 선언한다. | ||
+ | |||
+ | -- create objects | ||
+ | create or replace type obj_type1 as object ( | ||
+ | c1 int, | ||
+ | c2 int | ||
+ | ); | ||
+ | / | ||
+ | |||
+ | create or replace type obj_tbl_type1 as table of obj_type1; | ||
+ | / | ||
+ | |||
+ | |||
+ | Collection Type을 Return하는 Pipelined Function을 생성한다. | ||
+ | |||
+ | create or replace function func1 | ||
+ | return obj_tbl_type1 | ||
+ | pipelined | ||
+ | is | ||
+ | v_obj obj_type1; | ||
+ | begin | ||
+ | for idx in 1 .. 100 loop | ||
+ | v_obj := obj_type1(idx, idx); | ||
+ | pipe row(v_obj); | ||
+ | end loop; | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | |||
+ | 다음과 같이 사용된다. | ||
+ | |||
+ | select * from table(func1()); | ||
+ | |||
+ | C1 C2 | ||
+ | ---------- ---------- | ||
+ | 1 1 | ||
+ | 2 2 | ||
+ | 3 3 | ||
+ | 4 4 | ||
+ | 5 5 | ||
+ | ... | ||
+ | 99 99 | ||
+ | 100 100 | ||
+ | |||
+ | |||
+ | |||
+ | 좀 더 재미있는 테스트를 위해 다음과 같이 Argument를 받는 Function을 생성한다. | ||
+ | |||
+ | create or replace function func2(p1 int, p2 int, p3 int) | ||
+ | return obj_tbl_type1 | ||
+ | pipelined | ||
+ | is | ||
+ | v_obj obj_type1; | ||
+ | begin | ||
+ | for idx in 1 .. p3 loop | ||
+ | v_obj := obj_type1(p1+idx, p2+idx); | ||
+ | pipe row(v_obj); | ||
+ | end loop; | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | |||
+ | 다음과 같이 사용된다. | ||
+ | |||
+ | select * from table(func2(1, 1, 10)) | ||
+ | ; | ||
+ | C1 C2 | ||
+ | ---------- ---------- | ||
+ | 2 2 | ||
+ | 3 3 | ||
+ | 4 4 | ||
+ | 5 5 | ||
+ | 6 6 | ||
+ | 7 7 | ||
+ | 8 8 | ||
+ | 9 9 | ||
+ | 10 10 | ||
+ | 11 11 | ||
+ | |||
+ | |||
+ | 이 함수를 어떻게 다른 Table과 조인하는가? | ||
+ | |||
+ | drop table t1 purge; | ||
+ | create table t1(c1) | ||
+ | as | ||
+ | select level from dual connect by level <= 100 | ||
+ | ; | ||
+ | |||
+ | 이런 방식은 지원되지 않는다. | ||
+ | |||
+ | select * | ||
+ | from t1, table(func2(p1, p2, 10)) x | ||
+ | where t1.c1 = x.c1 | ||
+ | ; | ||
+ | |||
+ | |||
+ | 다음과 같은 문법이 사용된다. | ||
+ | |||
+ | select * | ||
+ | from t1, table(func2(t1.c1, t1.c1, 10)) | ||
+ | ; | ||
+ | |||
+ | C1 C1 C2 | ||
+ | ---------- ---------- ---------- | ||
+ | 1 2 2 | ||
+ | 1 3 3 | ||
+ | ... | ||
+ | |||
+ | 즉, t1의 결과가 Function의 인자로 바로 사용된다. 이때 순서가 중요하다. | ||
+ | 다음과 같이 순서가 바뀌면 Oracle은 처리하지 못한다. | ||
+ | |||
+ | select * | ||
+ | from table(func2(t1.c1, t1.c1, 10)), t1 | ||
+ | ; | ||
+ | ERROR at line 2: | ||
+ | ORA-00904: "T1"."C1": invalid identifier | ||
+ | |||
+ | |||
+ | 이 사실을 응용하면 다음과 같이 자유롭게 Join에 사용할 수 있다. | ||
+ | |||
+ | select * | ||
+ | from | ||
+ | (select null as c1, null as c2 from dual connect by level <= 100) s, | ||
+ | table(func2(s.c1, s.c1, 10)) | ||
+ | ; | ||
+ | |||
+ | |||
+ | 잘 이용하면 매우 강력한 Query를 만들 수 있다. | ||
+ | |||
+ | 가령 아래 Query를 보자. Shared Pool(v$sql)에 Cache되어 있는 Query들 중 buffer_gets(logical reads) 수치가 높은 순으로 Runtime 실행 계획을 추출한다. 이런 복잡해 보이는 요구 사항도 Table Function의 Join 기능을 잘 이용하면 매우 간단한게 구현할 수 있다. | ||
+ | |||
+ | select plan_table_output | ||
+ | from | ||
+ | (select * from | ||
+ | (select s.sql_id, s.child_number | ||
+ | from v$sql s | ||
+ | where exists(select 1 from v$sql_plan p where p.plan_hash_value = s.plan_hash_value) | ||
+ | order by s.buffer_gets desc) | ||
+ | where rownum <= 10 | ||
+ | ) s, | ||
+ | table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'allstats last')) | ||
+ | ; | ||
+ | |||
+ | (출력 문제로 짤림) | ||
+ | PLAN_TABLE_OUTPUT | ||
+ | ------------------------------------------------------------------------------- | ||
+ | SQL_ID 803b7z0t84sq7, child number 0 | ||
+ | ------------------------------------- | ||
+ | select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and ( | ||
+ | ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and | ||
+ | (this_date is null) order by next_date, job | ||
+ | |||
+ | Plan hash value: 1846751226 | ||
+ | |||
+ | ------------------------------------------------------------------------------- | ||
+ | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buf | ||
+ | ------------------------------------------------------------------------------- | ||
+ | | 1 | SORT ORDER BY | | 1 | 1 | 0 |00:00:00.01 | | ||
+ | |* 2 | TABLE ACCESS FULL| JOB$ | 1 | 1 | 0 |00:00:00.01 | | ||
+ | ------------------------------------------------------------------------------- | ||
+ | |||
+ | Predicate Information (identified by operation id): | ||
+ | --------------------------------------------------- | ||
+ | |||
+ | 2 - filter(((("NEXT_DATE">=:1 AND "NEXT_DATE"<:2) OR ("LAST_DATE" IS NULL AN | ||
+ | ("FIELD1"=:4 OR ('Y'=:5 AND "FIELD1"=0)) AND "THIS_DATE" IS NULL) | ||
+ | |||
+ | SQL_ID 96g93hntrzjtr, child number 0 | ||
+ | ------------------------------------- | ||
+ | select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample | ||
+ | minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln | ||
+ | hist_head$ where obj#=:1 and intcol#=:2 | ||
+ | |||
+ | Plan hash value: 2239883476 | ||
+ | |||
+ | ------------------------------------------------------------------------------- | ||
+ | | Id | Operation | Name | Starts | A-Rows | A | ||
+ | ------------------------------------------------------------------------------- | ||
+ | | 1 | TABLE ACCESS BY INDEX ROWID| HIST_HEAD$ | 1 | 1 |00:0 | ||
+ | |* 2 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 1 |00:0 | ||
+ | ------------------------------------------------------------------------------- | ||
+ | |||
+ | Predicate Information (identified by operation id): | ||
+ | --------------------------------------------------- | ||
+ | |||
+ | 2 - access("OBJ#"=:1 AND "INTCOL#"=:2) | ||
+ | |||
+ | Note | ||
+ | ----- | ||
+ | - rule based optimizer used (consider using cbo) | ||
+ | ... | ||
+ | |||
+ | |||
+ | (Pipelined) Table Function은 적재적소에 잘 사용하면 매우 세련된 Query와 Application을 가능하게 해 주는 좋은 기능이라고 생각된다. |
2020년 11월 20일 (금) 19:21 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
업무를 수행하다 보면 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를 잘 이용하길 바란다.
간단한 예를 통해 Table Function을 Join에서 어떻게 사용하는지 알아 보자.
다음과 같이 Object Type과 Collection Type을 선언한다.
-- create objects create or replace type obj_type1 as object (
c1 int, c2 int
); /
create or replace type obj_tbl_type1 as table of obj_type1; /
Collection Type을 Return하는 Pipelined Function을 생성한다.
create or replace function func1 return obj_tbl_type1 pipelined is
v_obj obj_type1;
begin
for idx in 1 .. 100 loop v_obj := obj_type1(idx, idx); pipe row(v_obj); end loop;
end; /
다음과 같이 사용된다.
select * from table(func1());
C1 C2
----------
1 1 2 2 3 3 4 4 5 5
...
99 99 100 100
좀 더 재미있는 테스트를 위해 다음과 같이 Argument를 받는 Function을 생성한다.
create or replace function func2(p1 int, p2 int, p3 int) return obj_tbl_type1 pipelined is
v_obj obj_type1;
begin
for idx in 1 .. p3 loop v_obj := obj_type1(p1+idx, p2+idx); pipe row(v_obj); end loop;
end; /
다음과 같이 사용된다.
select * from table(func2(1, 1, 10))
C1 C2
----------
2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10 11 11
이 함수를 어떻게 다른 Table과 조인하는가?
drop table t1 purge; create table t1(c1) as select level from dual connect by level <= 100
이런 방식은 지원되지 않는다.
select * from t1, table(func2(p1, p2, 10)) x where t1.c1 = x.c1
다음과 같은 문법이 사용된다.
select * from t1, table(func2(t1.c1, t1.c1, 10))
C1 C1 C2
---------- ----------
1 2 2 1 3 3
...
즉, t1의 결과가 Function의 인자로 바로 사용된다. 이때 순서가 중요하다. 다음과 같이 순서가 바뀌면 Oracle은 처리하지 못한다.
select * from table(func2(t1.c1, t1.c1, 10)), t1
ERROR at line 2: ORA-00904: "T1"."C1": invalid identifier
이 사실을 응용하면 다음과 같이 자유롭게 Join에 사용할 수 있다.
select * from
(select null as c1, null as c2 from dual connect by level <= 100) s, table(func2(s.c1, s.c1, 10))
잘 이용하면 매우 강력한 Query를 만들 수 있다.
가령 아래 Query를 보자. Shared Pool(v$sql)에 Cache되어 있는 Query들 중 buffer_gets(logical reads) 수치가 높은 순으로 Runtime 실행 계획을 추출한다. 이런 복잡해 보이는 요구 사항도 Table Function의 Join 기능을 잘 이용하면 매우 간단한게 구현할 수 있다.
select plan_table_output from
(select * from (select s.sql_id, s.child_number from v$sql s where exists(select 1 from v$sql_plan p where p.plan_hash_value = s.plan_hash_value) order by s.buffer_gets desc) where rownum <= 10 ) s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'allstats last'))
(출력 문제로 짤림) PLAN_TABLE_OUTPUT
SQL_ID 803b7z0t84sq7, child number 0
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and ( ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and (this_date is null) order by next_date, job
Plan hash value: 1846751226
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buf
| 1 | SORT ORDER BY | | 1 | 1 | 0 |00:00:00.01 | |* 2 | TABLE ACCESS FULL| JOB$ | 1 | 1 | 0 |00:00:00.01 |
Predicate Information (identified by operation id):
2 - filter(((("NEXT_DATE">=:1 AND "NEXT_DATE"<:2) OR ("LAST_DATE" IS NULL AN ("FIELD1"=:4 OR ('Y'=:5 AND "FIELD1"=0)) AND "THIS_DATE" IS NULL)
SQL_ID 96g93hntrzjtr, child number 0
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln hist_head$ where obj#=:1 and intcol#=:2
Plan hash value: 2239883476
| Id | Operation | Name | Starts | A-Rows | A
| 1 | TABLE ACCESS BY INDEX ROWID| HIST_HEAD$ | 1 | 1 |00:0 |* 2 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 1 | 1 |00:0
Predicate Information (identified by operation id):
2 - access("OBJ#"=:1 AND "INTCOL#"=:2)
Note
- rule based optimizer used (consider using cbo)
...
(Pipelined) Table Function은 적재적소에 잘 사용하면 매우 세련된 Query와 Application을 가능하게 해 주는 좋은 기능이라고 생각된다.