Skip to main content

Search

Welcome to Tibero GTS!

Issue with IN → EXISTS Conversion Not Possible Despite GROUP Condition

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)
 
Note
It can also be resolved by workarounds using window functions. 
 
 
 

Related to