[
https://issues.jboss.org/browse/TEIID-1726?page=com.atlassian.jira.plugin...
]
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