[teiid-issues] [JBoss JIRA] Commented: (TEIID-1726) Requesting ability to add/use Oracle hints in EDS

[Alan Fitton (JIRA)]

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

Alan Fitton commented on TEIID-1726:
------------------------------------

Hello Steve,

Thanks for looking into this issue and explaining the options. I did include some detail in the support ticket this issue was created based on. I'll send you the ticket number privately and also some SQL (which I can't share publicly) which I hope will demonstrate what we're trying to do.

The specific part of the query where the hint applies is below (this is just one example, we have others which is why a simple way to push through any hint would be useful).

UNION ALL
  SELECT /*+ LEADING(bal) ALL_ROWS */

In the example above, I believe it would be necessary for the column names to be translated to the aliases used by EDS (I understand this probably complicates things slightly, sorry!).

As I mentioned in the ticket, we're aware of option 1 but this isn't an approach we would like to take. Mainly because we're supposed to be offering a service to the whole organisation, who shouldn't need knowledge of what optimisations are needed, but also (in our immediate project) because it's inconvenient to have to push driver specific features through our Spring JDBC abstraction. We would ideally like to apply Oracle hints on EDS views.

Option 2 sounds like it may have potential use for us. We're not doing updates through EDS, and it sounds like this would work on views. An example or where and how to embed the hint using this method would be appreciated, as I would have expected the translator or engine to strip this out.

Option 3 is something we would like to avoid, but could be an acceptable solution until there's a better one. If we did go down this route in the meantime, we would probably require our changes to be reviewed and know our usage is supported.

Alternative option #1: This sounds like it could be a very useful feature to us, although not the ideal fix for wanting to push hints through. We generally don't want to bypass the EDS query planner completely as it works well in most cases, but this sounds like it would offer a good fallback for us. There have been a few instances where we couldn't get EDS to perform the source query quite as we would like, and a native query would be a desirable solution to that.

Alternative option #2: This sounds ideal.

Alternative option #3: Unfortunately I don't think we want to go down this avenue every time we find a new hint we want to use, there's potentially many other hints that we will want to use when use of EDS increases in the organisation. We would like a generic way to do this.

If you have any more questions please let me know.

> Requesting ability to add/use Oracle hints in EDS
> -------------------------------------------------
>
>                 Key: TEIID-1726
>                 URL: https://issues.jboss.org/browse/TEIID-1726
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Server
>    Affects Versions: 7.4.1
>            Reporter: Debbie Steigner
>
> Requesting the ability to use Oracle hints inside virtual transformations and enduser queries in EDS and Teiid Designer

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira