ORACLE ELIMINATE OR CLAUSE
from
1. ORACLE ELIMINATE OR CLAUSE
OR 연산은 플랜에 악영향을 끼치는 경우가 더러 있다. 그 중 대표적인 경우로 INDEX 가 있음에도 불구하고 FULL SCAN 을 하게 되는 경우가 있다. 부분범위 처리 (NESTED LOOP JOIN) 을 하면 더 적은 블록으로 원하는 결과 달성이 가능한데 말이다.
또 다른 경우로 filter 처리를 하게 되는 경우가 있는데 Driving 건수가 많아 비효율적으로 풀리는 경우도 있다.
2. 예제
select first_name,last_name,salary,JOB_ID
from employees
where salary > 7000
and ( job_id = :b0
or job_id in (select job_id from jobs
where job_id = :b0))
위와 같은 쿼리가 있다고 하자. 딱봐도 이상한 쿼리이지만 실제로 사용되고 있다. ;;
바인드에 대해 값이 들어갈때와 들어가지 않을 때로 나눠 생각하자. 값이 들어가지 않았을 때(NULL) 위 결과는 0건이다. 과정에 대한 이야기는 따로 언급하지 않겠다. (본인이 확실하게 이야기 할 수 없는 부분이기도 하다.)
값이 들어갔을 때 이상한 점은 IN SUBQUERY 부분이다. IN 이라는 것이 값의 유무를 체크하는 것인데 이 부분이 OR 로 중복된다.
이렇게 해야 하는 상황이라는 걸 상상해 보면 EMPLOYEES 에 어떤 사람이 JOB_ID 를 가지고 있는데 JOBS 에는 JOB_ID 가 없는 경우이다.
도식화 해서 이야기 하면 A OR B 가 있을 때 위의 이야기는 A = TRUE, B = FALSE 란 이야기다.
반대로 A = FALSE, B = TRUE 를 생각해 보면 EMPLOYEES 에 조건 JOB_ID 에 해당하는 사람이 존재하지 않지만 해당 JOB_ID 의 결과가 JOBS 에 있다는 이야기이다. 하지만 이는 메인쿼리의 EMPLOYEES 자체에 이미 만족하는 ROWS 가 없기 때문에 서브쿼리의 체크는 무의미하다.
결과적으로 위 조건이 유효한 상황이라는 것이 EMPLOYEES 가 JOB_ID 에 없는 값을 가지고 있는 경우란 이야기이다.
하지만 일반적으로 참조무결성 관계를 가질 때 이는 있을 수 없는 일이다.
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 3 0.00 0.00 0 17 0 22
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 17 0 22
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 26
Rows Row Source Operation
------- ---------------------------------------------------
22 FILTER (cr=17 pr=0 pw=0 time=226 us)
44 TABLE ACCESS FULL EMPLOYEES (cr=17 pr=0 pw=0 time=314 us)
0 FILTER (cr=0 pr=0 pw=0 time=109 us)
0 INDEX UNIQUE SCAN JOB_ID_PK (cr=0 pr=0 pw=0 time=0 us)(object id 9970)
플랜을 보면 위와 같이 나온다. PREDICATE 에 대한 추가 정보가 없어 정확한 해석이 어렵다. 그래서 AUTOTRACE 로 다시 살펴보았다.
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 224 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | EMPLOYEES | 83 | 2324 | 4 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX UNIQUE SCAN| JOB_ID_PK | 1 | 8 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"=:B0 OR EXISTS (SELECT /*+ */ 0 FROM "JOBS"
"JOBS" WHERE :B1=:B0 AND "JOB_ID"=:B2))
2 - filter("SALARY">7000)
3 - filter(:B1=:B0)
4 - access("JOB_ID"=:B1)
앞서 본 트레이스의 TABLE ACCESS EMPLOYEES (44건) 은 SALARY > 7000 에 대한 결과물이다. 다시 아래의 AUTOTRACE 를 가지고 이야기 하자면
1. EMPLOYEES 에서 첫 ROW 를 확인
2. 해당 ROW 의 SALARY > 7000 을 확인
3. 해당 ROW 의 JOB_ID 가 JOBS.JOB_ID 에 있는지 확인 (EXISTS)
4. 해당 ROW 의 JOB_ID 가 EMPLOYEES.JOB_ID 에 있는지 확인
5. * 3, 4 에서 하나라도 만족하면 이 ROW 는 운반단위로 보내진다.
6. EMPLOYEES 의 두번째 ROW ~ 끝까지 2~5 를 반복
이러한 과정을 생각했을 때 filter operation 의 특성상 비효율로 판단되는 경우가 있다. 그것은 Driving Table 의 rows 수가 커지는 경우가 있을 것이다.
Driving Table 의 결과 row 수가 많아진다는 것은 inner table 과의 연결 시도 횟수 (싱글블록 I/O) 가 많아진다는 것을 의미하며 이는 성능에 악영향을 끼친다.
이야기를 돌려 쿼리 자체를 보고 수정을 하도록 하자. 앞서 말한 랜덤엑세스를 줄이기 위해서는 데이터의 검증과정 (조인) 을 바꿀 필요가 있다.
이를 테면 hash join 이 있을것이다. 각 조인 멤버들이 스스로의 범위를 충분히 줄여줄 조건이 있다면 랜덤엑세스도 최소화 하고 더 좋은 처리속도를 얻을 수 있을 것이다.
select first_name,last_name,salary,JOB_ID
from employees
where salary > 7000
and ( job_id = :b0
or job_id in (select job_id from jobs
where job_id = :b0))
select first_name,last_name,salary,JOB_ID
from employees
where salary > 7000
and job_id in (
select :b0 from dual
union all
select job_id from jobs1
where job_id = :b0)
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 315 | 8 (13)| 00:00:01 |
|* 1 | HASH JOIN | | 9 | 315 | 8 (13)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 2 | 14 | 3 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 2 | 7 | 3 (34)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN| JOBS1_JOB_ID_IX | 1 | 7 | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | EMPLOYEES | 83 | 2324 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
위와 같이 쿼리를 변경해 다른 조인으로 풀었다. 지금 현재의 데이터로는 (107건) 사실 이것이 효율적이라고 말할 수는 없다. 그 이유는 VIEW 를 사용한데다 HASH JOIN 이라는 OPERATION 자체가 NL JOIN 보다 무겁기 때문이다. 위 OPERATION 이 효율적인 상황은 데이터가 많고 아까 이야기한 랜덤엑세스가 많이 일어나는 상황(DRIVING TABLE 건수가 많을 때) 이다.
위 플랜을 보면 JOB_ID 를 이용해 EMPLOYEES 건수를 걸러내면 안되나 생각할 수도 있다. 물론 그렇게 진행해도 상관없다. 이는 HASH JOIN 이 효율적인 상황을 상정하면 당연하다. 다만 해당 조건을 이용해 INDEX SCAN 을 할지는 알 수 없다. (아마도 이상황에서는 FULL SCAN 할 것이다.) 그 이유는 INDEX 를 이용해 JOB_ID 를 걸러내기에는 전체 ROWS 에 비해 NDV(NUMBER OF DISTINCT VALUE) 가 작기 때문이다.