+1
Answered

Snowflake rely contraint property

roman gyger 7 months ago updated 6 months ago 3

Hi,

It looks like contraints properties for Snowflake are not supported yet, which makes sense as Snowflake does not enforce them apart from the property RELY, which helps the snowflake optimizer to enahnce query performance:

----------------------------

RELY | NORELY

Specifies whether a constraint in NOVALIDATE mode is taken into account during query rewrite.

By default, this constraint property is set to NORELY.

If you have ensured that the data in the table does comply with the constraints, you can change this to RELY to indicate that the query optimizer should expect the data in the table to adhere to the constraints. Setting this can improve query performance (e.g. by eliminating unnecessary joins).

For primary and foreign key constraints, set this property on both the primary key constraint and the foreign key constraint. For example:

ALTER TABLE table_with_primary_key ALTER CONSTRAINT a_primary_key_constraint RELY;
ALTER TABLE table_with_foreign_key ALTER CONSTRAINT a_foreign_key_constraint RELY;

https://docs.snowflake.com/en/sql-reference/constraints-properties

-------------------------------

We use this option, but doesn't seems to be supported within SqlDBM yet. Is there a way to have it generated or does it need actually added manually the the scripts? If it is not supported yet, I would be more than happy if it would be supported in the near future as it is an important Snowflake query performance feature.


Thanks for your support.

Best regards

Roman

GOOD, I'M SATISFIED
Satisfaction mark by roman gyger 6 months ago
Under review

Hi Roman, 

You are correct; we do not currently support RELY constraints. It is on our roadmap, though I do not have an answer of the timeline of that. We are working on releasing tags next for Snowflake :) 

As for a workaround, I will gather an answer from the team and get back to you once I have that. Thanks!

Answered

Hi Roman, Thanks for your patience. We did release our RELY functionality late last week. Thanks again for your patience and support. 

Hi Hanna,

I did see the new functionality last week already. Thanks a lot for your prompt support.

Best regards,

Roman