Hi Ming,
When I try this query I get this plan
create table t1 (a1 int not null, a2 int not null);
create table t2 (a1 int not null, a2 int not null);
cqd subquery_unnesting 'debug' ;
explain options 'f' select * from t1 where not exists (select * from t2
where t2.a2 = t1.a1 );
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- --------------------
---------
3 . 4 root
1.00E+002
2 1 3 hybrid_hash_anti_sem
1.00E+002
. . 2 trafodion_scan T1
1.00E+002
. . 1 trafodion_scan T2
1.00E+002
--- SQL operation complete.
There is no nested join in the plan, so whatever correlation was there
initially has been removed. As you said, this decorrelation did NOT occur
in the SQO phase and is therefore not affected by the subquery_unnesting
cqd. The decorrelation happened in Subquery::transformToRelExpr() in
NormItemExpr.cpp. The lines to look at are
// -----------------------------------------------------------------
// Allocate a new semijoin or anti semijoin
// -----------------------------------------------------------------
OperatorTypeEnum joinOp = REL_SEMITSJ;
if (isAnAllSubquery() || isNotExists())
joinOp = REL_ANTI_SEMITSJ;
You may have seen this already by debugging.
Thank you
Suresh
On Mon, Jan 29, 2018 at 9:09 AM, Ming Liu <[email protected]> wrote:
> Thanks Dave and Suresh,
>
> I was trying to understand this debug skill and try to make it a tool that
> can engineers can use in the field to check query performance issues
> related to subquery unnestting. I think in most cases Trafodion does very
> well to unnest subqueries. I am trying to find out those scenarios that it
> cannot do unnesting. And hope this tool can help.
>
> I tried this query:
>
> explain options 'f' select * from t1 where not exists (select * from t2
> where t2.a2 = t1.a1 );
>
> From some reference document, it was said EXISTS after a NOT, OR will not
> be unnest, but I didn't see that behavior. Maybe Trafodion can already
> optimize these subqueries.
>
> The subquery I tested is correlated, but it doesn't show any warning. I
> will try to use gdb to do some debugging asap.
>
> Ming
>
