Rewrite self join in in predicate to aggregate

XMLWordPrintableJSON

    • Type: Improvement
    • Resolution: Unresolved
    • Priority: Major
    • None
    • Affects Version/s: 3.4.0
    • Component/s: SQL

      Transforms the SelfJoin resulting in duplicate rows used for IN predicate to aggregation.
      For IN predicate, duplicate rows does not have any value. It will be overhead.

      Ex: TPCDS Q95: following CTE is used only in IN predicates for only one column comparison (

      {@code ws_order_number}

      ).
      This results in exponential increase in Joined rows with too many duplicate rows.

      WITH ws_wh AS
      (
             SELECT ws1.ws_order_number,
                    ws1.ws_warehouse_sk wh1,
                    ws2.ws_warehouse_sk wh2
             FROM   web_sales ws1,
                    web_sales ws2
             WHERE  ws1.ws_order_number = ws2.ws_order_number
             AND    ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
      

      Could be optimized as below:

      WITH ws_wh AS
          (SELECT ws_order_number
            FROM  web_sales
            GROUP BY ws_order_number
            HAVING COUNT(DISTINCT ws_warehouse_sk) > 1)
      

      Optimized CTE scans table only once and results in unique rows.

            Assignee:
            Unassigned
            Reporter:
            Wan Kun
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: