Starting a discussion to gather information related to the modeling activity and how it impacts Teiid runtime. At some point, this will transcribed into a document for Designer.
What does impact runtime:
- Pk/fk relationships are meaningful to optimization. Starting in Teiid 7.3 we use the key relationship information to determine key preservation, which is used in update procedure logic, subquery optimization, etc
- Virtual pk/fk relationships are useful for subquery optimization and inherently updatable views. Other optimization paths will typically not consider virtual keys because the views layers can be removed.
- Indexes/unique constraints are also used during optimization in costing dependent joins and cardinality determination
- Cross source integrity could be enforced through Teiid by using updated procedures to check for necessary preconditions before an insert/update/delete is allowed to happen. However unless all writers to both sources were actively maintaining integrity then additional compensation would be needed.
- From a data-write enforcement and query planning perspective, teiid is looking for the appropriate equi-join predicates to be present.
- Defining data types
- In general only the run-time type is meaningful in terms of how data is returned/transformed. Run-time types are the Teiid built-in types, such as boolean, byte, integer, etc.
- Design-time types are used in two limited capacities at run-time. They can be reported through the system schema and for output conversion when querying XML document models. Designer allows for a broader set of types, known as design-time types, to be selected. Design-time types include the built-in types, standard xsd types - like g:dateTime, and even types defined by imported xsds. Any design-time type will be mapped to a sufficiently expressive run-time type. Any design-time type facet information not reflected by the run-type, such a restriction to non-negative, will not have any run-time enforcement.
- Nullability information is meaningful when determining allowable values for virtual procedure input parameters, default insert values, and costing.
What does not impact runtime, their only use is for metadata information:
- PF/FK's directly between sources are not currently supported for optimization.
- There is no runtime checking of the pk/fk data constraints. For keys within the same source it's considered the source systems responsibility to maintain integrity.
- Teiid does not currently look at the cardinalities (1-1, 1-many, many-1) of the relationship, just whether the pk to fk has been satisfied
- Teiid does not enforce constraints (nullable, unique, pk/fk, etc.) on source tables/procedures, but rather rely on the source system.