ORACLE POSITION OF FUNCTION
from
1.
ORACLE POSITION OF FUNCTION
ORACLE 의 FUNCTION 은 주의해서 사용해야 한다. 이것이 SELECT-LIST 에 오느냐 WHERE 절에 오느냐에 따라서 주의할
점도 달라진다. 밑에서 내용을 진행하기 전에 테스트용으로 HR
USER 를 사용했고 DNAME 이라는 입력된
EMP.DEPARTMENT_ID 에 대해 DEPARTMENTS.DEPARTMENT_NAME 을
반환하는 FUNCTION 을 생성하였다.
SQL> CREATE OR REPLACE FUNCTION DNAME(INP
IN NUMBER)
2 RETURN VARCHAR2
3 IS
4 TMP VARCHAR2(20);
5 BEGIN
6 SELECT DEPARTMENT_NAME INTO TMP
FROM HR.DEPARTMENTS
7 WHERE DEPARTMENT_ID = INP;
8 RETURN TMP;
9 END;
10 /
함수가 생성되었습니다.
추가적으로 DEPARTMENTS.LOCATION_ID 를 받아 LOCATIONS.CITY 를 반환하는 LNAME 을 생성했다.
create or replace function lname(inp in
number)
return varchar2 is
tmp varchar2(20);
begin
select city into tmp
from hr.locations
where location_id = inp;
return tmp;
end;
/
2.
SELECT-LIST 에 사용하는 경우
<FUNCTION 의 SCALAR SUB QUERY 화>
SELECT-LIST 에 사용하는 경우 SCALAR SUB QUERY 로 만들면 유리할 수 있다. 여기서 SCALAR SUB QUERY 라 함은 (SELECT FUNCTION(COLUMN) FROM DUAL) 형태로
SELECT-LIST 에 묶는걸 말한다. 이에 대한 자세한 내용은 아래의 링크를 참조하라.
http://h391106.tistory.com/159
위의 예시와 상관없이 다시 이야기하겠다. (ㅋ)
아래와 같이 쿼리를 수행을 하고 그 트레이스를 보았다.
select first_name,dname(department_id)
department_name
from hr.employees
/
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
9 0.02 0.02 0 17 0 107
------- ------ -------- ---------- ---------- ----------
---------- ----------
total
11 0.02 0.02 0
17 0 107
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows
Row Source Operation
-------
---------------------------------------------------
107 TABLE ACCESS FULL EMPLOYEES
(cr=15 pr=0 pw=0 time=713 us)
SELECT DEPARTMENT_NAME
FROM
HR.DEPARTMENTS WHERE DEPARTMENT_ID = :B1
call
count cpu elapsed disk
query current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse
1 0.00 0.00 0 0 0 0
Execute
107 0.06
0.06 0 0 0 0
Fetch
107 0.00 0.00 0 212 0 106
------- ------ -------- ---------- ---------- ----------
---------- ----------
total
215 0.06 0.06 0 212 0 106
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
위의 결과를 보면 상단은 메인쿼리에 대한 정보이며
하단부는 FUNCTION 에 대한 정보이다. FUNCTION 에
대한 내용을 보면 EXECUTE 가 107 번이고 ROWS (결과) 가 106 인
것을 알 수 있다. 이것이 의미하는 바는 EMPLOYEES 의
총 ROWS 만큼 FUNCTION 을 수행했다는 것을 의미하며
그 결과 중에 하나가 비는 것은 입력 EMPLOYEES 의DEPARTMENT_ID
에 NULL 값이 있기 대문이다.
위의 쿼리에서 개선할 수 있는 부분은 앞에 이야기
한 것과 갘이 FUNCTION 의 SCALAR SUB QUERY 화이다.
select first_name,(select dname(department_id)
from dual) department_name
from hr.employees
/
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
9 0.00 0.00 0 15 0 107
------- ------ -------- ---------- ---------- ----------
---------- ----------
total
11 0.00 0.00 0 15 0 107
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows
Row Source Operation
-------
---------------------------------------------------
12 FAST DUAL
(cr=0 pr=0 pw=0 time=130 us)
107 TABLE ACCESS FULL EMPLOYEES
(cr=15 pr=0 pw=0 time=951 us)
SELECT
DEPARTMENT_NAME
FROM
HR.DEPARTMENTS WHERE DEPARTMENT_ID = :B1
call count
cpu elapsed disk
query current rows
-------
------ -------- ---------- ----------
---------- ---------- ----------
Parse 1
0.00 0.00 0 0 0 0
Execute 12
0.00 0.00 0 0 0 0
Fetch 12
0.00 0.00 0 22 0 11
-------
------ -------- ---------- ----------
---------- ---------- ----------
total 25
0.00 0.00 0 22 0 11
위에서 유념해 볼 부분은 메인쿼리의 FAST DUAL OPERATION 부분과 FUNCTION 의 STAT 부분이다.
메인쿼리 부분을 보면 FAST DUAL 에 12 건이란 항목이 있다. 이는 단도직입적으로 말해 FUNCTION 의 횟수를 의미한다. 아래의 FUNCTION STAT 을 보면 EXECUTE 가 12 건임을 알 수 있다. 또 ROWS 가 11 인
것은 아까 이야기 한 것과 동일하게 NULL 입력이 있기 때문이다.
결과적으로
FUNCTION 을 107 건이 아니라 12건
수행했다는 것을 의미한다. 이는 성능상 장점이 있다는 것을 의미한다.
그렇다면 이 12건은 어디서 온 값인지 확인할 필요가 있다.
다음의 쿼리를 수행해 각각의 ROW 를 확인해 본다.
SQL> select
count(distinct nvl(department_id,0)) from employees;
COUNT(DISTINCTNVL(DEPARTMENT_ID,0))
-----------------------------------
12
이 쿼리가 의미하는 바는 NULL 을 포함해 DEPARTMENT_ID 입력에 대해 몇가지 타입의
결과가 있는지를 의미한다. 다시말해 한번 입력에 대해 결과를 얻으면 같은 입력에 대해 결과를 재이용한다는
것을 의미한다. 그렇기 때문에 ROWS 에 비해 DISTINCT KEY 가 작을 수록 유리해진다.
3.
FUNCTION 의
위치조정 (WHERE -> SELECT-LIST)
select e.first_name,d.department_name
from hr.employees e,hr.departments d
where e.department_id = d.department_id
and lname(d.location_id) = 'Oxford'
and e.salary > 10000
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 0.01 0.01 0 24 0 16
------- ------ -------- ---------- ---------- ----------
---------- ----------
total
8 0.01 0.01 0 24 0 16
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
-------
---------------------------------------------------
8
TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=66 pr=0 pw=0 time=11917 us)
36
NESTED LOOPS (cr=64 pr=0 pw=0
time=388814 us)
1
TABLE ACCESS FULL DEPARTMENTS (cr=62 pr=0 pw=0 time=20961 us)
34
INDEX RANGE SCAN EMP_DEPARTMENT_IX (cr=2 pr=0 pw=0 time=395 us)(object
id 10254)
*DEPARTMENTS.DEPARTMENT_ID 와 EMPLOYEES.EMPLOYEE_ID 는 1:N 관계를 가지고 있다. 위
TRACE ROWS 를 보면 NL JOIN 의 결과로
36 이 나왔는데 이는 잘못된 값이다. DEPARTMENTS 의 1행이란 결과에 대해 N 집합과의 조인결과는 N 이 나오거나 이보다 작아야 한다. 다시말해 ROWS 가 증가할 수는 없다.*
SELECT CITY
FROM
HR.LOCATIONS WHERE LOCATION_ID = :B1
call
count cpu elapsed disk
query current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse
1 0.00 0.00 0 0 0 0
Execute
54 0.01
0.01 0 0 0 0
Fetch
54 0.00 0.00 0 108 0 54
------- ------ -------- ---------- ---------- ----------
---------- ----------
total
109 0.02 0.02 0 108 0 54
트레이스 내용을 보면 DEPARTMENTS 를 DRIVING 으로 FULL ACCESS 하면서 각각의 입력 ROW 에 대해 EMPLOYEES 와 연결시도(EMP_DEPARTMENT_IX) 를 해 그 결과로 34(36은 잘못된
값이다.) 을 얻는다. 34란 값은 아래의 쿼리 수행으로
얻을 수 있다.
select e.first_name,d.department_name
from hr.employees e,hr.departments d
where e.department_id = d.department_id
and lname(d.location_id) = 'Oxford'
/
이것이 의미하는 바는 34건에 대해 EMPLOYEES.SALARY > 10000 을 체크조건으로 26건이
필터링되고 8건이 결과로 남았다는 것이다.
그렇다면 정작 중요한 FUNCTION 의 실행횟수 54 건이 의미하는 것은 어떤것일까?
select d.department_name
from hr.departments d
where lname(d.location_id) = 'Oxford'
call
count cpu elapsed disk
query current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse
1 0.04 0.04 0 0 0
0
Execute
1 0.00 0.00 0 0 0 0
Fetch
2 0.00 0.00 0 8 0 1
------- ------ -------- ---------- ---------- ----------
---------- ----------
total
4 0.04 0.04 0 8 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows
Row Source Operation
-------
---------------------------------------------------
1 TABLE ACCESS FULL DEPARTMENTS
(cr=62 pr=0 pw=0 time=1860 us)
위 쿼리를 수행하면 본래 쿼리 OPERATION 의 DEPARTMENTS 수행 결과와 동일한 건수가 나온다.
SELECT CITY
FROM
HR.LOCATIONS WHERE LOCATION_ID = :B1
call
count cpu elapsed disk
query current rows
------- ------ -------- ---------- ---------- ----------
---------- ----------
Parse
0 0.00 0.00 0 0 0 0
Execute
27 0.00
0.00 0 0 0 0
Fetch
27 0.00 0.00 0 54 0 27
------- ------ -------- ---------- ---------- ----------
---------- ----------
total
54 0.00 0.00 0 54 0 27
위 부분은 앞서 본 쿼리가 호출하는 FUNCTION 에 대한 STAT 이다. EXECUTE 를보면 27임을 볼수 있다. 그런데
ORIGINAL QUERY 의 FUNCTION 의
EXECUTE 를 보면 54 로 되어있다. /*+ 이
부분은 이해가 잘 안가는 부분이다. */ 덧붙여 이야기 하자면
DEPARTMENTS 가 분명 DRIVING TABLE 이고 여기서 LNAME FUNCTION 을 DEPARTMENTS 의 ROWS 수 만큼 수행해 그 결과가 Oxford 인것 만 남기고 버린다.
잡설이 길어졌는데 결과적으로 LNAME 은 DEPARTMENTS 의 ROWS 수 만큼 호출되었다는 것을 의미한다.
select e.first_name,d.department_name
from hr.employees e,hr.departments d
where e.department_id = d.department_id
and e.salary > 10000
/
위의 쿼리를 수행하면 15건이 나온다. 이것이 의미하는 것은 FUNCTION 을 이 결과에 대해 수행을
하면 15 -> 8 건을 얻는데 15회의 FUNCTION 수행으로 가능하다는 것이다. 이는 27회 수행에 비해 12회의 이득을 얻을 수 있다.
select first_name,department_name
from
(select rownum,e.first_name,d.department_name,lname(d.location_id)
lname
from hr.employees e,hr.departments d
where e.department_id = d.department_id
and e.salary > 10000)
where lname = 'Oxford'
/
위와 같이 쿼리를 변경하면 INLINE VIEW 의 결과 15건에 대해 FUNCTION 을 수행하게 된다. ROWNUM 은 조건이 INLINE VIEW 로 파고드는 걸 막기
위해 ROWNUM 을 사용했다.