You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@trafodion.apache.org by "Liu, Yuan (Yuan)" <yu...@esgyn.cn> on 2018/07/25 12:28:55 UTC

Do we have hint as a replacement of cqd nested_joins off?

Hi Trafodioneers,

Sometimes we find the query plan is not correct, e.g the query should use hash join but it chooses nested_joins.

Then we have to use one more statement "cqd nested_joins 'off'". However, sometimes it is not easy to add one more statement in application side.

So I am just asking that do we have a hint for the cqd? Like below,

Select <<+nested_joins 'off'>> ...


Best regards
Yuan

RE: Do we have hint as a replacement of cqd nested_joins off?

Posted by "Liu, Yuan (Yuan)" <yu...@esgyn.cn>.

Thanks, Hans.



Best regards
Yuan

-----Original Message-----
From: Hans Zeller <ha...@esgyn.com> 
Sent: Thursday, July 26, 2018 12:59 AM
To: dev@trafodion.apache.org
Subject: RE: Do we have hint as a replacement of cqd nested_joins off?

Hi Yuan,

No, unfortunately there are no join hints in Trafodion. Maybe you can try cardinality hints to influence the optimizer to choose a hash join:

select * from t1 <<+ cardinality 1e9>> join t2 <<+ cardinality 1e6>> on t1.a=t2.b;

Hans

-----Original Message-----
From: Liu, Yuan (Yuan) <yu...@esgyn.cn> 
Sent: Wednesday, July 25, 2018 5:29 AM
To: dev@trafodion.apache.org
Subject: Do we have hint as a replacement of cqd nested_joins off?

Hi Trafodioneers,

Sometimes we find the query plan is not correct, e.g the query should use hash join but it chooses nested_joins.

Then we have to use one more statement "cqd nested_joins 'off'". However, sometimes it is not easy to add one more statement in application side.

So I am just asking that do we have a hint for the cqd? Like below,

Select <<+nested_joins 'off'>> ...


Best regards
Yuan

RE: Do we have hint as a replacement of cqd nested_joins off?

Posted by Hans Zeller <ha...@esgyn.com>.

Hi Yuan,

No, unfortunately there are no join hints in Trafodion. Maybe you can try cardinality hints to influence the optimizer to choose a hash join:

select * from t1 <<+ cardinality 1e9>> join t2 <<+ cardinality 1e6>> on t1.a=t2.b;

Hans

-----Original Message-----
From: Liu, Yuan (Yuan) <yu...@esgyn.cn> 
Sent: Wednesday, July 25, 2018 5:29 AM
To: dev@trafodion.apache.org
Subject: Do we have hint as a replacement of cqd nested_joins off?

Hi Trafodioneers,

Sometimes we find the query plan is not correct, e.g the query should use hash join but it chooses nested_joins.

Then we have to use one more statement "cqd nested_joins 'off'". However, sometimes it is not easy to add one more statement in application side.

So I am just asking that do we have a hint for the cqd? Like below,

Select <<+nested_joins 'off'>> ...


Best regards
Yuan