-
1. Re: Limit and Offset not passed into the translator
rareddy Oct 19, 2011 10:54 AM (in response to rakeshsagar)Rakesh,
I believe pushing Limit to source would yield to wrong results, because the Limit is on the filtered resultset, not over entire data. May be you can use some custom pushdown function that specifies the number of rows to fetch, and you convert the function into Limit clause for source at translator level.
Ramesh.. -
2. Re: Limit and Offset not passed into the translator
shawkins Oct 19, 2011 12:17 PM (in response to rareddy)The functional approach Ramesh is suggesting could work, but it would have to have a column value as an input so that we would know where to push it. The straight sql workaround is to use an inline view:
SELECT * FROM (SELECT * FROM "DBMzDB2.IDB2_DSQDSETS_Rbase_Vbase" LIMIT 100, 100) AS CHORUS_B WHERE (CHORUS_B."NUMBER-EXTENTS" = '1' AND CHORUS_B."dsSSID" = 'DF1G' AND CHORUS_B."dsSystem" = 'CA11' AND CHORUS_B."dsGroup" = 'DFG' AND CHORUS_B."dsConf" = 'DEFAULT')
Here the limit will be applied first. In 7.6 https://issues.jboss.org/browse/TEIID-1726 we'll introduce a feature for source hints that may have some applicability here as well.
Steve
-
3. Re: Limit and Offset not passed into the translator
rareddy Oct 19, 2011 12:29 PM (in response to shawkins)Steve,
I was under the impression that the in-line view might re-written by the optimizer, thus back to the original query, is that not the case when a LIMIT is used? or may be when "supports" for criteria is not available?
Ramesh.. -
4. Re: Limit and Offset not passed into the translator
shawkins Oct 19, 2011 12:48 PM (in response to rareddy)Ramesh,
Yes inline views can be rewritten by the optimizer, but that logic has considerations for limits. You are partially correct though https://issues.jboss.org/browse/TEIID-1422 prevents this from working the way that I expect. We allow ourselves to push the criteria below the source node, but then we won't push the limit under the criteria. That change was really only needed by the crazy rowlimit exception logic in xml, so we could revisit it.
Steve
-
5. Re: Limit and Offset not passed into the translator
shawkins Nov 2, 2011 3:58 PM (in response to shawkins)To follow up on this, Teiid 7.6 has a new hint and baseline behavior that is applicable to this situation. https://issues.jboss.org/browse/TEIID-1806 added a NON_STRICT limit hint and ensured our limit handling is consistent. In 7.6 the inline view approach above would work by default as the criteria will not be allowed to be pushed below the limit node. You could also write the query as:
SELECT * FROM "DBMzDB2.IDB2_DSQDSETS_Rbase_Vbase" as CHORUS_B WHERE (CHORUS_B."NUMBER-EXTENTS" = '1' AND CHORUS_B."dsSSID" = 'DF1G' AND CHORUS_B."dsSystem" = 'CA11' AND CHORUS_B."dsGroup" = 'DFG' AND CHORUS_B."dsConf" = 'DEFAULT') /*+ NON_STRICT */ LIMIT 100, 100
to instruct the optimizer that the limit is pushable even if there is interviening dup removal or selection.
Steve