[HIVE-13076] Implement FK/PK "rely novalidate" constraints for better CBO

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Implemented
• Component/s: CBO, Logical Optimizer
• Assignee: Hari Sankar Sivarama Subramaniyan Assign to me
• Reporter: Ruslan Dautkhanov
• Created: 17/Feb/16 17:35
• Updated: 22/May/17 17:24
• Resolved: 11/May/16 20:35

Description

Oracle has "RELY NOVALIDATE" option for constraints.. Could be easier for Hive to start with something like that for PK/FK constraints. So CBO has more information for optimizations. It does not have to actually check if that constraint is relationship is true; it can just "rely" on that constraint..

https://docs.oracle.com/database/121/SQLRF/clauses002.htm#sthref2289

So it would be helpful with join cardinality estimates, and with cases like this - https://issues.apache.org/jira/browse/HIVE-13019

Issue Links

• Dependent: SPARK-17076 Cardinality estimation of join operator
• is related to: HIVE-6905 Implement Auto increment, primary-foreign Key, not null constraints and default value in Hive Table columns
• is related to: SPARK-15302 Implement FK/PK "rely novalidate" constraints for better CBO
• is related to: HIVE-16575 Support for 'UNIQUE' and 'NOT NULL' constraints
• is related to: HIVE-13019 Optimizer COLLECT_LIST/COLLECT_SET
• is related to: HIVE-15342 Add support for primary/foreign keys in HBase metastore
• links to: Impala-side feature to implement the same

Sub-Tasks

• Support primary keys/foreign keys constraint as part of create table command in Hive: Closed
• Metastore Changes : API calls for retrieving primary keys and foreign keys information: Closed
• HS2 changes : API calls for retrieving primary keys and foreign keys information: Closed
• Support drop Primary Key/Foreign Key constraints: Closed
• Support Alter commands for Rely/NoRely novalidate for PK/FK constraints: Closed