UseCommentsInSourceQuery

[iniciam]

Hi, i'm working with Oracle database and sometime we use oracle database optimizer hints to override the default execution plan.

Can we use these oracle optimizer hints with Teiid ?

I see this UseCommentsInSourceQuery property inside Translator for Oracle Database, if i set it to "true", will Teiid push the oracle db optimizer hints to oracle and make sure oracle db execute it properly ?

Thank you for the help.

[rareddy]

Yes, that is intent. When this flag is on and you used comments in the transformation query for your sources, then Teiid will not remove these hints and pushes to the underlying database. However, Teiid does not validate the hints themselfs.

Ramesh..

[iniciam]

Got it. Thank you Ramesh.

[loleary]

I think we need some clarification on this. From the TranslatorProperty annotation:

@TranslatorProperty(display="Use informational comments in Source Queries",
                    description="This will embed a /*comment*/ leading comment with session/request id in source SQL query for informational purposes",
                    advanced=true)

This would lead me to believe that iniciam's use of the property would not achieve the goal of passing inline comments to the source but instead, pre-pend the query with a /* session/request id */ comment before it is sent to the source. Is this property being overloaded to serve multiple functions?

----

Larry

http://gplus.to/larryoleary

[van.halbert]

Shouldn't designer be updated to enable the user to put a comment or hint in the transformation query?   Currently, if its not a caching hint, it's being stripped.

[shawkins]

Larry is correct.  Ramesh was incorrect to indicate that this property would allow for customizable source comments. The source comment is currently only for tracking teiid queries on the database.

Van,

Putting a comment (or more likely an embedded hint) into a transformation query could be done in addition to the execution payload approach of specifying hints that is targeted at Oracle.  However it would need to be an enhancement.  It is not something that we currently do.

e.g. something like a source hint hint: /*+ sh oracle:... sybase:... */ or /*+ sh translator name:... translator name:... */

Even that, while more expressive than a single query hint, would still not be sufficient when there are multiple source queries to the same source.  Also embedding hints in transformations would more than likely require the user to selectively disable or override the hints when the view is consumed in a more complex query.

Knowing contextually when a hint should be applied is best suited to a custom translator.  If we had examples of common usecases we could determine if there is a way to metadata drive the hint application through extension properties.