[JBoss JIRA] (TEIID-2249) Enable the use of temporary tables for those data sources that support them instead of IN criteria for EDS
by Debbie Steigner (JIRA)
[ https://issues.jboss.org/browse/TEIID-2249?page=com.atlassian.jira.plugin... ]
Debbie Steigner commented on TEIID-2249:
----------------------------------------
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