[teiid-issues] [JBoss JIRA] (TEIID-2085) Additional feature to TEIID-1726

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-2085:
---------------------------------------

The possible solutions paths here are:

1. Introduce a substitution syntax for the source hint mechanism.  i.e. {code}select /*+ sh oracle:'use_hash(@a @b) all_rows full(@a)' */ col from tbl a, tbl b {code}
Which means that the \@ values should be replaced by the generated alias names.  

This approach seems too limiting though if a global table hint referencing mechanism is not also added to allow for deeper view references, such as \@v.x.y.  

2. Introduce a source hint option to keep the query aliases. i.e. {code}select /*+ sh oracle KEEP ALIASES:'use_hash(a b) all_rows full(a)' */ col from tbl a, tbl b {code}

In this case rather than use the g_0, g_1 names, we'd use a, b from the user query in the source query.  This is quite a bit simpler than the first scenario as it does not require further parsing of the source hint.

The downsides though are that the generated query may be invalid for several reasons, including:

- the user has chosen aliases that are not valid on the source, such as names that are too long.
- the user query has an alias that conflicts with an internal alias (which is only possible if it looks like v_integer or g_integer and we can easily detect this situation)
- the query is structured so that view removal or some other optimization introduces a name conflict if the original aliases are used.  Here again we can easily detect this ahead of time.

My thought is to go with the second approach since it keeps the teiid handling the simplest.  The resolution mechanisms for the user if they were to choose to keep aliases and created an invalid source query are fairly straight-forward.  Their other options would be to use the query plan to determine the generated aliases, or to use the native query procedure to directly issue a source query.
                
> Additional feature to TEIID-1726
> --------------------------------
>
>                 Key: TEIID-2085
>                 URL: https://issues.jboss.org/browse/TEIID-2085
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>            Reporter: Debbie Steigner
>            Assignee: Steven Hawkins
>             Fix For: 8.2
>
>
> Specifying the aliases in the hints added in https://issues.jboss.org/browse/TEIID-1726 do not work with even the full table name (fdm.wh_gl_daily_balances_f). However, if you were to specify g_0 and g_1 as the aliases for the tables, the hints take effect. The teiid engine has rewritten the query with its own internal aliases and the only way to find out what has been specified is to actually look at the source system sql send to Oracle.
> Can we specify the table aliases of the main query in the source
> hints rather than looking at what Teiid engine has rewritten to? It
> is quite confusing for developers to use the source hints feature
> and if the developer does not have access to the Oracle database
> they wont be able to tell what has been used in the source query to
> reflect the aliases in the source hints.
>  

--
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