October | 2012 | 오늘도 은퇴는 1주일 연기중…

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

다음과 같이 HttpServletResponse 객체에서 PrintWriter객체를 얻어서, 직접 텍스트를 출력할 경우 의도와 달리 브라우저 레벨에서는 해당 컨텐츠를 일반 텍스트로 인식하여, html파싱을 수행하지 않고, 코드를 그대로 화면에 출력하는 현상이 발생하는 경우가 있다.
(사실 현재 투입된 프로젝트의 소스를 분석하다 보니 그렇게 되어 있더라는……. 운영기에서 사용하는 WAS에서는 친절하게 알아서 헤더를 세팅해주는지 문제가 없는듯 하나, 개발환경의 WAS는 좀 더 엄격히 처리되는 다른 제품을 사용하다 보니 아래의 문제가 발생해서…..해결책을 찾아봄……….. )

public ModelAndView testWrite(HttpServletRequest request, HttpServletResponse response) throws Exception {
    PrintWriter out = response.getWriter();
    out.println("<HTML><HEAD><TITLE>TEST</TITLE>");
    out.println("<SCRIPT LANGUAGE=\"JAVASCRIPT\">");
    out.println(" function fnForward() {");
    out.println(" document.family.submit(); ");
    out.println(" }");
    out.println("</script></head>");
    out.println("<body onload=\"fnForward();\">");
    out.println(" <form name=\"family\" action=\"http://xxx.com\" method=\"post\">");
    out.println(" <input name=\"login_id\" type=\"hidden\" value=\"test\">");
    out.println(" <input name=\"password\" type=\"hidden\" value=\"test\">");
    out.println(" </form>");
    out.println("</body>");
    out.println("</HTML>");
}

JSP페이지라면 <@page contentType=”text/html;”> 형태로 페이지 상단에 해당 페이지가 html컨텐츠라는 정보를 명시하고 있기 때문에, WAS에서 JSP페이지를 실행한 결과를 반환할때 html컨텐츠라는 정보를 헤더에 포함해서 전달하고, 브라우저에서 정상적으로 파싱하여 표시가 되지만, Controller레벨에서 위의 코드와 같이 임의로 PrintWriter객체를 얻어서 출력하는 경우에는 헤더에 컨텐츠 종류가 어떤 것인지 명시되지 않아서, 브라우저가 일반텍스트로 인식하여 실행이 안되는 경우가 발생한다.

위의 현상을 해결하기 위해서 HttpServletResponse 객체에 setHeader() 메소드를 이용하여 강제로 아래와 같이 contentType을 지정해 봤으나……안되는;;;;;

response.setHeader("contentType", "text/html");

검색을 조금 더 해보니……response객체에는 setHeader말고…….아래와 같은 녀석이 있더라는……

response.setContentType("text/html");

위와같은 방법으로 response 객체에 컨텐츠 유형을 강제로 할당하고, PrintWriter 객체를 얻어서 출력할 경우 임의로 지정한 컨텐츠로 인식시킬 수 있다.
response 객체에는 setContentType() 이외에 setCharacterEncoding() 이나 setLocale() 등도 있으니… setHeader()를 이용하기 전에 이미 정의된 헤더값들을 처리하는 녀석이 있는지는 한번쯤 봐야할듯………(그런게 몇개 안됨;;;)

근데 Controller 레벨에서 직접 PrintWriter객체를 얻어서 출력하는건…….올바른 방법은 아닌듯……JSP페이지 하나 더 만드는게 그렇게 귀찮았나….;;;;;

S	M	T	W	T	F	S
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

오늘도 은퇴는 1주일 연기중…

Monthly Archives: October 2012

Table Function과 Join

HttpServletResponse 객체에서 PrintWriter 객체를 얻어서, 직접 출력시 contentType 처리.

오늘도 은퇴는 1주일 연기중…