Daily Archives: 2012-10-10

misc.

2012-10-10

Leave a comment

늘 그랬던 것처럼 오늘도 일하기 싫다.

새 프로젝트에 투입된지 1주일.
여기와서는 화장실도 자주 안가고,
원래 많이 쉬기는 했었지만, 쉬는 시간도 줄은듯.
덩달아 군것질도 줄어듬.

첫날을 제외하고 계속 자전거타고 출퇴근중.
천천히 와도 30분이면 충분.
매일매일 세이브되는 교통비는 적금중(ㅡ.ㅡ;)

뱃속에 거지가 들어 앉아있는 건지, 기생충이 많은건지 배고픔.

겨울 송어 낚시를 위해서 주문한 낚시 바늘 도착함.
100개나 구입했고, 작년에 쓰던것도 어딘가 좀 남아있을테니, 올겨울은 이걸로 충분할듯.
이제 메탈지그만 장만하면 될듯……

etc

Table Function과 Join

Leave a comment

출처 : http://ukja.tistory.com/197

 

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