펑션 위치별 실행 횟 수

- 스칼라서브쿼리와 달리 펑션은 실행 시점의 데이터를 가져와서 타세션에서 데이터 변경시 데이터가 달라질 수 있음
- 펑션 위치별 실행 횟수의 차이를 알아본다

select 절 펑션

 : 메인 쿼리의 모든 결과만큼 수행

SELECT owner, object_name, sk.sel_d(object_id)
FROM sk.tab10

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     4862      1.12       1.60          0       6185          0       72914
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4864      1.12       1.60          0       6185          0       72914

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     72914      72914      72914  TABLE ACCESS FULL TAB10 (cr=6185 pr=0 pw=0 time=27341 us starts=1 cost=394 size=3281130 card=72914)

SELECT :B1
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  72914      1.08       1.49          0          0          0           0
Fetch    72914      0.03       0.05          0          0          0       72914
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   145829      1.11       1.54          0          0          0       72914

 

 

반응형

where 절에 조건 있을 경우 select 절 펑션

 : 조건에 해당하는 select 결과 만큼 수행

SELECT owner, object_name, sk.sel_d(object_id)
FROM sk.tab10
WHERE owner = 'SK'

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       12      0.00       0.00          0         35          0         162
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0         35          0         162

SELECT :B1
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    162      0.00       0.00          0          0          0           0
Fetch      162      0.00       0.00          0          0          0         162
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      325      0.00       0.00          0          0          0         162

 

where 절에 조건 및 펑션, select 절 펑션

 : SK조건 결과(조건에서 걸러진 것) 162건에 펑션 조건 수행 + 최종 select 결과 펑션수행(49) = 211

SELECT owner, object_name, sk.sel_d(object_id)
FROM sk.tab10
WHERE owner = 'SK'
AND sk.sel_d(object_id) >= 83000

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        5      0.00       0.00          0         22          0          49
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.00          0         22          0          49

SELECT :B1
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    211      0.00       0.00          0          0          0           0
Fetch      211      0.00       0.00          0          0          0         211
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      423      0.00       0.00          0          0          0         211