Open dynamobi-build opened 12 years ago
[author="jhyde", created="Tue, 17 Oct 2006 19:12:59 -0500 (GMT-05:00)"]
I agree.
Another dimension to consider is "WITH RECURSIVE", the SQL standard way of defining transitive closure, among other things. I've not given it much thought, but view expansion is certain to get interesting, so it might affect the order of operations described above.
[author="jvs", created="Mon, 30 Oct 2006 12:15:23 -0500 (GMT-05:00)"]
In eigenchange 8026, Rushan got us most of the way there (everything except separating view expansion from flattening). It turned out that doing flattening before decorrelation was required in order to expose rel metadata to decorrelation (allowing us to omit the value generators altogether in some cases).
Note: flattening necessarily includes conversion of logical TableAccessRel to physical table rel (since physical tables contain flattened output rows), and we can't provide the metadata until that's been done since physical storage plugins are an important part of the metadata chain.
So the new sequence is:
Sql2Rel (without view expansion)
(EXPLAIN PLAN WITHOUT IMPLEMENTATION stops here to preserve the current behavior, including reverting back to showing CorrelatorRels)
flattening (including view expansion)
full-tree decorrelation
planner
Last step will be pulling view expansion out into its own phase (independent of flattening) so that we can show the view expansion as part of EXPLAIN PLAN WITHOUT IMPLEMENTATION. This may cause a bunch of plan diffs so we should coordinate on it.
And then we can start thinking about WITH RECURSIVE :) And WITH named inline views in general.
[reporter="jvs", created="Tue, 17 Oct 2006 18:33:09 -0500 (GMT-05:00)"] Currently we perform these two activities together, which is not incorrect, but it is confusing, and makes tracing harder. It also makes EXPLAIN PLAN WITHOUT IMPLEMENTATION show the unexpanded tree, but it would be more useful to see the logical rels for the view expansions too.
Plus, now with decorrelation mixed in, we're decorrelating each view as it gets expanded; it would probably be better to do:
Sql2Rel together with full view expansion until all done
full-tree decorrelation
full-tree flattening
planner