[
https://issues.jboss.org/browse/TEIID-2249?page=com.atlassian.jira.plugin...
]
Steven Hawkins edited comment on TEIID-2249 at 5/30/13 1:03 PM:
----------------------------------------------------------------
Customer's responses to Steve request for specifics:
* explicitly what triggers the default dependent join handling vs. the use of a temp table
(row count, number of source queries etc.)?
- in an ideal world they would implement their temp table strategy given the current
capability for dependent join pushdown and contribute back the implementation (showing
type mapping, index generation, table naming strategy, etc.) and provide us with
empirically what makes sense in terms of when the temp table strategy should be used.
Answer 1) The use of temp table could be trigger either on
a) The row count of the temp table. A configurable threshold
b) Hinting on the main query to toggle on/off
There should also be a configurable restiction threshold to prevent the use of temp
table if the tuple width is too wide
Just have to ensure it will clear the data in the temp table when the source system
request has completed
* can an example of the desired query hint and options (this could include a source hint,
control over adding an index, etc.) be provided?
Answer 2) I wont be able to provide the syntax of what it should look like but the hints
and options are stated in 1) and 4).
* what sources will be targeted?
Answer 3) Definitely Oracle 10g & 11g and Sybase ASE 12 & 15. Potentially Sybase
IQ and ParAccel
* under what conditions should an index be added to the generated temp table or would this
just be directed via a hint?
Answer 4) Index creation should be configurable [hinted] and index should be on the
joining key(s). For simplicity, I would expect just a simple non unique index
even though oracle offers different types of indexes. Source hints should also be
available to hint the source system query to ensure good execution
plan is generated while joining to the temp table.
was (Author: dsteigne):
Customer's responses to Steve request for specifics:
* explicitly what triggers the default dependent join handling vs. the use of a temp table
(row count, number of source queries etc.)?
- in an ideal world they would implement their temp table strategy given the current
capability for dependent join pushdown and contribute back the implementation (showing
type mapping, index generation, table naming strategy, etc.) and provide us with
empirically what makes sense in terms of when the temp table strategy should be used.
Answer 1) The use of temp table could be trigger either on
a) The row count of the temp table. A configurable threshold
b) Hinting on the main query to toggle on/off
There should also be a configurable restiction threshold to prevent the use of temp
table if the tuple width is too wide
Just have to ensure it will clear the data in the temp table when the source system
request has completed
* can an example of the desired query hint and options (this could include a source hint,
control over adding an index, etc.) be provided?
Answer 2) I wont be able to provide the syntax of what it should look like but the hints
and options are stated in 1) and 4).
* what sources will be targeted? (presumably for RBS just Oracle)
Answer 3) Definitely Oracle 10g & 11g and Sybase ASE 12 & 15. Potentially Sybase
IQ and ParAccel
* under what conditions should an index be added to the generated temp table or would this
just be directed via a hint?
Answer 4) Index creation should be configurable [hinted] and index should be on the
joining key(s). For simplicity, I would expect just a simple non unique index
even though oracle offers different types of indexes. Source hints should also be
available to hint the source system query to ensure good execution
plan is generated while joining to the temp table.
Enable the use of temporary tables for those data sources that
support them instead of IN criteria for EDS
----------------------------------------------------------------------------------------------------------
Key: TEIID-2249
URL:
https://issues.jboss.org/browse/TEIID-2249
Project: Teiid
Issue Type: Feature Request
Components: Query Engine
Reporter: Debbie Steigner
Assignee: Steven Hawkins
Fix For: 8.4.1
Our proposal is to allow for the more efficient use of large ad-hoc result-sets by rather
than creating a long 'IN' list, inserting them in to a temporary table - for
example a # table in Sybase and SQL Server - and then generating an SQL join to that
instead.
One of the difference to materialized views (or at least my understanding), is that this
work happens at a data-source rather than within the Teiid server.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see:
http://www.atlassian.com/software/jira