Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-2622

Left join with other_join_predicate on outer table alone is slow

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.0-incubating
    • Fix Version/s: None
    • Component/s: sql-general
    • Labels:
      None

      Description

      When a Left join has an equality predicate in the ON clause connecting both tables, but this predcate is not selective (causes a join explosion) AND there is anothe predicate in the ON clause that ion the outer table alone that is highly selective, then the query is slow.

      To reproduce
      create table imei_outer(imei char(15));
      create table imei_inner(imei char(15));
      create table imei_inner_null(imei char(15));

      upsert using load into imei_outer
      select 'imei' || cast(num as char(10))
      from (
      select 100000*e5+10000*e4+1000*e3+100*e2+10*e1+e0 as num
      from (values (0)) seed(c)
      transpose 0,1,2,3,4,5,6,7,8,9 as e0
      transpose 0,1,2,3,4,5,6,7,8,9 as e1
      transpose 0,1,2,3,4,5,6,7,8,9 as e2
      transpose 0,1,2,3,4,5,6,7,8,9 as e3
      transpose 0,1,2,3,4,5,6,7,8,9 as e4
      transpose 0,1,2,3,4,5,6,7,8,9 as e5)
      ;

      upsert using load into imei_inner
      select case when num >200000 then '' else 'imei' || cast(num as char(10)) end
      from (
      select 100000*e5+10000*e4+1000*e3+100*e2+10*e1+e0 as num
      from (values (0)) seed(c)
      transpose 0,1,2,3,4,5,6,7,8,9 as e0
      transpose 0,1,2,3,4,5,6,7,8,9 as e1
      transpose 0,1,2,3,4,5,6,7,8,9 as e2
      transpose 0,1,2,3,4,5,6,7,8,9 as e3
      transpose 0,1,2,3,4,5,6,7,8,9 as e4
      transpose 0,1,2,3,4,5,6,7,8,9 as e5)
      ;

      upsert using load into imei_inner_null
      select case when num >200000 then null else 'imei' || cast(num as char(10)) end
      from (
      select 100000*e5+10000*e4+1000*e3+100*e2+10*e1+e0 as num
      from (values (0)) seed(c)
      transpose 0,1,2,3,4,5,6,7,8,9 as e0
      transpose 0,1,2,3,4,5,6,7,8,9 as e1
      transpose 0,1,2,3,4,5,6,7,8,9 as e2
      transpose 0,1,2,3,4,5,6,7,8,9 as e3
      transpose 0,1,2,3,4,5,6,7,8,9 as e4
      transpose 0,1,2,3,4,5,6,7,8,9 as e5)
      ;
      prepare s from
      select count
      from imei_outer o left join imei_inner_null i on substring(o.imei,1,14) = substring(i.imei,1,14) and o.imei > 'zzzzz' ;

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                suresh_subbiah Suresh Subbiah
                Reporter:
                suresh_subbiah Suresh Subbiah
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: