h3.(1) PL/SQL 함수의 특징
h5.PL/SQL의 단점
!컨텍스트 스위칭.GIF!
h5.참고
인터프리터란? 프로그래밍 소스 코드를 바로 실행하는 컴퓨터 프로그램 또는 환경을 뜻하며, Runtime 시점에 해석하여 바로 실행
h3.(2) Recursive Call를 포함하지 않는 함수의 성능 부하
SQL> create or replace function data_to_char(p_Dt date) return varchar2
2 as
3 begin
4 return to_char(p_dt,'yyyy/mm/dd hh24:mi:ss');
5 end;
6 /
Function created.
SQL> create table t (no number, char_time varchar2(21));
Table created.
-- 내장함수사용
SQL> INSERT INTO t
2 SELECT rownum no
3 ,TO_CHAR(sysdate+rownum, 'yyyy/mm/dd hh24:mi:ss') char_time
4 FROM dual
5 CONNECT BY level <= 1000000 ;
1000000 rows created.
Elapsed: 00:00:05.70
-- 사용자가만든 함수사용
SQL> INSERT INTO t
2 SELECT rownum no
3 ,data_to_char(sysdate+rownum) char_time
4 FROM dual
5 CONNECT BY level <= 1000000 ;
1000000 rows created.
Elapsed: 00:00:08.67
h2.(3) Recursive Call를 포함 하는 함수의 성능 부하
- 책에 소스 오류있음.
SQL> create or replace function data_to_char(p_Dt date) return varchar2
2 as
3 l_empno number;
4
5 begin
6 select 1 into l_empno from dual;
7
8 return to_char(p_dt,'yyyy/mm/dd hh24:mi:ss');
9 end;
10 /
Function created.
SQL> delete from t;
2000000 rows deleted.
SQL> INSERT INTO t
2 SELECT rownum no
3 ,data_to_char(sysdate+rownum) char_time
4 FROM dual
5 CONNECT BY level <= 1000000 ;
1000000 rows created.
Elapsed: 00:00:24.25
- 트레이스결과
SELECT 1
FROM DUAL
call count cpu elapsed disk query current rows
------- ------- -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000000 15.09 12.98 0 0 0 0
Fetch 1000000 10.64 8.73 0 0 0 1000000
------- ------- -------- ---------- ---------- ---------- ---------- ----------
total 2000001 25.73 21.71 0 0 0 1000000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 73 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1000000 FAST DUAL (cr=0 pr=0 pw=0 time=6174443 us)
h2.(4) 함수를 필터 조건으로 사용할 때 주의 사항
SQL> create or replace function emp_avg_sal return number
2 is
3 l_avg_sal number;
4
5 begin
6 select avg(sal) into l_avg_sal from emp;
7
8 return l_avg_sal;
9 end;
10 /
Function created.
SQL> create index emp_x01 on emp(sal);
Index created.
SQL> create index emp_x02 on emp(deptno);
Index created.
SQL> create index emp_x03 on emp(deptno, sal);
Index created.
SQL> create index emp_x04 on emp(deptno, ename, sal);
Index created.
<케이스1 : 인덱스를 사용하지 않고 Full Scan할때는 읽은 전체 건수만큼 함수호출>
SQL> SELECT /*+ full(emp) */ *
2 FROM emp
3 WHERE sal >= emp_avg_sal;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 22 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.003 0 226 0 7
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.006 0 248 0 7
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
7 TABLE ACCESS FULL EMP (cr=226 pr=0 pw=0 time=1517 us)
SELECT AVG(SAL) FROM EMP
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 14 0.000 0.000 0 0 0 0
Fetch 14 0.000 0.001 0 210 0 14
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 29 0.000 0.001 0 210 0 14
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Recursive depth: 1
* 전체 조회 된 Row 수 만큼 함수 호출.
<케이스 2 : 인덱스를 이용>
SQL> SELECT /*+ index(emp(sal)) */ *
2 FROM emp
3 WHERE sal >= emp_avg_sal;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.005 0 22 0 0
Execute 1 0.000 0.000 0 15 0 0
Fetch 2 0.000 0.003 1 4 0 7
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.008 1 41 0 7
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
7 TABLE ACCESS BY INDEX ROWID EMP (cr=19 pr=1 pw=0 time=2796 us)
7 INDEX RANGE SCAN EMP_X01 (cr=17 pr=1 pw=0 time=2854 us)(Object ID 89319122)
SELECT AVG(SAL) FROM EMP
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 15 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 3 0.000 0.000 0 15 0 1
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Recursive depth: 1
* 옵티마이져가 인덱스 Predicate를 Access조건으로 인식을 하여 첫번째 함수 실행으로 리턴 된 결과값을 Index Assecc 하는데 상수조건으로
사용되므로 함수호출은 1번
<케이스 3 : 조건절에 deptno = 20 추가, exp_x02(deptno)인덱스이용하여 조회>
SQL> SELECT /*+ index(emp, emp_x02) */ *
2 FROM emp
3 WHERE sal >= emp_avg_sal
4 AND deptno = 20;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.007 0 25 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.005 1 64 0 2
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.012 1 89 0 2
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
2 TABLE ACCESS BY INDEX ROWID EMP (cr=64 pr=1 pw=0 time=4146 us)
4 INDEX RANGE SCAN EMP_X02 (cr=2 pr=1 pw=0 time=3599 us)(Object ID 89319133)
SELECT AVG(SAL) FROM EMP
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 4 0.000 0.000 0 0 0 0
Fetch 4 0.000 0.000 0 60 0 4
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 9 0.000 0.000 0 60 0 4
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Recursive depth: 1
* deptnp = 20 조건에 만족하는 값 4 Rows를 가지고 sal >= 비교를 하기 위해 함수호출 할 때, Predicate를 Filter조건으로 인식 하기
때문에 테이블을 엑세스하는 횟수만큼 건건이 함수 호출 발생
<케이스 4 : deptno + sal 순으로 구성된 emp_x03(deptno, sal) 인덱스이용>
SQL> SELECT /*+ index(emp(deptno, sal) */ *
2 FROM emp
3 WHERE sal >= emp_avg_sal
4 AND deptno = 20;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.007 0 25 0 0
Execute 1 0.000 0.000 0 15 0 0
Fetch 2 0.000 0.002 1 4 0 2
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.009 1 44 0 2
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
2 TABLE ACCESS BY INDEX ROWID EMP (cr=19 pr=1 pw=0 time=2116 us)
2 INDEX RANGE SCAN EMP_X03 (cr=17 pr=1 pw=0 time=2120 us)(Object ID 89319134)
SELECT AVG(SAL) FROM EMP
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 15 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 3 0.000 0.001 0 15 0 1
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Recursive depth: 1
* deptno = 20 조건에 만족하는 4Rows를 가지고 와서 sal >= 비교 할 때, Index Predicate를 Assecc로 인식을 하여 첫번째 실행되어 리턴 된 값을 인덱스 엑세스 하는데 상수값으로 사용 되기 때문에 함수 호출은 1번
<케이스 5 : 조건은 같고, deptno 와 sal 컬럼중간인 ename 컬럼이 낀 emp_x04(deptno, ename, sal) 인덱스사용>
SQL> SELECT /*+ index(emp(deptno, ename, sal)) */ *
2 FROM emp
3 WHERE sal >= emp_avg_sal
4 AND deptno = 20;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.004 0 25 0 0
Execute 1 0.000 0.000 0 15 0 0
Fetch 2 0.010 0.001 1 64 0 2
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.006 1 104 0 2
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
2 TABLE ACCESS BY INDEX ROWID EMP (cr=79 pr=1 pw=0 time=1002 us)
2 INDEX RANGE SCAN EMP_X04 (cr=77 pr=1 pw=0 time=1176 us)(Object ID 89319135)
SELECT AVG(SAL) FROM EMP
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 5 0.000 0.000 0 0 0 0
Fetch 5 0.000 0.000 0 75 0 5
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 11 0.000 0.000 0 75 0 5
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Recursive depth: 1
* Index Predicate가 Access 조건일 때, 인덱스 스캔을 위해 함수 호출 1번
Index Predicate가 Filter 조건일 때, 나머지 3건에 대한 함수 호출 3번
deptno = 20 범위를 넘어 더이상 조건을 만족하는 레코드가 없음을 확인 하는 one-plus 스캔 과정에서 1번 총 5번
<케이스 6 : = 조건이 아닌경우>
SQL> SELECT /*+ index(emp(deptno, sal)) */ *
2 FROM emp
3 WHERE sal >= emp_avg_sal
4 AND deptno >= 10
5 /
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.005 0 25 0 0
Execute 1 0.000 0.000 0 15 0 0
Fetch 2 0.010 0.002 1 199 0 6
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.008 1 239 0 6
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
6 TABLE ACCESS BY INDEX ROWID EMP (cr=214 pr=1 pw=0 time=897 us)
6 INDEX RANGE SCAN EMP_X03 (cr=212 pr=1 pw=0 time=1918 us)(Object ID 89319134)
SELECT AVG(SAL) FROM EMP
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 14 0.000 0.000 0 0 0 0
Fetch 14 0.000 0.001 0 210 0 14
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 29 0.000 0.001 0 210 0 14
Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Recursive depth: 1
* 인덱스 컬럼 구조상
1) 선행 컬럼이 조건절에 누락
2) '=' 조건이 아닌 Range 조건 일 경우, Predicate는 filter 조건으로 인식 된다.
따라서, 인덱스 스캔할 첫 번째 레코드를 엑세스 하기위해 1번, deptno >= 10 조건에 만족하는 나머지 13번 총 14번
h3.(5) 함수와 읽기 일관성
ex) 그림
!함수와 읽기 일관성.GIF!
SQL> create table lookuptable(key number, value varchar2(100));
Table created.
SQL> insert into lookuptable values (1, 'HSH');
1 row created.
SQL> commit;
Commit complete.
SQL> CREATE OR REPLACE FUNCTION lookup(l_input NUMBER) RETURN VARCHAR2
2 AS
3 l_output lookuptable.value%TYPE;
4 BEGIN
5 SELECT value into l_output FROM lookuptable WHERE key = l_input;
6 RETURN l_output;
7 END;
8 /
Function created.
** lookup 함수를 호출하는 건수만큼 lookuptable 테이블의 Value 컴럼 값(HSH)을 출력 **
SQL> SELECT lookup(1) FROM all_objects;
** 다른세션에서 함수에 참조되는 있는 테이블(lookuptable)을 Update **
SQL> UPDATE lookuptable
2 SET value ='HSH&PJM';
1 행이 갱신되었습니다.
SQL> commit;
커밋이 완료되었습니다.
결과는 P401 그림 5-9참조
h3.(6) 함수의 올바른 사용 기준
- 함수 사용 시, 되도록이면 내장함수 사용 추천
- 사용자 정의 함수사용 시, 소량의 데이타를 조회 할 목적으로 만 사용 하는것을 추천
- Where 조건에 함수를 사용하는건 지양
- 만일 어쩔수 없이 써야 될 경우, 인덱스를 활용해서 Where 조건에 함수를 사용
- 일관성 위배가 소지 여부를 확인하여 사용
이 필자가 말하기를 결론은 그때그때 상황에 맞게 알아서 써라!!!!!