[ https://issues.apache.org/jira/browse/IMPALA-3531?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16787164#comment-16787164 ]
Greg Rahn commented on IMPALA-3531: ----------------------------------- A few comments on grammar specifics: The ANSI/ISO SQL spec specifically covers deferrable and optionally enforced constraints using the following grammar which should be supported as part of this effort: {noformat} <constraint name definition> ::= CONSTRAINT <constraint name> <constraint characteristics> ::= <constraint check time> [ [ NOT ] DEFERRABLE ] [ <constraint enforcement> ] | [ NOT ] DEFERRABLE [ <constraint check time> ] [ <constraint enforcement> ] | <constraint enforcement> <constraint check time> ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE <constraint enforcement> ::= [ NOT ] ENFORCED {noformat} Adding support for Oracle's grammar makes sense for compatibility reasons: {noformat} ENABLE | DISABLE Specifies whether the constraint is enabled or disabled. VALIDATE | NOVALIDATE Specifies whether to validate existing data in the table when a constraint is created. Only used in conjunction with the ENABLE | DISABLE property. RELY | NORELY Specifies whether a constraint in NOVALIDATE mode is taken into account for query optimizations. {noformat} In addition to grammar, I'll also highlight information_schema.table_constraints uses the following columns to show the features: - is_deferrable - YES if the constraint is deferrable, NO if not - initially_deferred - YES if the constraint is deferrable and initially deferred, NO if not - enforced - YES if the constraint is enforced, NO if not See also: - https://www.postgresql.org/docs/11/infoschema-table-constraints.html > Implement FK/PK "rely novalidate" constraints for better CBO > ------------------------------------------------------------ > > Key: IMPALA-3531 > URL: https://issues.apache.org/jira/browse/IMPALA-3531 > Project: IMPALA > Issue Type: New Feature > Components: Catalog, Frontend, Perf Investigation > Affects Versions: Impala 2.5.0, Impala 2.6.0 > Environment: CDH > Reporter: Ruslan Dautkhanov > Assignee: Anurag Mantripragada > Priority: Minor > Labels: CBO, performance, ramp-up > > 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 > IMPALA-2929. > https://docs.oracle.com/database/121/DWHSG/schemas.htm#DWHSG9053 > "Overview of Constraint States": > - Enforcement > - Validation > - Belief > So FK/PK with "rely novalidate" will have Enforcement&Validate disabled but > Belief = RELY as it is possible to do in Oracle and now in Hive (HIVE-13076). > It opens a lot of ways to do additional ways to optimize execution plans. > As exxplined in Tom Kyte's "Metadata matters" > http://www.peoug.org/wp-content/uploads/2009/12/MetadataMatters_PEOUG_Day2009_TKyte.pdf > pp.30 - "Tell us how the tables relate and we can remove them from the > plan...". > pp.35 - "Tell us how the tables relate and we have more access paths > available...". > Also it might be helpful when Impala is being integrated with Kudu as the > latter have to have a PK. -- This message was sent by Atlassian JIRA (v7.6.3#76005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-all-unsubscr...@impala.apache.org For additional commands, e-mail: issues-all-h...@impala.apache.org