Our UI has a notion of a "focal object" - It's the set of information that the user is primarily interested in. The focal object is represented to Teiid as a table or an inline view. The UI shows the first n rows of the focal object and rows of related objects. In a lot of cases, the related objects are sparsely populated and the desire is to show default values for the missing related data. I think of this as a hierarchical join between the focal object and everything else.
I can think of a few ways to express the hierarchical idea in Teiid SQL but the most straightforward is to outer join the focal object with the related using the optional hint. Something like this:
SELECT * FROM focal_object LEFT OUTER JOIN /*+ optional */ related_object ON focal_object.a=related_object.a LIMIT 10
This produces the desired execution plan by pushing the LIMIT clause to focal_object source and then joining that result to related_object. This technique doesn't work, however when an ORDER BY is introduced:
SELECT * FROM focal_object LEFT OUTER JOIN /*+ optional */ related_object ON focal_object.a=related_object.a ORDER BY focal_object.c LIMIT 10
In this case, Teiid pushes an ORDER BY a to both focal_object and related_object (presumably to execute an efficient join) and then performs the order by focal_object.c itself and finally executing the limit clause. Wouldn't it be more efficient to push down order by focal_object.c limit 10 to focal_object source and then join those results to related_object? The results may not be right but "right" takes on a looser definition with the optional hint. I have tried making focal_object a dependent join but that doesn't affect the push down in the desired way, either.
Attached is my test case.
HierarchicalPlanTests.java 3.4 KB