Document Type | Troubleshooting
Field | Tuning
Applicable Product Version | 6FS07
Document Number | TTUTS002
Issue
During the upgrade from Tibero5 to Tibero6, the query execution time becomes long due to an error in the in-subquery check logic in subqueries. At this time, even though there is a GROUP condition, the IN → EXISTS conversion does not occur properly, causing an issue.
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(improvement MAIN) /
Cause
In subqueries, only the first encountered GRP node checks the GROUP condition.
Because of this, even if there are lower GRP nodes that do not affect the boundary (i.e., do not affect the result), the condition check is not performed on those nodes, causing the problem.
IN (select max(c1) from (select distinct c1 from t))
Solutions
Change to check the group condition of lower GRP nodes as long as they do not affect the boundary.
Before Patch (No UNNEST)
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) -- Not UNNESTed
5 - access: ("A"."C2" = "B"."C2") (0.000)
7 - filter: ("A"."C1" <> 0) (1.000)
After Patch (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)
NoteIt can also be resolved by workarounds using window functions.