[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=12628609#comment-12628609 ] 

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

Hi Steven,

Thanks for the refined set of options and apologies for the delay responding.

The first refined option sounds like it is a possible solution, as I think we could simply define different views (maybe over other views if we wanted to offer both with/without hints to clients?). As I mentioned, this service is supposed to offered to other business areas and projects where ideally we don't want them to have knowledge of how to tune a query on our end, so encapsulating hinting inside views which is modelled by us in the designer is desirable.

The multi-hint comment container sounds like the ideal solution, and exactly what we want, although I appreciate this is one of the more difficult approaches.

As for the exposing procedures, this also could be something we would fine very useful. In particular, although I think that may be for a seperate issue, the ability to use Oracle sys ref cursors which are outputted (by an OUT parameter) as some kind of source model in EDS. Having to define a static model for the output would be acceptable here.

A Teiid implementation of ALL_ROWS/LEADING, although it would solve our problem in the short term, would not be preferable to the previous option as we would have to engage with you guys the next time their was a requirement for a particular hint and incur the inevitable lag between that and productisation.

On the subject of the short term, any advice on how we could extend the Oracle translator to push this particular hint through would be appreciated. I have looked at the Oracle translator and written other translators, so a hint (no pun intended) could be enough. I reached the conclusion that the engine doesn't push comment style hints down to translators at all (please correct if I'm mistaken) and my best idea was to implement a dummy pushdown function which was translated to 1 (the number) using a function modifier, and then use its presence and parameters to inject the right Oracle hint (eg WHERE ... OR CUSTOMHINT('x','y')=1 sort of like the SDO_RELATE functions do. I'm sure there's a better way to do this, though.

I think the final option of optimisation profiles could work too, although I agree this could be brittle and as mentioned being able to define hints on views/models themselves would be the ideal.

Given that there's multiple acceptable options you've given on how we can achieve what we want, I think it's best for you to choose what you believe which will be the most suitable, clean, and best for EDS with your familiarity of the engine rather than us choosing one approach.

Thanks.

> 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