SELECT 절 펑션 수행 횟수 확인

단순 조회와 집계함수(SUM,MAX) 사용 시 SELECT 절 펑션 몇 번 실행 횟 수 차이 요약 SELECT LV, F_SQLP(LV) FROM SQLP GROUP BY LV ; -- 위 SQL은 아래와 같이 작동한다. SELECT LV, F_SQLP(LV) FROM ( SELECT LV FROM SQLP GROUP BY LV ) ; -- SUM과 같은 함수는 위 SQL처럼 동작할 수 없다 SELECT LV, SUM(F_SQLP(LV)) FROM SQLP GROUP BY LV;

 
테스트 함수 생성

CREATE OR REPLACE FUNCTION F_SQLP(p_num NUMBER)
RETURN NUMBER
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_num)||':리턴');
RETURN p_num;
END;
/

 
SQL 테스트

-- SQL 실행
SELECT LV, F_SQLP(LV)
FROM SQLP
GROUP BY LV;

--결과
LV F_SQLP(LV)
---------- ----------
 1            1
 6            6
 2            2
 4            4
 5            5
 8            8
 3            3
 7            7
 9            9
 0            0

10 rows selected.

1:리턴
6:리턴
2:리턴
4:리턴
5:리턴
8:리턴
3:리턴
7:리턴
9:리턴
0:리턴

 
SUM, MAX 사용 테스트

-- SUM, MAX 예제
SELECT LV, SUM(F_SQLP(LV))
FROM SQLP
GROUP BY LV;


SELECT LV, MAX(F_SQLP(LV))
FROM SQLP
GROUP BY LV;


-- 결과
LV SUM(F_SQLP(LV))
---------- ---------------
 1                10
 6                60
 2                20
 4                40
 5                50
 8                80
 3                30
 7                70
 9                90
 0                 0

10 rows selected.

1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴
1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴
1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴
1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴
1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴
1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴
1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴
1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴
1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴
1:리턴
2:리턴
3:리턴
4:리턴
5:리턴
6:리턴
7:리턴
8:리턴
9:리턴
0:리턴

 
10046 trace 테스트

CREATE OR REPLACE FUNCTION SEL_D(p_num NUMBER)
RETURN NUMBER
IS
lv_num number ;
BEGIN
SELECT p_num into lv_num FROM dual ;
RETURN lv_num;
END;
/

-- 실행
SELECT LV, SUM(sel_d(LV))
FROM SQLP
GROUP BY LV;

-- 10046 trace
********************************************************************************
SELECT LV, SUM(scott.sel_d(LV))
FROM scott.SQLP
GROUP BY LV

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.01       0.01          0          3          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0          4          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  HASH GROUP BY (cr=3 pr=0 pw=0 time=0 us cost=4 size=1300 card=100)
    100   TABLE ACCESS FULL SQLP (cr=3 pr=0 pw=0 time=198 us cost=3 size=1300 card=100)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  asynch descriptor resize                        2        0.00          0.00
  SQL*Net message from client                     2       12.00         12.00
********************************************************************************

-- 아래 부분 펑션 100 회 수행 확인 가능
SQL ID: f7b5mry8rz5dk
Plan Hash: 1388734953
SELECT :B1
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    100      0.00       0.01          0          0          0           0
Fetch      100      0.00       0.00          0          0          0         100 --! 100 회 수행
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201      0.00       0.01          0          0          0         100

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 88     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
********************************************************************************