[teiid-issues] [JBoss JIRA] (TEIID-2381) Expanded source hint support

Fri Apr 5 00:26:41 EDT 2013

    [ https://issues.jboss.org/browse/TEIID-2381?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12765146#comment-12765146 ] 

Mark Addleman commented on TEIID-2381:
--------------------------------------

> It's two-fold, the primary concern is the placement on subqueries, union branches, with clause items, etc. The secondary is for sources that support more complicated positional hints, such as a column hint. The primary is fairly feasible. Arbitrary positions however are currently not.

For us, table hints that get collected by the optimizer and delivered to the appropriate translator is the use case we have right now.  I can imagine use for positional hints but those are less compelling to me.

> If you want a hint to appear in a push-down subqueries, that would require additional translator support - e.g. a query and its subquery both have hints and are pushed together - "SELECT /*+ sh ... */ ... (SELECT /*+ sh ... */ ... ) FROM ..." such that the pushdown has both hints.

This is appealing but, as you say, the translator would have to be smart enough to retrieve the hints.  The alternative, however, seems undesirable:  the optimizer would have to understand the semantics of the hints and make decisions about which to push or not or how to combine.  I think that would either imply some standardization of hints or some API to describe metadata about hints.  Neither of those options seem like a useful road to go down.

> For example if you have two views each with their own source hint and you join them together. The optimizer removes the view layers and reorders the join. Where do the hints apply and why?

Although it's a little embarrassing to admit, I'm still not versed enough in SQL to think about this in the abstract.  Can you provide a specific example?

> Expanded source hint support
> ----------------------------
>
>                 Key: TEIID-2381
>                 URL: https://issues.jboss.org/browse/TEIID-2381
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Query Engine
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>         Attachments: HintsExecutionFactory.java, RandomExecutionFactory.java, RandomNumberStoredProcedureExecution.java, SysviewHintsExecutionTest.java
>
>
> We currently look at the source hint in only the root user query (not in subqueries nor the with clause) and only consider it in a very narrow set of circumstances when it's used in a view.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira