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;
/
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;
/
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
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;
/
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
;
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
;
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
;
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
...
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
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))
;
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을 가능하게 해 주는 좋은 기능이라고 생각된다.
'정보과학 IT' 카테고리의 다른 글
[Oracle] PARTITION BY 구문 (0) | 2012.07.17 |
---|---|
TABLE FUNCTION 사용하기 (0) | 2012.07.13 |
DECODE, NULLIF, NVL, and NVL2 in Oracle (0) | 2012.07.09 |
[Oracle] 부정형(NOT IN, <>, NOT EXISTS ...)의 비교 (0) | 2012.07.09 |
래치(Latch)와 락(Lock) (0) | 2012.07.06 |