"Table function"의 두 판 사이의 차이

업무를 수행하다 보면 Result Set 전체를 인자 값으로 받아서 결과를 Return하고자 하는 경우가 종종 있다. 이때 Oracle Table Function을 사용하면 이를 간단히 해결할 수 있다.

Oracle Table Function은 Result Set(Multi column + Multi Row)의 형태를 인자 값으로 받아들여 값을 Return할 수 있는 PL/SQL Function이고, Pipelined Table Function은 Oracle Table Function과 마찬가지로 Result Set의 형태로 인자 값을 제공하거나 전체 집합을 한번에 처리하지 않고 Row 단위로 한 건씩 처리하는 Function으로 PL/SQL의 부분범위 처리를 가능하게 해주는 Function이다.

그럼 Table Function과 Pipelined Table Function을 살펴보도록 하자.

Table Function은 어떻게 사용하는가? Table Function은 Function으로 정의되며 Function의 Input으로 Row들의 집합을 취할 수 있고 출력으로 Row들의 집합을 생성할 수 있다.

Query의 FROM 절에서 'TABLE'이라는 키워드로 접근 가능하며 Return Type은 Nested Table 또는 Varray 형태이다. 간단한 예제를 통해 Table Function을 어떻게 사용하는지 확인해 보자.

Return 받을 행을 받는 Object Type을 생성 ? 1 2 3 4 5 6 7 CREATE OR REPLACE TYPE obj_type AS object ( c1 INT,

 c2 INT

); /

유형이 생성되었습니다.

Collection Type 생성 ? 1 2 3 4 5 CREATE OR REPLACE TYPE table_type AS TABLE OF obj_type; /

유형이 생성되었습니다.

Table Function을 생성 - 원하는 만큼의 Row를 출력하는 Function ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE OR REPLACE FUNCTION table_func (p_start int, p_end int)

 RETURN table_type
 IS
   v_type TABLE_TYPE := table_type();
 BEGIN
  
   FOR i IN p_start..p_end LOOP
     v_type.extend;
     v_type(i) := obj_type(i,i);
   END LOOP;
    
   RETURN v_type;
END;
/

함수가 생성되었습니다.

FROM 절에 'TABLE'이라는 Keyword를 이용해 아래와 같은 결과 추출 ? 1 2 3 4 5 6 7 8 SELECT * FROM TABLE(table_func(1,3));

  C1         C2

---

----------

   1          1
   2          2
   3          3

Pipelined Table Function은 어떻게 사용하는가? 다음은 Pipelined Table Function에 대해 알아보도록 하자.

Pipelined Table Function은 한 행 단위로 즉시 값을 리턴하는 함수로, 9i 이상에서만 가능하며 수행 속도가 향상되었고 부분범 위 처리가 가능하다.

간단한 예제를 통해 Pipelined Table Function을 어떻게 사용하는지 확인해 보자.

Return 받을 행을 받는 Object Type을 생성 ? 1 2 3 4 5 6 7 CREATE OR REPLACE TYPE obj_type1 AS object ( c1 INT,

 c2 INT
);
/
 
유형이 생성되었습니다.

Collection Type 생성 ? 1 2 3 4 5 CREATE OR REPLACE TYPE table_type1 AS TABLE OF obj_type1; /

유형이 생성되었습니다.

Pipelined Table Function을 생성 - 원하는 만큼의 Row를 출력하는 Function ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE OR REPLACE FUNCTION pipe_table_func(p_start INT, p_end INT)

 RETURN table_type1
 PIPELINED
 IS
   v_type obj_type1;
 BEGIN
   FOR i IN p_start..p_end LOOP
     v_type := obj_type1(i, i);
     PIPE ROW(v_type);
   END LOOP;
 END;
/
 

함수가 생성되었습니다.

FROM 절에 'TABLE'이라는 Keyword를 이용해 수행한다면 아래와 같은 결과 추출 ? 1 2 3 4 5 6 7 SELECT * FROM TABLE(pipe_table_func(1,3));

  C1         C2

---

----------

   1          1
   2          2
   3          3

Pipelined Table Function은 하나의 Row를 받아서 바로 처리하므로 수행 속도가 빠르다. 이에 비해 Table Function은 전체 Row가 처리된 이후에 동작되므로 Pipelined Table Function에 비해 이전 처리된 Row를 Cache할 Memory를 더 요구하게 된다.

Table Function & Pipelined Table Function을 비교하자 위에서 생성한 Function을 이용해 Table Function & Pipelined Table Function을 확인할 수 있다.

Table Function만을 사용한 table_func에 큰 Row를 Return하는 Test ? 1 2 -- 커서가 깜빡거린 이후 일정 시간 경과 후(모든 결과가 계산됨) Row 가 출력된다. SELECT * FROM TABLE(table_func(1, 1000000));

Pipeline Table Function만을 사용한 table_func에 큰 Row를 Return하는 Test ? 1 2 -- 한 Row씩 처리하므로 바로 결과 값들이 출력되기 시작 SELECT * FROM TABLE(pipe_table_func(1, 1000000)); 이처럼 Table Function은 전체 데이터 처리를 수행하지만 Pipelined Table Function은 부분 범위 처리를 수행한다는 것을 확인할 수 있다.

우리가 Oracle 10g부터 사용하는 dbms_xplan Package의 Function들도 Pipelined Table Function으로 구현되어 있다.

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 EXPLAIN PLAN FOR SELECT * FROM emp;

해석되었습니다.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

---

Plan hash value: 3956160932

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 15 | 555 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP | 15 | 555 | 3 (0)| 00:00:01 |

---

이처럼 Pipelined Table Function을 적재적소에 사용해 강력 한 PL/SQL Query를 잘 이용하길 바란다.

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