출처 : http://ukja.tistory.com/197
Table Function을 즐겨 사용하는 사람들이 있을 것이다.
Table Function과 관련해서 잘 알려지지 않은 사실 중 하나는 Join에 관한 것이다. Table Function의 결과는 말 그대로 Table과 같으므로 Join에 문제가 없어야 한다.
하지만 어떻게?
간단한 예를 통해 Table Function을 Join에서 어떻게 사용하는지 알아 보자.
다음과 같이 Object Type과 Collection Type을 선언한다.
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을 생성한다.
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;
/
다음과 같이 사용된다.
C1 C2
———- ———-
1 1
2 2
3 3
4 4
5 5
…
99 99
100 100
좀 더 재미있는 테스트를 위해 다음과 같이 Argument를 받는 Function을 생성한다.
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;
/
다음과 같이 사용된다.
;
C1 C2
———- ———-
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
이 함수를 어떻게 다른 Table과 조인하는가?
create table t1(c1)
as
select level from dual connect by level <= 100
;
이런 방식은 지원되지 않는다.
from t1, table(func2(p1, p2, 10)) x
where t1.c1 = x.c1
;
다음과 같은 문법이 사용된다.
from t1, table(func2(t1.c1, t1.c1, 10))
;
C1 C1 C2
———- ———- ———-
1 2 2
1 3 3
…
즉, t1의 결과가 Function의 인자로 바로 사용된다. 이때 순서가 중요하다.
다음과 같이 순서가 바뀌면 Oracle은 처리하지 못한다.
from table(func2(t1.c1, t1.c1, 10)), t1
;
ERROR at line 2:
ORA-00904: “T1”.”C1″: invalid identifier
이 사실을 응용하면 다음과 같이 자유롭게 Join에 사용할 수 있다.
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을 가능하게 해 주는 좋은 기능이라고 생각된다.