태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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

               


                                                                              


신고
Trackback 0 : Comments 4
  1. 박병섭 2009.01.30 10:36 신고 Modify/Delete Reply

    안녕하세요..
    욱짜의 오라클 블로그가 조동욱님꺼였네요. 쓰신 책들은 사서(RAC빼고는 모두 샀습니다. ) 아주.. 힘들게 보고 있습니다.
    ㅡㅡ;
    최근엔 옵티마이징 오라클 옵티마이저를 보기 시작했습니다. 한번씩 보고 필요한 책을 다시 볼예정이지만, 너무 어렵네요.

    이런 정보를 주신거에 감사드리는 마음으로 글을 남깁니다.

    공부하면 공부할수록 배울게 너무 많아지네요. 언제쯤이면 중급자가 될수있는지도 모르겠습니다.

  2. 욱짜 2009.01.30 13:34 신고 Modify/Delete Reply

    이번 책 [Optimizing Oracle Optimizer]는 되도록 쉽게 쓸려고 했는데, 마음먹은 대로 되지 않은 것 같습니다.

    하지만 이전 책들보다는 훨씬 쉽고 체계적이라고 생각하고 있습니다.

    책을 보시다가 궁금하거나 이상한 점은 언제든지 문의해주세요.

  3. 유수익 2009.04.03 20:56 신고 Modify/Delete Reply

    plan정보는 어떻게 가져오나요? 음.. 재미있네요

    NOTE: cannot fetch plan for SQL_ID 8x83p6cctmgmb, CHILD_NUMBER: 0
    Was STATISTICS_LEVEL set to ALL for the session that run the statement ?

  4. 욱짜 2009.04.03 21:31 신고 Modify/Delete Reply

    http://121.254.172.39:7777/pls/apex/f?p=101:11:0::::P11_QUESTION_ID:32900346354855

Write a comment

티스토리 툴바