문서유형ㅣ장애해결
분야ㅣ 튜닝
적용제품버전ㅣ 6FS07
문서번호ㅣ TTUTS002
현상
Tibero5에서 Tibero6 업그레이드 중 서브쿼리에서 in서브쿼리 체크로직 오류로 쿼리 수행시간이 오래걸리게 됩니다. 이때 GROUP 조건이 있음에도 IN → EXISTS 변환이 제대로 이루어지지 않는 문제가 발생하였습니다.
drop table t;
create table t
(pin varchar(20),
psdt_ls_idno number);
insert into tibero.t
select 'test'||level,level from dual connect by level <=10000;
commit;
create unique index pk_t on t(pin);
alter table t add constraint pk_t primary key(pin);
create index ix5_t on t(psdt_ls_idno,pin);
drop table t2;
create table t2(c1 number, c2 number);
insert into t2 values(1,1);
insert into t2 values(1,1);
insert into t2 values(10,10);
insert into t2 values(10,10);
insert into t2 values(20,20);
insert into t2 values(20,20);
insert into t2 values(5,5);
insert into t2 values(5,5);
insert into t2 values(30,50);
insert into t2 values(30,50);
commit;
drop table t3;
create table t3(c1 number, c2 number);
insert into t3 (select level, level from dual connect by level <= 10000);
commit;
set autot on;
-- SELECT
select a.c1
from t3 a
where c1 in (select max(c11) from (select distinct b.c1 c11 from t3 a, t2 b where a.c2=b.c2 and a.c1 != 0) ) -- unnest(개선 MAIN)
/
원인
서브쿼리에서는 첫 번째로 만나는 GRP 노드에 대해서만 GROUP 조건을 체크합니다.
이로 인해, boundary에 영향을 받지 않는 하위 GRP 노드가 존재하더라도(즉, 결과값에는 영향을 주지 않더라도) 해당 노드에 대해서는 조건 체크가 수행되지 않아 문제가 발생하게 됩니다.
IN (select max(c1) from (select distinct c1 from t))
해결
boundary 영향을 받지 않는 선에서 하위 GRP노드의 group조건을 체크하도록 변경합니다.
패치 전 (UNNEST X)
C1
----------
30
1 row selected.
Execution Plan
--------------------------------------------------------------------------------
1 TABLE ACCESS (FULL): T3 (Cost:40, %%CPU:2, Rows:1)
3 SORT AGGR (Cost:26, %%CPU:0, Rows:1)
4 DISTINCT (HASH) (Cost:26, %%CPU:0, Rows:5)
5 HASH JOIN (Cost:26, %%CPU:0, Rows:30)
6 TABLE ACCESS (FULL): T2 (Cost:12, %%CPU:0, Rows:20)
7 TABLE ACCESS (FULL): T3 (Cost:13, %%CPU:0, Rows:12452)
Predicate Information
--------------------------------------------------------------------------------
1 - filter: ("A"."C1" = (SELECT MAX(C11) FROM (SELECT DISTINCT B.C1 C11 FROM
T3 A, T2 B WHERE A.C2=B.C2 AND A.C1 != 0) )) (0.000) -- UNNEST 되지 않음
5 - access: ("A"."C2" = "B"."C2") (0.000)
7 - filter: ("A"."C1" <> 0) (1.000)
패치 후 (UNNEST)
C1
----------
30
1 row selected.
Execution Plan
--------------------------------------------------------------------------------
1 HASH JOIN (REVERSE SEMI) (Cost:39, %%CPU:0, Rows:1)
2 SORT AGGR (Cost:26, %%CPU:0, Rows:1)
3 DISTINCT (HASH) (Cost:26, %%CPU:0, Rows:5)
4 HASH JOIN (Cost:26, %%CPU:0, Rows:30)
5 TABLE ACCESS (FULL): T2 (Cost:12, %%CPU:0, Rows:20)
6 TABLE ACCESS (FULL): T3 (Cost:13, %%CPU:0, Rows:12452)
7 TABLE ACCESS (FULL): T3 (Cost:13, %%CPU:0, Rows:12454)
Predicate Information
--------------------------------------------------------------------------------
1 - access: ("A"."C1" = MAX("B"."C1")) (0.000) -- UNNEST
4 - access: ("A"."C2" = "B"."C2") (0.000)
6 - filter: ("A"."C1" <> 0) (1.000)
참고윈도우 함수로도 우회하여 해결 가능합니다.