Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5522

Avoid pushing join to datasource if DS cannot handle 1600+ columns

    Details

      Description

      Problem: I am trying to create a wide view (~5000 columns), which works across data sources fine JDV. However, when I try to create the view with a join on 2+ table from data source, the optimizer pushes down the join to the source. The current source cannot handle more then ~1600 columns.

      Example: When trying to join Member_DX1 and Member_DX2 at client, JDV pushes the enter code herecombined join to postgres as one getting the too max column error.

      /* TABLE 1 */

      CREATE VIEW Member_DX1 (
      MEMB_BID Integer
      , DX130402000000 Integer
      , DX180608000000 Integer
      , DX20401070000 Integer
      .... /* 1000 more */
      as
      SELECT dx.memb_bid
      , case dx.EPI_1_DX4 when 130402000000 then 1 else 0 END as DX130402000000
      , case dx.EPI_1_DX4 when 180608000000 then 1 else 0 END as DX180608000000
      , case dx.EPI_1_DX4 when 20401070000 then 1 else 0 END as DX20401070000
      ...
      FROM BDR.ENH_EPI_DETAIL dx

      /* TABLE 2 */

      CREATE VIEW Member_DX2 (
      MEMB_BID Integer
      , DX200102010000 Integer
      , DX90125000000 Integer
      , DX160603070000 Integer
      ... /* 1000 more ...
      SELECT dx.memb_bid /* FOREIGN TABLE */
      , case dx.EPI_1_DX4 when 200102010000 then 1 else 0 END as DX200102010000
      , case dx.EPI_1_DX4 when 90125000000 then 1 else 0 END as DX90125000000
      , case dx.EPI_1_DX4 when 160603070000 then 1 else 0 END as DX160603070000
      ...`enter code here`
      FROM BDR.ENH_EPI_DETAIL dx;

      then my query in (e.g. dBeaver) looks like this:

      SELECT * from Member_DX1 dx1
      join Member_DX2 dx2
      on dx1.MEMB_BID = dx2.MEMB_BID

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                funknor Norbert Funke
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: