[teiid-issues] [JBoss JIRA] (TEIID-5900) query with local and remote pushes too much to remote

[Steven Hawkins (Jira)]

    [ https://issues.redhat.com/browse/TEIID-5900?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13971040#comment-13971040 ] 

Steven Hawkins commented on TEIID-5900:
---------------------------------------

So there are a couple of things to unpack here:

1. Is the creation of the dependent join from the local table to the remote an appropriate default plan by the optimizer - can you provide the debug plan and relevant ddl?  We need to confirm what the planner sees as the source row count.

2. It does not make it dependent if I use the MAKEDEP hint in the FROM clause; only if I put it in the OPTION clause. - can you provide the sql you are executing?

3. The exception you are seeing from oracle seems to be related to the connection being closed and doesn't seem to be the root error.  We could look more at finding the underlying problem.

> query with local and remote pushes too much to remote
> -----------------------------------------------------
>
>                 Key: TEIID-5900
>                 URL: https://issues.redhat.com/browse/TEIID-5900
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 12.3.1
>            Reporter: Ken Geis
>            Assignee: Steven Hawkins
>            Priority: Major
>
> I have a table "A" in an Oracle database. I have a date dimension "B" in Teiid, which is a materialized view of a recursive query. The date dimension has all dates from 1950 to 2050, 36890 rows in total.
> I run the following query:
> {code:sql}
> SELECT *
>   FROM a
>        JOIN b
>          ON a.date_column = b.date_key
> {code}
> Teiid sends a query to Oracle, which I will paraphrase as:
> {code:sql}
> SELECT <columns>
>   FROM a
>  WHERE date_column IN (to_date('1950-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
>                        <36899 other date values!>)
> {code}
> Oracle breaks either with the length of the query or the number of options in the IN clause.
> I thought that MAKEDEP/MAKENOTDEP/MAKEIND could help, but they didn't seem to change anything.



--
This message was sent by Atlassian Jira
(v7.13.8#713008)