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

[ https://issues.jboss.org/browse/TEIID-1726?page=com.atlassian.jira.plugin... ] Steven Hawkins edited comment on TEIID-1726 at 8/29/11 4:45 PM: ---------------------------------------------------------------- based on your comments the refined set of options are: - targeted use of native query features, see TEIID-669. Now that designer has flexible extension metadata, its quite easy for us to add handling for source procedures/views where the native sql is defined in designer. However any hints in the sql would always be applied and not contextually. Exposing source procedures allowing for dynamic sql (like what is done for MDX) could also be done, but that breaks all abstraction and would not integrate well. - add support for the multi-hint comment container (alternative option #2 above). Unless some other syntax was used to separate the hints, a parsing change would be needed to support nested comments (see below). And without additional considerations, this would be non-positional in the source sql, so it really only applies to Oracle style hints. SQL Server, db2, mysql, Teiid, etc. make use of positional/syntax related hints. Referencing columns/tables as they appear in the user query would also add difficulty since we would have to introduce some escape syntax then parse/update the hint once the source query is planned - e.g. SELECT /\*+ sh my-oracle:/\*+LEADING(@BAL@) ALL_ROWS\*/ \*/ ... - implement TEIID handling for LEADING/ALL_ROWS (although the specifics of how tables/views are referenced would take more thought) as they are relevant for EDS queries (although our latency/processing tradeoff in union processing is fairly effective), and add pushdown propagation for the hints. As you point out this of course lacks generality. - instead of using Java to customize translators, make use of something akin to DB2 optimization profiles, which has some tie in to alternative option #2/#3. The translator could be configured with a file that describes hint application. The most simplistic rules could just match the entire query or be regular expression matching the source query, e.g.: <rule match="SELECT (.\*) FROM BAL (.\*) UNION SELECT (.\*)" replace="SELECT /\*+ALL_ROWS\*/ $1 FROM BAL $2 UNION SELECT $3"/> The tie to #2/#3 would be to take advantage of additional metadata / and or allow for the user to drive the hint application: <rule user_hint="loop" match="INNER JOIN" replace="INNER LOOP JOIN"/> which could handle simplistic application of SQL Server loop join hints (assuming that the translator was configured to expect ANSI FROM clauses), e.g.: SELECT /\*+ sh my-ssql:/+\*loop\*/ \*/ ... The benefit here is that this doesn't require breaking any abstraction and can be done on an as needed basis without modifying vdbs or the client application, but it could be brittle and there are still a lot of details left out here, such as rule precedence, rule file format, etc. was (Author: shawkins): based on your comments the refined set of options are: - targeted use of native query features, see TEIID-669. Now that designer has flexible extension metadata, its quite easy for us to add handling for source procedures/views where the native sql is defined in designer. However any hints in the sql would always be applied and not contextually. Exposing source procedures allowing for dynamic sql (like what is done for MDX) could also be done, but that breaks all abstraction and would not integrate well. - add support for the multi-hint comment container (alternative option #2 above). Unless some other syntax was used to separate the hints, a parsing change would be needed to support nested comments (see below). And without additional considerations, this would be non-positional in the source sql, so it really only applies to Oracle style hints. SQL Server, db2, mysql, Teiid, etc. make use of positional/syntax related hints. Referencing columns/tables as they appear in the user query would also add difficulty since we would have to introduce some escape syntax then parse/update the hint once the source query is planned - e.g. SELECT /*+ sh my-oracle:/*+LEADING(@BAL@) ALL_ROWS*/ */ ... - implement TEIID handling for LEADING/ALL_ROWS (although the specifics of how tables/views are referenced would take more thought) as they are relevant for EDS queries (although our latency/processing tradeoff in union processing is fairly effective), and add pushdown propagation for the hints. As you point out this of course lacks generality. - instead of using Java to customize translators, make use of something akin to DB2 optimization profiles, which has some tie in to alternative option #2/#3. The translator could be configured with a file that describes hint application. The most simplistic rules could just match the entire query or be regular expression matching the source query, e.g.: <rule match="SELECT (.*) FROM BAL (.*) UNION SELECT (.*)" replace="SELECT /*+ALL_ROWS+/ $1 FROM BAL $2 UNION SELECT $3"/> The tie to #2/#3 would be to take advantage of additional metadata / and or allow for the user to drive the hint application: <rule user_hint="loop" match="INNER JOIN" replace="INNER LOOP JOIN"/> which could handle simplistic application of SQL Server loop join hints (assuming that the translator was configured to expect ANSI FROM clauses), e.g.: SELECT /*+ sh my-ssql:/+*loop*/ */ ... The benefit here is that this doesn't require breaking any abstraction and can be done on an as needed basis without modifying vdbs or the client application, but it could be brittle and there are still a lot of details left out here, such as rule precedence, rule file format, etc. -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira