| 1) 페이저 처리 또는 부분범위 처리활용{code:sql} |
|---|
SQL> select *
2 from (
3 select rownum no , a.*
4 from (
5 select lookup(1)
6 from all_objects
7 order by 1
8 ) a
9 where rownum <= 300
10 ) where no between 21 and 300;
280 개의 행이 선택되었습니다.
경 과: 00:00:08.04
select lookup(1),no
from (
select rownum no , a.*
from (
select rownum
from all_objects
order by 1
) a
where rownum <= 300
) where no between 21 and 300
280 개의 행이 선택되었습니다.
경 과: 00:00:02.17
맨마지막에서 함수가 일어나게 처리함으로 속도개선향상
||2) Decode, Case 함수 문으로 변환{code:sql}
-- 사용자정의함수
두개의 테이블을 만들고
create table t2 (no varchar2(2));
create table t3 (no varchar2(2));
t2 테이블에 1~5의 값이 들어간 데이터 백만건입력
SQL> insert into t2
2 select ceil(dbms_random.value(1,5)) no from dual
3 connect by level<= 1000000;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:07.78
SQL> commit;
커밋이 완료되었습니다.
함수 생성
CREATE OR REPLACE function trans
(
Str varchar2
)
RETURN VARCHAR2
IS
RET_VAL VARCHAR2 (4000);
BEGIN
if str = '1' then
RET_VAL := '일';
elsif str = '2' then
RET_VAL := '이';
elsif str = '3' then
RET_VAL := '삼';
elsif str = '4' then
RET_VAL := '사';
elsif str = '5' then
RET_VAL := '오';
end if;
RETURN RET_VAL;
end;
-사용자정의 insert
SQL> insert into t3
2 select trans(no) from t2;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:50.76
- 내장함수사용 insert
SQL> insert into t3
2 select decode(no,'1','일','2','이','3','삼','4','사','5','오') no from t2;
1000000 개의 행이 만들어졌습니다.
경 과: 00:01:06.89
-- decode사용
SQL> delete from t3;
2000000 행이 삭제되었습니다.
경 과: 00:01:24.54
SQL> insert into t3
2 select decode(no,'1','일','2','이','3','삼','4','사','5','오') no from t2;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:10.31
-- case사용
SQL> delete from t3;
1000000 행이 삭제되었습니다.
경 과: 00:00:29.32
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 개의 행이 만들어졌습니다.
경 과: 00:00:02.96
-- 테이블조인
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;
SQL> alter table t_code add constraint t_code_pk primary key(no);
테이블이 변경되었습니다.
SQL> delete from t3;
1000000 행이 삭제되었습니다.
경 과: 00:00:24.07
SQL> insert into t3
2 select korea_no
3 from t2 a , t_code b
4 where a.no = b.no;
1000000 개의 행이 만들어졌습니다.
경 과: 00:00:01.85
| 3) 뷰 머지 방지를 통한 함수 호출 최소화{code:sql} SQL> select sum(decode(trans(no),'일',1,0)) "1의총계", 2 sum(decode(trans(no),'이',1,0)) "2의총계", 3 sum(decode(trans(no),'삼',1,0)) "3의총계", 4 sum(decode(trans(no),'사',1,0)) "4의총계", 5 sum(decode(trans(no),'오',1,0)) "5의총계" 6 from t2 7 ; |
|---|
1의총계 2의총계 3의총계 4의총계 5의총계
call count cpu elapsed disk query current rows
경 과: 00:00:23.45
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select trans(no) no from t2
8 ) a;
1의총계 2의총계 3의총계 4의총계 5의총계
call count cpu elapsed disk query current rows
경 과: 00:00:24.09
– NO_MERGE 흰트를 주다...
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select /*+ NO_MERGE */trans(no) no from t2
8 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
경 과: 00:00:06.10
call count cpu elapsed disk query current rows
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select rownum,trans(no) no from t2
8 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
경 과: 00:00:05.39
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select trans(no) no from t2
8 where rownum > 0
9 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
경 과: 00:00:06.96
||4) 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화{code:sql}
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select (select trans(no) from dual) no from t2
8 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
---------- ---------- ---------- ---------- ----------
0 249557 250515 249514 250414
경 과: 00:00:00.89
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
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.76 0.76 0 1571 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.76 0.76 0 1572 0 1
-- _query_execution_cache_max_size 파라미터에 의해 캐시 사이즈를 증가후 테스트
SQL> alter session set "_query_execution_cache_max_size" =2097152;
세션이 변경되었습니다.
SQL> select sum(decode(no,'일',1,0)) "1의총계",
2 sum(decode(no,'이',1,0)) "2의총계",
3 sum(decode(no,'삼',1,0)) "3의총계",
4 sum(decode(no,'사',1,0)) "4의총계",
5 sum(decode(no,'오',1,0)) "5의총계"
6 from (
7 select (select trans(no) from dual) no from t2
8 ) a ;
1의총계 2의총계 3의총계 4의총계 5의총계
---------- ---------- ---------- ---------- ----------
0 249557 250515 249514 250414
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.82 0.82 0 1571 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.82 0.82 0 1571 0 1
경 과: 00:00:00.78
별로 변화가 없어서 줄이고 해보았다.
SQL> alter session set "_query_execution_cache_max_size" =100;
세션이 변경되었습니다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 2.00 1.98 0 3142 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 2.00 1.98 0 3142 0 2
| 5) Deterministic 함수의 캐싱 효과 활용{code:sql} |
|---|
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 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 return rvalue;
20
21 end;
22 /
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)
경 과: 00:00:01.67
SQL> create or replace function accum(p_input number) return numb
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 --call_cnt := call_cnt+1;
14 dbms_application_info.set_client_info(call_cnt+1);
15
16 for i in 1..p_input loop
17 rvalue := rValue + i;
18 end loop;
19 return rvalue;
20
21 end;
22 /
함수가 생성되었습니다.
경 과: 00:00:00.01
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)
경 과: 00:00:01.89
SQL> select sys_context('userenv', 'client_info') from dual
2 ;
SYS_CONTEXT('USERENV','CLIENT_INFO')
50
– deterministic 제거후테스트
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 --call_cnt := call_cnt+1;
14 dbms_application_info.set_client_info(call_cnt+1);
15
16 for i in 1..p_input loop
17 rvalue := rValue + i;
18 end loop;
19 return rvalue;
20
21 end;
22 /
함수가 생성되었습니다.
경 과: 00:00:00.06
SQL> exec dbms_application_info.set_client_info(null);
PL/SQL 처리가 정상적으로 완료되었습니다.
경 과: 00:00:00.00
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)
경 과: 00:00:10.07
함수를 1000000 호출한걸 확인할수있다.
SQL> select client_info
2 from v$session
3 where sid= sys_context('userenv','sid');
CLIENT_INFO
deterministic 요함수를 사용을 하므로써, 성능은 조아지지만 일괄성을 보장해주지않는다.
||6) 복잡한 함수 로직을 풀어 SQL로 구현{code:sql}
사용자(개발자) 입장에서는 난감한 경우다.
Function을 사용하여 좀더 가독성 및 객체지향? , 쉬운 유지보수가 젤 어떻게보면 맞을것이다.
때와 상황에 맞쳐서 사용하는게 좋을듯하다.