1) 소량의 데이터 조회 시
2) 대용량 데이터를 조회 할 때는 부분범위처리가 가능한 상황에서 제한적으로 사용
3) 조인 또는 스칼라 서브쿼리 형태로 변환 할려는 노력
4) 구현상 어쩔 수 없을 때는 함수를 쓰되 호출 횟수를 최소화 할 수 있는 방법 강구
-- 함수를 서브쿼리에 넣은 경우.
SELECT *
FROM (
SELECT rownum no
,ao.*
FROM (
SELECT lookup(1)
FROM all_objects
ORDER BY 1
) ao
WHERE rownum <= 300
)
WHERE no BETWEEN 21 AND 30;
280 rows selected.
Elapsed: 00:00:16.14
-- 함수를 SQL-List에 넣은 경우
SELECT lookup(1)
,no
FROM (
SELECT rownum no
,ao.*
FROM (
SELECT rownum
FROM all_objects
ORDER BY 1
) ao
WHERE rownum <= 300
)
WHERE no BETWEEN 21 AND 30;
280 rows selected.
Elapsed: 00:00:13.00
-- 함수를 SQL-List에 넣어 두는 것이 성능적으로 효과를 볼 수 있다.
-- 함수가 안쪽 인라인뷰에서 Order by 절에 사용, 전체 결과집합을 모두 출력, INSERT...SELECT문에서 사용 된다면 다량의 함수 호출을 피할 수 없다.
-- 함수 로직을 풀어서 Decode, Case, Join문으로 전환 하여 사용 할 수 있는지 확인 한다.
-- 만약 전환이 가능 하다면 전환 해서 쓰는 것을 추천 할 것이다.
-- 프로젝트 상 함수를 사용 해야 되거나, 로직이 복잡할 경우에는 함수를 스칼라 서브쿼리로 감싸서 사용 하는 것을 추천 할 것이다.
-- Table 생성
SQL> create table t2 (no varchar2(5));
Table created.
SQL> create table t3 (no varchar2(5));
Table created.
-- t2 테이블에 1~5의 값이 들어간 데이터 백만건입력
SQL> insert into t2
2 select ceil(dbms_random.value(1,5)) no from dual
3 connect by level<= 1000000;
1000000 rows created.
SQL> commit;
Commit complete.
-- 함수 생성
SQL> CREATE OR REPLACE function trans
2 (
3 Str varchar2
4 )
5 RETURN VARCHAR2
6 IS
7 RET_VAL VARCHAR2 (4000);
8 BEGIN
9 if str = '1' then
10 RET_VAL := '일';
11 elsif str = '2' then
12 RET_VAL := '이';
13 elsif str = '3' then
14 RET_VAL := '삼';
15 elsif str = '4' then
16 RET_VAL := '사';
17 elsif str = '5' then
18 RET_VAL := '오';
19 end if;
20 RETURN RET_VAL;
21 end;
22 /
Function created.
Case1) Select문만 날렸을 때 비교
-- 사용자 정의 함수 사용
SQL> SELECT trans(no) FROM t2 WHERE rownum <= 100000;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 10001 0.040 0.750 154 10156 0 100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10003 0.040 0.750 154 10156 0 100000
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100000 COUNT STOPKEY (cr=10156 pr=154 pw=0 time=2216 us)
100000 TABLE ACCESS FULL T2 (cr=10156 pr=154 pw=0 time=2206 us)
-- Decode문 변환 했을 때
SQL> SELECT DECODE(no,'1','일'
,'2','이'
,'3','삼'
,'4','사'
,'5','오') no FROM t2 WHERE rownum <= 100000;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.020 0.020 0 150 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 10001 0.010 0.208 0 10156 0 100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10003 0.030 0.228 0 10306 0 100000
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100000 COUNT STOPKEY (cr=10156 pr=0 pw=0 time=27 us)
100000 TABLE ACCESS FULL T2 (cr=10156 pr=0 pw=0 time=25 us)
Case2) Insert일 경우
-- 사용자정의 insert
SQL> insert into t3
2 select trans(no) from t2;
1000000 rows created.
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.020 0.026 0 75 0 0
Execute 1 7.540 14.824 1425 50555 20277 1000000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 7.560 14.850 1425 50630 20277 1000000
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1000000 TABLE ACCESS FULL T2 (cr=2189 pr=1312 pw=0 time=2000045 us)
********************************************************************************
select file# from file$ where ts#=:1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 96 0.010 0.006 0 0 0 0
Execute 96 0.000 0.007 0 0 0 0
Fetch 40800 0.210 0.362 0 44256 0 40704
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 40992 0.220 0.376 0 44256 0 40704
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user: SYS (ID=0)
Recursive depth: 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
424 TABLE ACCESS FULL FILE$ (cr=461 pr=0 pw=0 time=2199 us)
********************************************************************************
select blocks,maxblocks,grantor#,priv1,priv2,priv3 from tsq$ where ts#=:1 and
user#=:2
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 1 0.000 0.000 0 3 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 3 0.000 0.000 0 3 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user: SYS (ID=0)
Recursive depth: 1
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 TABLE ACCESS CLUSTER TSQ$ (cr=3 pr=0 pw=0 time=67 us)
1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=19 us)(Object ID 11)
********************************************************************************
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,
extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=
decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,
NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 96 0.000 0.005 0 0 0 0
Execute 96 0.000 0.061 0 576 96 96
Fetch 0 0.000 0.000
-- Decode문 변환
SQL> insert into t3
2 select decode(no,'1','일'
,'2','이'
,'3','삼'
,'4','사'
,'5','오') no from t2;
1000000 rows created.
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.015 0 75 0 0
Execute 1 2.180 2.800 342 4476 16011 1000000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 2.190 2.815 342 4551 16011 1000000
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1000000 TABLE ACCESS FULL T2 (cr=1532 pr=342 pw=0 time=50 us)
-- Case문 변환
SQL> insert into t3
2 select case when no = '1' then '일'
3 when no = '2' then '이'
4 when no = '3' then '삼'
5 when no = '4' then '사'
6 when no = '5' then '오' end no from t2;
1000000 rows created.
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.020 0.018 0 79 0 0
Execute 1 2.530 3.497 2834 4476 16016 1000000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 2.550 3.515 2834 4555 16016 1000000
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1000000 TABLE ACCESS FULL T2 (cr=1532 pr=1286 pw=0 time=1002612 us)
-- Join문 변환
-- Target Table 생성
SQL> create table t_code(no,korea_no)
2 as
3 select'1','일' from dual union all
4 select'2','이' from dual union all
5 select'3','삼' from dual union all
6 select'4','사' from dual union all
7 select'5','오' from dual;
Table created.
SQL> alter table t_code add constraint t_code_pk primary key(no);
Table altered.
SQL> insert into t3
2 select korea_no
3 from t2 a , t_code b
4 where a.no = b.no;
1000000 rows created.
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.030 0.054 0 84 0 0
Execute 1 2.600 4.806 1819 10606 16012 1000000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 2.630 4.860 1819 10690 16012 1000000
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1000000 NESTED LOOPS (cr=7662 pr=1455 pw=0 time=1740068 us)
5 TABLE ACCESS BY INDEX ROWID T_CODE (cr=2 pr=1 pw=0 time=1051 us)
5 INDEX FULL SCAN T_CODE_PK (cr=1 pr=1 pw=0 time=1007 us)(Object ID 89322346)
1000000 TABLE ACCESS FULL T2 (cr=7660 pr=1454 pw=0 time=1739210 us)
* 함수를 쓰게 되면서 함수가 컴파일 되는 과정에서 내부적으로 Select 문장이 돌면서 Block I/O가 발생이 되면서 성능에 부하를 주게 된다.
-- Select-List에 함수를 넣었을 때
select sum(decode(trans(no),'일',1,0)) "1의총계"
,sum(decode(trans(no),'이',1,0)) "2의총계"
,sum(decode(trans(no),'삼',1,0)) "3의총계"
,sum(decode(trans(no),'사',1,0)) "4의총계"
,sum(decode(trans(no),'오',1,0)) "5의총계"
from t2;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.015 0 75 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 16.430 17.774 1328 1532 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 16.440 17.789 1328 1607 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=1532 pr=1328 pw=0 time=17773554 us)
1000000 TABLE ACCESS FULL T2 (cr=1532 pr=1328 pw=0 time=2007988 us)
-- Inline-View에 함수를 넣었을 때
select sum(decode(no,'일',1,0)) "1의총계"
,sum(decode(no,'이',1,0)) "2의총계"
,sum(decode(no,'삼',1,0)) "3의총계"
,sum(decode(no,'사',1,0)) "4의총계"
,sum(decode(no,'오',1,0)) "5의총계"
from (
select trans(no) no from t2
) a;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.020 0 75 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 15.970 18.384 1372 1532 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 15.980 18.404 1372 1607 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=1532 pr=1372 pw=0 time=18384457 us)
1000000 TABLE ACCESS FULL T2 (cr=1532 pr=1372 pw=0 time=1001899 us)
-- No_Merge Hint 사용 시
select sum(decode(no,'일',1,0)) "1의총계"
,sum(decode(no,'이',1,0)) "2의총계"
,sum(decode(no,'삼',1,0)) "3의총계"
,sum(decode(no,'사',1,0)) "4의총계"
,sum(decode(no,'오',1,0)) "5의총계"
from (
select /*+ NO_MERGE */trans(no) no from t2
) a;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.014 0 75 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 4.350 4.699 1417 1532 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 4.360 4.713 1417 1607 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=1532 pr=1417 pw=0 time=4698583 us)
1000000 VIEW (cr=1532 pr=1417 pw=0 time=4001647 us)
1000000 TABLE ACCESS FULL T2 (cr=1532 pr=1417 pw=0 time=1542 us)
-- Rownum 사용 시1
select sum(decode(no,'일',1,0)) "1의총계"
,sum(decode(no,'이',1,0)) "2의총계"
,sum(decode(no,'삼',1,0)) "3의총계"
,sum(decode(no,'사',1,0)) "4의총계"
,sum(decode(no,'오',1,0)) "5의총계"
from (
select rownum, trans(no) no from t2
) a;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.014 0 75 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 4.490 4.636 32 1532 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 4.500 4.650 32 1607 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=1532 pr=32 pw=0 time=4636410 us)
1000000 VIEW (cr=1532 pr=32 pw=0 time=4000136 us)
1000000 COUNT (cr=1532 pr=32 pw=0 time=44 us)
1000000 TABLE ACCESS FULL T2 (cr=1532 pr=32 pw=0 time=41 us)
-- Rownum 사용 시2
select sum(decode(no,'일',1,0)) "1의총계"
,sum(decode(no,'이',1,0)) "2의총계"
,sum(decode(no,'삼',1,0)) "3의총계"
,sum(decode(no,'사',1,0)) "4의총계"
,sum(decode(no,'오',1,0)) "5의총계"
from (
select trans(no) no from t2
where rownum > 0
) a;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.016 0 75 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 5.880 6.865 0 1532 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 5.890 6.882 0 1607 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=1532 pr=0 pw=0 time=6865383 us)
1000000 VIEW (cr=1532 pr=0 pw=0 time=6000193 us)
1000000 COUNT (cr=1532 pr=0 pw=0 time=2000048 us)
1000000 FILTER (cr=1532 pr=0 pw=0 time=1000047 us)
1000000 TABLE ACCESS FULL T2 (cr=1532 pr=0 pw=0 time=42 us)
-- 함수 생성
SQL> CREATE OR REPLACE function trans
2 (
3 Str varchar2
4 )
5 RETURN VARCHAR2
6 IS
7 RET_VAL VARCHAR2 (4000);
8 BEGIN
9 if str = '1' then
10 RET_VAL := '일';
11 elsif str = '2' then
12 RET_VAL := '이';
13 elsif str = '3' then
14 RET_VAL := '삼';
15 elsif str = '4' then
16 RET_VAL := '사';
17 elsif str = '5' then
18 RET_VAL := '오';
19 end if;
20
21 SELECT korea_no INTO ret_val
22 FROM t_code
23 WHERE korea_no = ret_val;
24
25 RETURN RET_VAL;
26 end;
27 /
Function created.
-- Query 수행
select sum(decode(no,'일',1,0)) "1의총계"
,sum(decode(no,'이',1,0)) "2의총계"
,sum(decode(no,'삼',1,0)) "3의총계"
,sum(decode(no,'사',1,0)) "4의총계"
,sum(decode(no,'오',1,0)) "5의총계"
from (
select (select trans(no) from dual) no from t2
) a;
SELECT KOREA_NO FROM T_CODE WHERE KOREA_NO = :B1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 750042 7.050 7.845 0 0 0 0
Fetch 750042 11.480 13.546 2 2250126 0 750042
------- ------- -------- ------------ ---------- ---------- ---------- ----------
Total 1500085 18.530 21.391 2 2250126 0 750042
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Recursive depth: 1
-- Hidden 파라메터값 증가
SQL> ALTER SESSION SET "_query_execution_cache_max_size" = 2097152;
-- 다시 Query 수행
select sum(decode(no,'일',1,0)) "1의총계"
,sum(decode(no,'이',1,0)) "2의총계"
,sum(decode(no,'삼',1,0)) "3의총계"
,sum(decode(no,'사',1,0)) "4의총계"
,sum(decode(no,'오',1,0)) "5의총계"
from (
select (select trans(no) from dual) no from t2
) a;
SELECT KOREA_NO FROM T_CODE WHERE KOREA_NO = :B1
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 4 0.000 0.000 0 0 0 0
Fetch 4 0.000 0.001 2 12 0 4
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 9 0.000 0.002 2 12 0 4
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Recursive depth: 1
9i 부터 설정가능
1. _query_execution_cache_max_size=4194304
: SQL 수행 시 select list clause에서 function call 과다에 의한 성능부하가 심할 경우가 많이 있다. 그런 경우에는 function call 부분을 스칼라서브쿼리로 변경하여 function call에 의한 부하를
최소화 할 필요가 있다. 이런 경우 스칼라 서브쿼리를 위한 buffer cache 영역을 늘려주어야 하며, _query_execution_cache_max_size 로 메모리 사이즈를 늘려주어야 한다.
일반적으로 OLTP 홖경에서는 4M 정도가 적당하며 Function Call을 스칼라 서브쿼리로 변경할 경우 스칼라 서브쿼리 buffer cache 변경 외에 또 다른 주의할 사항이 있는데, Function Call에 의해 데이터
를 리턴 받을 때 옵티마이저는 리턴 받는 데이터를 위하여 varchar2(4000) 만큼 Memory 영역을 점유하게 되므로, substr()로 적정하게 사이즈를 두어 비효율적으로 옵티마이저가 메모리를 점유하지 않게 하
여야 한다.
* CGA(Call Global Area)
: 이름과 달리 Global Area는 아니며, Call과 관련된 정보를 저장하기 위한 임시공간
따라서, Call이 발생되면 임시로 저장해 두었다가 끝나는 시점에 그 값들을 해제 되는 공간
(SQL Parsing, SQL Executing, SELECT문에 대한 Row Source Fetch시에 사용)
* UGA(User Global Area)
: 세션과 관련된 정보를 저장하기 위한 공간
Fixed UGA : 세션에 대한 기본 정보, Variable UGA의 포인터 저장
Variable UGA : Heap으로 구성
(Open Cursor에 대한 Run Time 영역, Privtate SQL, PL/SQL영역, Package Status, Enable 된 Role, Open되어있는 DB link 정보)
-- Deterministic 함수 포함된 Function 생성
SQL> create or replace function accum(p_input number) return number
2 deterministic
3 as
4 rvalue number := 0;
5 call_cnt number := 0;
6 call_cnt2 number := 0;
7
8 begin
9 dbms_application_info.read_client_info(call_cnt);
10 if call_cnt is null then
11 call_cnt := 0;
12 end if;
13
14 call_cnt := call_cnt+1;
15 dbms_application_info.read_client_info(call_cnt2);
16
17 for i in 1..p_input loop
18 rvalue := rValue + i;
19 end loop;
20
21 return rvalue;
22
23 end;
24 /
Function created.
-- Query 실행
SQL> select sum(accum_num)
2 from (
3 select accum(mod(rownum,50)) accum_num
4 from dual
5 connect by level <= 1000000
6 );
SUM(ACCUM_NUM)
--------------
416500000
-- 함수 호출 확인
SQL> select sys_context('userenv', 'client_info') from dual;
SYS_CONTEXT('USERENV','CLIENT_INFO')
----------------------------------------------------------------
50
-- Deterministic 함수 미포함된 Function 생성
SQL> create or replace function accum(p_input number) return number
2 as
3 rvalue number := 0;
4 call_cnt number := 0;
5 call_cnt2 number := 0;
6
7 begin
8 dbms_application_info.read_client_info(call_cnt);
9 if call_cnt is null then
10 call_cnt := 0;
11 end if;
12
13 call_cnt := call_cnt+1;
14 dbms_application_info.read_client_info(call_cnt2);
15
16 for i in 1..p_input loop
17 rvalue := rValue + i;
18 end loop;
19
20 return rvalue;
21
22 end;
23 /
Function created.
-- Query 실행
SQL> select sum(accum_num)
2 from (
3 select accum(mod(rownum,50)) accum_num
4 from dual
5 connect by level <= 1000000
6 );
SUM(ACCUM_NUM)
--------------
416500000
-- 함수 호출 확인
SQL> select client_info
2 from v$session
3 where sid= sys_context('userenv','sid');
CLIENT_INFO
----------------------------------------------------------------
1000000
http://kpserver.egloos.com/469219
* 분할 접근 방식
Query 문장은 간단하게 1줄로 끝날 수도 있지만, 여러 Inline View를 사용하여 수십줄의 Query문장이 나올 수도 있다.
1줄로 코딩이 된 문장은 당연히 한눈에 알아 볼수 있을 것이다. 하지만 수십줄의 Query문장을 분석할때 어떻게 해야 되나 막막할 것이다.
이럴때 유용하게 쓰이는 방식이 분할 접근 방식이다. 각각의 Inline View, 또는 테이블을 각각 하나의 집합체로 가정하고, 기초데이타 뼈대 부터
하나하나씩 살을 붙여 나간다면 접근하기가 쉬울 것이다. 아래 예제를 함 보자
ex)
SELECT WEEK_2
, MIN(SUN) SUN
, MIN(MON) MON
, MIN(TUE) TUE
, MIN(WED) WED
, MIN(THU) THU
, MIN(FRI) FRI
, MIN(SAT) SAT
FROM (SELECT VAL
, VAL_1
, VAL_2
, WEEK_1
, WEEK_2
, DECODE(WEEK_1, '1', VAL_2) "SUN"
, DECODE(WEEK_1, '2', VAL_2) "MON"
, DECODE(WEEK_1, '3', VAL_2) "TUE"
, DECODE(WEEK_1, '4', VAL_2) "WED"
, DECODE(WEEK_1, '5', VAL_2) "THU"
, DECODE(WEEK_1, '6', VAL_2) "FRI"
, DECODE(WEEK_1, '7', VAL_2) "SAT"
FROM (SELECT VAL
, VAL_1
, VAL_2
, WEEK_1
, TO_CHAR(VAL_2, 'DD') - WEEK_1 "WEEK_2"
FROM (SELECT VAL
, VAL_1
, VAL_2
, TO_CHAR(VAL_2, 'D') WEEK_1
FROM (SELECT VAL_1 + LEVEL - 1 VAL_2
, VAL_1
, VAL
FROM (SELECT TRUNC(VAL, 'MM') VAL_1
, VAL
FROM (SELECT TO_DATE('20110115','YYYYMMDD') VAL FROM DUAL))
CONNECT BY LEVEL <= ADD_MONTHS(VAL_1 ,1) - VAL_1
)
)
)
)
GROUP BY WEEK_2
ORDER BY WEEK_2