Table Function과 Join

Table Function과 Join

 

2009/01/19 15:59

 

Table Function을 즐겨 사용하는 사람들이 있을 것이다.

Table Function과 관련해서 잘 알려지지 않은 사실 중 하나는 Join에 관한 것이다. Table Function의 결과는 말 그대로 Table과 같으므로 Join에 문제가 없어야 한다.

하지만 어떻게?

간단한 예를 통해 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을 가능하게 해 주는 좋은 기능이라고 생각된다.

 

http://ukja.tistory.com/197